# 分析函数3

REPORTING FUNCTIONS

3.REPORTING FUNCTIONS
REPORTING FUNCTIONS中使用任意的聚合函数(max,min,sum,avg...),但是不象WINDOWING FUNCTIONS，REPORTING FUNCTIONS不可以指定范围以

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

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

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

• 博文量
70
• 访问量
53831