5.5 Subquery Case Study: The Top N Performers
Certain queries that are easily described in English have traditionally been difficult to
formulate
in SQL. One common example is the "Find the top five salespeople" query. The complexity
stems
from the fact that data from a table must first be aggregated, and then the aggregated values must be sorted and compared to one another to identify the top or bottom performers. In this section, you will see how subqueries may be used to answer such questions. At the end of the section, we introduce ranking functions, a feature of Oracle SQL that was
specifically
designed for these types of queries.
5.5.1 A Look at the Data
Consider the problem of finding the top five salespeople. Let's assume that we are basing our evaluation on the amount of revenue each salesperson brought in during the previous year. The first task, then, would be to sum the dollar amount of all orders booked by each salesperson during the year in question. To do so, we will dip into our data warehouse, in which orders have been aggregated by salesperson, year, month, customer, and region. The following query generates total sales per salesperson for the year 2001:
SELECT s.name employee, SUM(o.tot_sales) total_sales
FROM orders o INNER JOIN salesperson s
ON o.salesperson_id = s.salesperson_id
WHERE o.year = 2001
GROUP BY s.name
ORDER BY 2 DESC;
EMPLOYEE TOTAL_SALES
------------------------------ -----------
Jeff Blake 1927580
Sam Houseman 1814327
Mark Russell 1784596
John Boorman 1768813
Carl Isaacs 1761814
Tim McGowan 1761814
Chris Anderson 1757883
Bill Evans 1737093
Jim Fletcher 1735575
Mary Dunn 1723305
Dave Jacobs 1710831
Chuck Thomas 1695124
Greg Powers 1688252
Don Walters 1672522
Alex Fox 1645204
Barbara King 1625456
Lynn Nichols 1542152
Karen Young 1516776
Bob Grossman 1501039
Eric Iverson 1468316
Tom Freeman 1461898
Andy Levitz 1458053
Laura Peters 1443837
Susan Jones 1392648
It appears that Isaacs and McGowan have tied for fifth place, which, as you will see, adds an interesting wrinkle to the problem.
5.5.2 Your Assignment
It seems that the boss was so tickled with this year's sales that she has asked you, the IT manager, to see that each of the top five salespeople receive a bonus equal to 1% of their yearly sales. No problem, you say. You quickly throw together the following report using your favorite feature, the inline view, and send it off to the boss:
SELECT s.name employee, top5_emp_orders.tot_sales total_sales,
ROUND(top5_emp_orders.tot_sales * 0.01) bonus
FROM
(SELECT all_emp_orders.salesperson_id emp_id,
all_emp_orders.tot_sales tot_sales
FROM
(SELECT salesperson_id, SUM(tot_sales) tot_sales
FROM orders
WHERE year = 2001
GROUP BY salesperson_id
ORDER BY 2 DESC
) all_emp_orders
WHERE ROWNUM <= 5
) top5_emp_orders INNER JOIN salesperson s
ON top5_emp_orders.emp_id = s.salesperson_id
ORDER BY 2 DESC;
EMPLOYEE TOTAL_SALES BONUS
------------------------------ ----------- ----------
Jeff Blake 1927580 19276
Sam Houseman 1814327 18143
Mark Russell 1784596 17846
John Boorman 1768813 17688
Tim McGowan 1761814 17618
The howl
emitted
by Isaacs can be
heard
for five square blocks. The boss, looking a bit harried, asks you to take another stab at it. Upon reviewing your query, the problem becomes immediately evident; the inline view aggregates the sales data and sorts the results, and the containing query grabs the first five sorted rows and discards the rest. Although it could easily have been McGowan, since there is no second
sort
column, Isaacs was arbitrarily omitted from the result set.
5.5.3 Second Attempt
You console yourself with the fact that you gave the boss exactly what she asked for: the top five salespeople. However, you realize that part of your job as IT manager is to give people what they need, not
necessarily
what they ask for, so you rephrase the boss's request as follows: give a bonus to all salespeople whose total sales ranked in the top five last year. This will require two steps: find the fifth highest sales total last year, and then find all salespeople whose total sales meet or exceed that figure. You write a new query as
follows
:
SELECT s.name employee, top5_emp_orders.tot_sales total_sales,
ROUND(top5_emp_orders.tot_sales * 0.01) bonus
FROM salesperson s INNER JOIN
(SELECT salesperson_id, SUM(tot_sales) tot_sales
FROM orders
WHERE year = 2001
GROUP BY salesperson_id
HAVING SUM(tot_sales) IN
(SELECT all_emp_orders.tot_sales
FROM
(SELECT SUM(tot_sales) tot_sales
FROM orders
WHERE year = 2001
GROUP BY salesperson_id
ORDER BY 1 DESC
) all_emp_orders
WHERE ROWNUM <= 5)
) top5_emp_orders
ON top5_emp_orders.salesperson_id = s.salesperson_id
ORDER BY 2 DESC;
EMPLOYEE TOTAL_SALES BONUS
------------------------------ ----------- ----------
Jeff Blake 1927580 19276
Sam Houseman 1814327 18143
Mark Russell 1784596 17846
John Boorman 1768813 17688
Tim McGowan 1761814 17618
Carl Isaacs 1761814 17618
Thus, there are actually six top five salespeople. The main difference between your first attempt and the second is the addition of the HAVING clause in the inline view. The subquery in the HAVING clause returns the five highest sales totals, and the inline view then returns all salespeople (
potentially
more than five) whose total sales exist in the set returned by the subquery.
Although you are confident in your latest results, there are several aspects of the query that bother you:
-
The aggregation of sales data is performed twice.
-
The query will never contend for Most Elegant Query of the Year.
-
You could've sworn you read about some sort of feature just for handling these types of queries . . .
In fact, there is a feature, an analytic SQL feature, for performing ranking queries that became available with Oracle8
i
. That feature is the RANK function.
5.5.4 Final Answer
The RANK function is specifically designed to help you write queries to answer questions like the one posed in this case study. Part of a set of analytic functions (all of which will be explored in Chapter 14), the RANK function may be used to assign a ranking to each element of a set. The RANK function understands that there may be ties in the set of values being ranked and leaves gaps in the ranking to compensate. The following query illustrates how rankings would be assigned to the entire set of salespeople; notice how the RANK function
leaves
a gap between the fifth and seventh rankings to compensate for the fact that two rows share the fifth spot in the ranking:
SELECT salesperson_id, SUM(tot_sales) tot_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank
FROM orders
WHERE year = 2001
GROUP BY salesperson_id;
SALESPERSON_ID TOT_SALES SALES_RANK
-------------- ---------- ----------
1 1927580 1
14 1814327 2
24 1784596 3
8 1768813 4
15 1761814 5
16 1761814 5
20 1757883 7
11 1737093 8
9 1735575 9
10 1723305 10
17 1710831 11
4 1695124 12
5 1688252 13
12 1672522 14
19 1645204 15
18 1625456 16
21 1542152 17
13 1516776 18
3 1501039 19
22 1468316 20
2 1461898 21
7 1458053 22
23 1443837 23
6 1392648 24
Leaving gaps in the rankings whenever ties are
encountered
is critical for properly handling these types of queries. (If you do not wish to have gaps in the ranking, you can use the DENSE_RANK function intead.) Table 5-1 shows the number of rows that would be returned for this data set for various top-N queries.
Table 5-1. Rows returned for N = {1,2,3,...,9}
|
Top-N salespeople
|
Rows returned
|
|
1
|
1
|
|
2
|
2
|
|
3
|
3
|
|
4
|
4
|
|
5
|
6
|
|
6
|
6
|
|
7
|
7
|
|
8
|
8
|
|
9
|
9
|
As you can see, the result sets would be identical for both the "top five" and "top six" versions of this query for this particular data set.
By wrapping the previous RANK query in an inline view, you can retrieve the salespeople with a ranking of five or less and join the results to the salesperson table to generate the final result set:
SELECT s.name employee, top5_emp_orders.tot_sales total_sales,
ROUND(top5_emp_orders.tot_sales * 0.01) bonus
FROM
(SELECT all_emp_orders.salesperson_id emp_id,
all_emp_orders.tot_sales tot_sales
FROM
(SELECT salesperson_id, SUM(tot_sales) tot_sales,
RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank
FROM orders
WHERE year = 2001
GROUP BY salesperson_id
) all_emp_orders
WHERE all_emp_orders.sales_rank <= 5
) top5_emp_orders INNER JOIN salesperson s
ON top5_emp_orders.emp_id = s.salesperson_id
ORDER BY 2 DESC;
EMPLOYEE TOTAL_SALES BONUS
------------------------------ ----------- ----------
Jeff Blake 1927580 19276
Sam Houseman 1814327 18143
Mark Russell 1784596 17846
John Boorman 1768813 17688
Tim McGowan 1761814 17618
Carl Isaacs 1761814 17618
If this query looks familiar, that's because it's almost identical to the first attempt, except that the RANK function is used instead of the pseudocolumn ROWNUM to determine where to draw the line between the top five salespeople and the rest of the pack.
Now that you are happy with your query and confident in your results, you show your findings to your boss. "Nice work," she says. "Why don't you give yourself a bonus as well? In fact, you can have Isaacs's bonus, since he quit this morning." Salespeople can be so touchy.
|
