ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析函数3

分析函数3

原创 Linux操作系统 作者:freezr 时间:2019-02-12 22:27:05 0 删除 编辑
REPORTING FUNCTIONS

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博客 ” ,链接:http://blog.itpub.net/371/viewspace-234296/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 外连接失效
请登录后发表评论 登录
全部评论

注册时间:2001-09-28

  • 博文量
    8
  • 访问量
    4035