14.4 Reporting Functions
Similar 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 each entire partition (or the entire result set, if no partitions are specified). Therefore, anything that can be accomplished using a reporting function can also be accomplished using a windowing function with an unbounded window, although it will 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 6307766The 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 6307766The empty parentheses after the OVER clause for the reporting_sales column indicate 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 you 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 to answer the question. Rather than computing both aggregations from the detail rows, you 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 39594908
2 3289336 39594908
3 3411024 39594908
4 3436482 39594908
5 3749264 39594908
6 3204730 39594908
7 3233532 39594908
8 3081290 39594908
9 3388292 39594908
10 3279637 39594908
11 3167858 39594908
12 3325138 39594908You would then simply divide MONTHLY_SALES by YEARLY_SALES to compute the requested percentage (see Section 14.4.2 later in the chapter).
14.4.1 Report Partitions
Like 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 6854731
8 14 1814327 6854731
8 15 1761814 6854731
8 16 1761814 6854731
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 6238901The value for the REGION_SALES column is the same for all salespeople in the same region. In the next section, you will see two different approaches for using this information to generate percentage calculations.
14.4.2 RATIO_TO_REPORT
One 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_id;
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 1761814 .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 .29Because 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 you 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 region_id, salesperson_id;
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 1761814 .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
