ITPub博客

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

分析函数1

原创 Linux操作系统 作者:freezr 时间:2019-07-05 15:48:05 0 删除 编辑

把分析函数简单看了一遍,写点笔记给自己看。

RANKING FUNCTIONS 


1.RANKING function

RANK,DENSE_RANK,ROW_NUMBER:这三个函数的功能都是显示排名,不同之处在于对于相同的值的处理,看下面的例子,得到2001年每个地区和顾

客的销售额排名

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank,

  DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 6;

REGION_ID   CUST_NBR  CUST_SALES SALES_RANK SALES_DENSE_RANK SALES_NUMBER

---------- ---------- ---------- ---------- ---------------- ------------

         9         25    2232703          1                1            1

         8         17    1944281          2                2            2

         7         14    1929774          3                3            3

         5          4    1878275          4                4            4

        10         26    1808949          5                5            5

         6          6    1788836          6                6            6

         8         20    1412006          7                7            7

        10         27    1322747          8                8            8

         7         13    1310434          9                9            9

         7         15    1255591         10               10           10

         8         18    1253840         11               11           11

         5          2    1224992         12               12           12

         9         23    1224992         12               12           13

         9         24    1224992         12               12           14

        10         30    1216858         15               13           15

         6          9    1208959         16               14           16

         6         10    1196748         17               15           17

         7         11    1190421         18               16           18

         7         12    1182275         19               17           19

         8         19    1174421         20               18           20

         5          5    1169926         21               19           21

         5          3    1161286         22               20           22

         5          1    1151162         23               21           23

         6          8    1141638         24               22           24

         8         16    1068467         25               23           25

         9         22    1036146         26               24           26

         9         21    1020541         27               25           27

        10         28     986964         28               26           28

         6          7     971585         29               27           29

        10         29     903383         30               28           30
可以看出三个函数的不同之处在于:RANK和DENSE_RANK对于相同的值都采用相同的排名,但是前者会跳过接下来的若干名次,而后者不会。

ROW_NUMBER则不会出现相同的名次,对于相同的值的排序是任意的。

PARTITION BY:
PARTITION BY的功能就是将结果集分组。我们看看下面的例子,同样是上面的查询,但是我要得到每个地区内的排名,而不是全部地区的排名,

这时就会用到PARTITION BY。

SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,

  RANK(  ) OVER (PARTITION BY region_id

    ORDER BY SUM(tot_sales) DESC) sales_rank,

  DENSE_RANK(  ) OVER (PARTITION BY region_id

    ORDER BY SUM(tot_sales) DESC) sales_dense_rank,

  ROW_NUMBER(  ) OVER (PARTITION BY region_id

    ORDER BY SUM(tot_sales) DESC) sales_number

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 1,6;
REGION_ID    CUST_NBR CUST_SALES SALES_RANK SALES_DENSE_RANK SALES_NUMBER

---------- ---------- ---------- ---------- ---------------- ------------

         5          4    1878275          1                1            1

         5          2    1224992          2                2            2

         5          5    1169926          3                3            3

         5          3    1161286          4                4            4

         5          1    1151162          5                5            5

         6          6    1788836          1                1            1

         6          9    1208959          2                2            2

         6         10    1196748          3                3            3

         6          8    1141638          4                4            4

         6          7     971585          5                5            5

         7         14    1929774          1                1            1

         7         13    1310434          2                2            2

         7         15    1255591          3                3            3

         7         11    1190421          4                4            4

         7         12    1182275          5                5            5

         8         17    1944281          1                1            1

         8         20    1412006          2                2            2

         8         18    1253840          3                3            3

         8         19    1174421          4                4            4

         8         16    1068467          5                5            5

         9         25    2232703          1                1            1

         9         23    1224992          2                2            2

         9         24    1224992          2                2            3

         9         22    1036146          4                3            4

         9         21    1020541          5                4            5

        10         26    1808949          1                1            1

        10         27    1322747          2                2            2

        10         30    1216858          3                3            3

        10         28     986964          4                4            4

        10         29     903383          5                5            5

FIRST/LAST
用来返回排名第一和最后一位的记录。看下面的例子,找到销售最好和最差的地区。
SELECT
  MIN(region_id)
    KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) best_region,
  MIN(region_id)
    KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) worst_region
FROM orders
WHERE year = 2001
GROUP BY region_id;

BEST_REGION WORST_REGION

----------- ------------

          7           10
上面的min(region_id)这句可能有些迷惑,MIN的意思是说,如果有相同销售额的地区,取比较小的一个。


NTILE
这个函数的功能是将结果集分为几个分区,例如,我们想知道哪些地区的销售情况是在前25%,哪些地区的销售情况是在最后的25%.看下面的例


SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,

  NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 4,3 DESC;

REGION_ID    CUST_NBR CUST_SALES SALES_QUARTILE

---------- ---------- ---------- --------------

         9         25    2232703              1

         8         17    1944281              1

         7         14    1929774              1

         5          4    1878275              1

        10         26    1808949              1

         6          6    1788836              1

         8         20    1412006              1

        10         27    1322747              1

         7         13    1310434              2

         7         15    1255591              2

         8         18    1253840              2

         5          2    1224992              2

         9         23    1224992              2

         9         24    1224992              2

        10         30    1216858              2

         6          9    1208959              2

         6         10    1196748              3

         7         11    1190421              3

         7         12    1182275              3

         8         19    1174421              3

         5          5    1169926              3

         5          3    1161286              3

         5          1    1151162              3

         6          8    1141638              4

         8         16    1068467              4

         9         22    1036146              4

         9         21    1020541              4

        10         28     986964              4

         6          7     971585              4

        10         29     903383              4
如果遇到不能均分的时候,按这样的原则分配,上面的例子中有30行,分为4组,每个组8行,这样1,2组8行,3,4组7行。

WIDTH_BUCKET
和NTILE函数不同的是,WIDTH_BUCKER是按照值的区间来分组的。看以下的例子:将销售额分为三组,1-1000000,1000001-2000000,2000001

-3000000。

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  WIDTH_BUCKET(SUM(tot_sales), 1, 3000000, 3) sales_buckets

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 3;

REGION_ID   CUST_NBR CUST_SALES SALES_BUCKETS

---------- ---------- ---------- -------------

        10         29     903383             1

         6          7     971585             1

        10         28     986964             1

         9         21    1020541             2

         9         22    1036146             2

         8         16    1068467             2

         6          8    1141638             2

         5          1    1151162             2

         5          3    1161286             2

         5          5    1169926             2

         8         19    1174421             2

         7         12    1182275             2

         7         11    1190421             2

         6         10    1196748             2

         6          9    1208959             2

        10         30    1216858             2

         5          2    1224992             2

         9         24    1224992             2

         9         23    1224992             2

         8         18    1253840             2

         7         15    1255591             2

         7         13    1310434             2

        10         27    1322747             2

         8         20    1412006             2

         6          6    1788836             2

        10         26    1808949             2

         5          4    1878275             2

         7         14    1929774             2

         8         17    1944281             2

         9         25    2232703             3

如果遇到不在设定的范围内的值,会自动产生两个组,0和N+1,看下面的例子:
SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  WIDTH_BUCKET(SUM(tot_sales), 1000000, 2000000, 3) sales_buckets

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 3;

REGION_ID   CUST_NBR CUST_SALES SALES_BUCKETS

---------- ---------- ---------- -------------

        10         29     903383             0

         6          7     971585             0

        10         28     986964             0

         9         21    1020541             1

         9         22    1036146             1

         8         16    1068467             1

         6          8    1141638             1

         5          1    1151162             1

         5          3    1161286             1

         5          5    1169926             1

         8         19    1174421             1

         7         12    1182275             1

         7         11    1190421             1

         6         10    1196748             1

         6          9    1208959             1

        10         30    1216858             1

         5          2    1224992             1

         9         24    1224992             1

         9         23    1224992             1

         8         18    1253840             1

         7         15    1255591             1

         7         13    1310434             1

        10         27    1322747             1

         8         20    1412006             2

         6          6    1788836             3

        10         26    1808949             3

         5          4    1878275             3

         7         14    1929774             3

         8         17    1944281             3

         9         25    2232703             4

CUME_DIST and PERCENT_RANK
CUME_DIST(Cumulative Distribution):计算小于或等于当前排名的行数在总行数中的百分比。计算方法:当前排名/总行数
PERCENT_RANK:计算排名和总行数的百分比。 当前排名-1/总行数-1
所有的排名都采用DENSE_RANK.我们以第二名为例,CUME_DIST=2/30=.066666667,PERCENT_RANK=2-1/30-1=1/29=.034482759

