|
|
|
13.4 Reporting FunctionsSimilar to the windowing functions described earlier, reporting functions allow the execution of various aggregate functions (MIN, MAX, SUM, COUNT, AVG, etc.) against a result set. Unlike windowing functions, however, the reporting functions cannot specify localized windows and thus generate the same result for the entire partition (or the entire result set, if no partitions are specified). Therefore, anything that can be accomplished using a reporting function could also be accomplished using a windowing function with an unbounded window, although it would generally be more efficient to use the reporting function. Earlier in the chapter, we used a windowing function with an unbounded reporting window to generate the total sales for the 12 months of 2001: SELECT month,
SUM(tot_sales) monthly_sales,
SUM(SUM(tot_sales)) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total_sales
FROM orders
WHERE year = 2001
AND region_id = 6
GROUP BY month
ORDER BY month;
MONTH MONTHLY_SALES TOTAL_SALES
---------- ------------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766
5 592935 6307766
6 501485 6307766
7 606914 6307766
8 460520 6307766
9 392898 6307766
10 510117 6307766
11 532889 6307766
12 492458 6307766
The next query adds a reporting function to generate the same results: SELECT month,
SUM(tot_sales) monthly_sales,
SUM(SUM(tot_sales)) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) window_sales,
SUM(SUM(tot_sales)) OVER ( ) reporting_sales
FROM orders
WHERE year = 2001
AND region_id = 6
GROUP BY month
ORDER BY month;
MONTH MONTHLY_SALES WINDOW_SALES REPORTING_SALES
---------- ------------- ------------ ---------------
1 610697 6307766 6307766
2 428676 6307766 6307766
3 637031 6307766 6307766
4 541146 6307766 6307766
5 592935 6307766 6307766
6 501485 6307766 6307766
7 606914 6307766 6307766
8 460520 6307766 6307766
9 392898 6307766 6307766
10 510117 6307766 6307766
11 532889 6307766 6307766
12 492458 6307766 6307766
The empty parentheses after the OVER clause in the reporting_sales column indicates that the entire result set should be included in the sum, which has the same effect as using an unbounded window function. Hopefully, you will agree that the reporting function is easier to understand than the unbounded window function. Reporting functions are useful when we need both detail and aggregate data (or different aggregation levels) to answer a business query. For example, the query "Show the monthly sales totals for 2001 along with each month's percentage of yearly sales" requires the detail rows to be aggregated first to the month level, and then to the year level in order to answer the question. Rather than computing both aggregations from the detail rows, we can use the SUM function with a GROUP BY clause to aggregate to the month level, and then use a reporting function to aggregate the monthly totals, as in: SELECT month,
SUM(tot_sales) monthly_sales,
SUM(SUM(tot_sales)) OVER ( ) yearly_sales
FROM orders
WHERE year = 2001
GROUP BY month
ORDER BY month;
MONTH MONTHLY_SALES YEARLY_SALES
---------- ------------- ------------
1 3028325 39593192
2 3289336 39593192
3 3411024 39593192
4 3436482 39593192
5 3749264 39593192
6 3204730 39593192
7 3233532 39593192
8 3081290 39593192
9 3388292 39593192
10 3279637 39593192
11 3167858 39593192
12 3323422 39593192
We would then simply divide MONTHLY_SALES by YEARLY_SALES to compute the requested percentage (see Section 13.4.2 later in the chapter). 13.4.1 Report PartitionsLike ranking functions, reporting functions can include PARTITION BY clauses to split the result set into multiple pieces, allowing multiple aggregations to be computed across different subsets of the result set. The following query generates total sales per salesperson per region along with the total regional sales for comparison: SELECT region_id, salesperson_id,
SUM(tot_sales) sp_sales,
SUM(SUM(tot_sales)) OVER (PARTITION BY region_id) region_sales
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id
ORDER BY region_id, salesperson_id;
REGION_ID SALESPERSON_ID SP_SALES REGION_SALES
---------- -------------- ---------- ------------
5 1 1927580 6585641
5 2 1461898 6585641
5 3 1501039 6585641
5 4 1695124 6585641
6 5 1688252 6307766
6 6 1392648 6307766
6 7 1458053 6307766
6 8 1768813 6307766
7 9 1735575 6868495
7 10 1723305 6868495
7 11 1737093 6868495
7 12 1672522 6868495
8 13 1516776 6853015
8 14 1814327 6853015
8 15 1760098 6853015
8 16 1761814 6853015
9 17 1710831 6739374
9 18 1625456 6739374
9 19 1645204 6739374
9 20 1757883 6739374
10 21 1542152 6238901
10 22 1468316 6238901
10 23 1443837 6238901
10 24 1784596 6238901
The value for the REGION_SALES column is the same for all salespeople in the same region. In the next section, we will see two different approaches for using this information to generate percentage calculations. 13.4.2 RATIO_TO_REPORTOne of the more common uses of reporting functions is to generate the value of the denominator for performance calculations. With the query from the previous section, for example, the next logical step would be to divide each salesperson's total sales (SP_SALES) by the total region sales (REGION_SALES) to determine what ratio of the total region sales can be attributed to each salesperson. One option is to use the reporting function as the denominator in the percentage calculation, as in: SELECT region_id, salesperson_id,
SUM(tot_sales) sp_sales,
ROUND(SUM(tot_sales) /
SUM(SUM(tot_sales)) OVER (PARTITION BY region_id),
2) percent_of_region
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id
ORDER BY region_id, salesperson_id1,2;
REGION_ID SALESPERSON_ID SP_SALES PERCENT_OF_REGION
---------- -------------- ---------- -----------------
5 1 1927580 .29
5 2 1461898 .22
5 3 1501039 .23
5 4 1695124 .26
6 5 1688252 .27
6 6 1392648 .22
6 7 1458053 .23
6 8 1768813 .28
7 9 1735575 .25
7 10 1723305 .25
7 11 1737093 .25
7 12 1672522 .24
8 13 1516776 .22
8 14 1814327 .26
8 15 1760098 .26
8 16 1761814 .26
9 17 1710831 .25
9 18 1625456 .24
9 19 1645204 .24
9 20 1757883 .26
10 21 1542152 .25
10 22 1468316 .24
10 23 1443837 .23
10 24 1784596 .29
Because this is such a common operation, however, Oracle has spared us the trouble by including the RATIO_TO_REPORT function. The RATIO_TO_REPORT function allows us to calculate each row's contribution to either the entire result set, or some subset of the result set if the PARTITION BY clause is included. The next query uses RATIO_TO_REPORT to generate the percentage contribution of each salesperson to her region's total sales: SELECT region_id, salesperson_id,
SUM(tot_sales) sp_sales,
ROUND(RATIO_TO_REPORT(SUM(tot_sales))
OVER (PARTITION BY region_id), 2) sp_ratio
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id
ORDER BY 1,2;
REGION_ID SALESPERSON_ID SP_SALES SP_RATIO
---------- -------------- ---------- ----------
5 1 1927580 .29
5 2 1461898 .22
5 3 1501039 .23
5 4 1695124 .26
6 5 1688252 .27
6 6 1392648 .22
6 7 1458053 .23
6 8 1768813 .28
7 9 1735575 .25
7 10 1723305 .25
7 11 1737093 .25
7 12 1672522 .24
8 13 1516776 .22
8 14 1814327 .26
8 15 1760098 .26
8 16 1761814 .26
9 17 1710831 .25
9 18 1625456 .24
9 19 1645204 .24
9 20 1757883 .26
10 21 1542152 .25
10 22 1468316 .24
10 23 1443837 .23
10 24 1784596 .29
|
|
|
|