| > if I have 10,000 sales people likely their leads and closed sales
| > are likely not modeled as part of the Salesperson EB itself, but
| > instead as a related Lead and Sale EB.
|
| You could also set up a two-way CMR relationship here for sales and the
| person responsible. Thus, it doesn't really matter whether you ask the
| sale for the salesman responsible, or the salesman for his sales. If the
| salesman has access to his sales, then it can just as easily have a
| convenience method on the interface to give a "total sales figure". That
| takes care of the aggregation part.
Right, this means that I can get an iterator over 10,000 Saleperson bean
instances, and in Java, for each one, ask what their total sales figure is.
That still doesn't help me find the top five of the whole company. I need
to visit each one and ask for their total to be sure I've considered them all.
For this you need to aggregate and order then filter...
| For the ordering part, I would take a different approach. If you DB has
| to sort 10,000 objects and then just pick the top 5, that's a hugely
| inefficient operation.
This is precisely my point. The database *theoretically* has to aggregate
and sort all 10,000, but a good database would never need to *actually*
do that. Using indexes and perhaps materialized views, with a few
disk I/O's the database could return the first five salespeople based
on the ranked aggregates of their total sales. Twenty-five years of
tuning have made relational DB's very good at this part.
I know Oracle, so I know I can do a query like this:
/* Return the top five salespeople based
* on total sales for september 2001
*/
select *
from (
select salesperson, sum(amount) as total
from sales
where sale_date between '01-SEP-2001' and '30-SEP-2001'
group by salesperson
order by total desc
)
where rownum <= 5 /* only the top 5 in aggregated, sorted order */
| Instead, I would create some form of bar first -
| pick all sales totals over some value (user defined with an input
| parameter) and then sort on that.
But, in general, the user (a top-level manager?) won't know what
sales total value to pick here to guarantee the result she wants.
| For example, have the finder locate the min and max sales totals
| and calculate the top five percent of sales totals.
To calculate the MIN and MAX you have to either use SQL with
vendor-specific extensions, or iterate over all of your
Salesperson EB's again, right? To the best of my knowledge,
the BNF grammar for EJB QL does not include a MAX() or MIN()
or SUM() aggregate function.
The list of employees with sales totals greater than
the top five percent of the MAX salesfigure may not return
a list of the top five salespeople. It may only return 1.
For example, if the very top salesperson was *way* ahead
of the rest. For example (given just a list of 10 employees),
if their sales totals were:
100000,80000,70000,60000,50000,40000,30000,20000,10000,9000
Then the number of salespeople who sold more than 95% of the MAX
of 100000 would be all those who sold more than 95000, which is
just one person, not the top five like the manager wanted.
| Use that as the return value of the EB finder and then sort the
| bean references as per usual in a List. I'd come up with some useful
| code, but my brain is too fried now (can't even get my CVS commit
| messages right when I have them written down in front of me!). Will do
| it in the morning.
I'll be interested to learn some tricks from your example. Thanks!
_____________________________________________________________________
Steve Muench - Developer, Product Manager, XML Evangelist, Author
"Building Oracle XML Applications" - www.oreilly.com/catalog/orxmlapp
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