SELECT region_id, cust_nbr,

  SUM(tot_sales) cust_sales,

  CUME_DIST(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_cume_dist,

  PERCENT_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_percent_rank

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

ORDER BY 3 DESC;

REGION_ID   CUST_NBR  CUST_SALES SALES_CUME_DIST SALES_PERCENT_RANK

---------- ---------- ---------- --------------- ------------------

         9         25    2232703      .033333333                  0

         8         17    1944281      .066666667         .034482759

         7         14    1929774              .1         .068965517

         5          4    1878275      .133333333         .103448276

        10         26    1808949      .166666667         .137931034

         6          6    1788836              .2         .172413793

         8         20    1412006      .233333333         .206896552

        10         27    1322747      .266666667         .24137931

         7         13    1310434              .3         .275862069

         7         15    1255591      .333333333         .310344828

         8         18    1253840      .366666667         .344827586

         5          2    1224992      .466666667         .379310345

         9         23    1224992      .466666667         .379310345

         9         24    1224992      .466666667         .379310345

        10         30    1216858              .5         .482758621

         6          9    1208959      .533333333         .517241379

         6         10    1196748      .566666667         .551724138

         7         11    1190421              .6         .586206897

         7         12    1182275      .633333333         .620689655

         8         19    1174421      .666666667         .655172414

         5          5    1169926              .7         .689655172

         5          3    1161286      .733333333         .724137931

         5          1    1151162      .766666667         .75862069

         6          8    1141638              .8         .793103448

         8         16    1068467      .833333333         .827586207

         9         22    1036146      .866666667         .862068966

         9         21    1020541              .9         .896551724

        10         28     986964      .933333333         .931034483

         6          7     971585      .966666667         .965517241

        10         29     903383               1                  1

Hypothetical Functions
这个函数的作用是我们需要查看数据库集中包括我们假定的值的情况。看下面的例子:
SELECT cust_nbr, SUM(tot_sales) cust_sales,

  RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) rank,

  DENSE_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) dense_rank,

  CUME_DIST(  ) OVER (ORDER BY SUM(tot_sales) DESC) cume_dist,

  PERCENT_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) percent_rank

FROM orders

WHERE year = 2001

GROUP BY cust_nbr

ORDER BY 3;

  CUST_NBR CUST_SALES       RANK DENSE_RANK  CUME_DIST PERCENT_RANK

---------- ---------- ---------- ---------- ---------- ------------

        25    2232703          1          1 .033333333            0

        17    1944281          2          2 .066666667   .034482759

        14    1929774          3          3         .1   .068965517

         4    1878275          4          4 .133333333   .103448276

        26    1808949          5          5 .166666667   .137931034

         6    1788836          6          6         .2   .172413793

        20    1412006          7          7 .233333333   .206896552

        27    1322747          8          8 .266666667    .24137931

        13    1310434          9          9         .3   .275862069

        15    1255591         10         10 .333333333   .310344828

        18    1253840         11         11 .366666667   .344827586

         2    1224992         12         12 .466666667   .379310345

        23    1224992         12         12 .466666667   .379310345

        24    1224992         12         12 .466666667   .379310345

        30    1216858         15         13         .5   .482758621

         9    1208959         16         14 .533333333   .517241379

        10    1196748         17         15 .566666667   .551724138

        11    1190421         18         16         .6   .586206897

        12    1182275         19         17 .633333333   .620689655

        19    1174421         20         18 .666666667   .655172414

         5    1169926         21         19         .7   .689655172

         3    1161286         22         20 .733333333   .724137931

         1    1151162         23         21 .766666667    .75862069

         8    1141638         24         22         .8   .793103448

        16    1068467         25         23 .833333333   .827586207

        22    1036146         26         24 .866666667   .862068966

        21    1020541         27         25         .9   .896551724

        28     986964         28         26 .933333333   .931034483

         7     971585         29         27 .966666667   .965517241

        29     903383         30         28          1            1
下面我们需要看看假定销售额是100000的排名情况。
SELECT

  RANK(1000000) WITHIN GROUP

    (ORDER BY SUM(tot_sales) DESC) hyp_rank,

  DENSE_RANK(1000000) WITHIN GROUP

    (ORDER BY SUM(tot_sales) DESC) hyp_dense_rank,

  CUME_DIST(1000000) WITHIN GROUP

    (ORDER BY SUM(tot_sales) DESC) hyp_cume_dist,

  PERCENT_RANK(1000000) WITHIN GROUP

    (ORDER BY SUM(tot_sales) DESC) hyp_percent_rank

FROM orders

WHERE year = 2001

GROUP BY cust_nbr;

  HYP_RANK HYP_DENSE_RANK HYP_CUME_DIST HYP_PERCENT_RANK

---------- -------------- ------------- ----------------

        28             26    .903225806               .9

WITHIN GROUP的意思说:决定这个假定值的排名时,是把这个值插入到实际的数据集中。如果没有的话,就是把假定值的排名和实际值的排名

进行比较。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/371/viewspace-234294/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 杭州两个月
下一篇: 分析函数3
请登录后发表评论 登录
全部评论

注册时间:2001-09-28

  • 博文量
    70
  • 访问量
    53403