3.REPORTING FUNCTIONS
REPORTING FUNCTIONS中使用任意的聚合函数(max,min,sum,avg...),但是不象WINDOWING FUNCTIONS，REPORTING FUNCTIONS不可以指定范围以
产生局部化的结果，对每个PARTITION都是同样的值。我们看下面的例子。
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
产生的结果都是一样的，但是分别使用windowing function和reporting function.使用windowing function使得计算全年总销售额的运算被执
行了12次，而reporting function只进行了一次。over()表示对全部记录集进行统计。
Report Partitions
下面的例子说明如何分组统计，比较每个地区，销售员的销售额和地区销售总额。
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
RATIO_TO_REPORT
我们看下面的例子，需要计算每个销售人员的销售额和地区销售总额的百分比。
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
我们可以用RATIO_TO_REPORT完成同样的功能，看下面的例子：
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
这些都是最基本，最简单的功能介绍，至于如何灵活使用分析函数去解决各种问题，还需要在实践中总结。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/371/viewspace-234296/ ，如需转载，请注明出处，否则将追究法律责任。
