窗口函数之CUME_DIST和PERCENT_RANK区别

over partition by与group by的区别：
group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能，但同时也具有其他的高级功能。

1. CUME_DIST 小于等于当前值的行数/分组内总行数
   create table windows_dept

(

deptno string,

pno string,

sum_insured decimal(18,2)

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

stored as textfile;

数据：

G01,P001,10

G01,P002,20

G01,P003,30

G02,P004,40

G02,P005,50

G03,P006,60

G03,P007,70

G03,P008,80

G03,P009,90

G03,P010,100

测试：

SELECT

deptno,

pno,

sum_insured,

CUME_DIST() OVER(ORDER BY sum_insured) AS rn1,

CUME_DIST() OVER(PARTITION BY deptno ORDERBY

sum_insured) AS rn2

FROM windows_dept;

结果：

deptno     pno      sum_insured     rn1   rn2

G01       P001      10          0.1  0.3333333333333333

G01     P002      20          0.2  0.6666666666666666

G01     P003      30          0.3  1.0

G02     P004      40          0.4  0.5

G02     P005      50          0.5  1.0

G03     P006      60          0.6  0.2

G03     P007      70          0.7  0.4

G03     P008      80          0.8  0.6

G03     P009      90          0.9  0.8

G03     P010      100         1.0  1.0

分析：

rn1: 没有partition,所有数据均为1组，总行数为10，

第1行：小于等于10的行数为1，因此，1/10=0.1

第5行：小于等于50的行数为5，因此，5/10=0.5

rn2: 按照部门分组，deptno=G03的行数为5,

第6行：小于等于60的行数为1，因此，1/5=0.2

第8行：小于等于80的行数为3，因此，3/5=0.6

2. PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
   SELECT

deptno,

pno,

sum_insured,

PERCENT_RANK() OVER(ORDER BY sum_insured) AS rn1, --分组内

RANK() OVER(ORDER BY sum_insured) AS rn11, --分组内RANK值

SUM(1) OVER(PARTITION BY NULL) AS rn12, --分组内总行数

PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sum_insured) AS rn2

FROM windows_dept;

结果：

deptno    pno   sum_insured  rn1   rn11      rn12      rn2

G01      P001 10           0.0  1        10         0.0

G01      P002 20           0.1111111111111111      2        10         0.5

G01     P003 30           0.2222222222222222      3        10         1.0

G02     P004 40           0.3333333333333333      4        10         0.0

G02     P005 50           0.4444444444444444      5        10         1.0

G03     P006 60           0.5555555555555556      6        10         0.0

G03     P007 70           0.6666666666666666      7        10         0.25

G03    P008 80           0.7777777777777778      8        10         0.5

G03     P009 90           0.8888888888888888      9        10         0.75

G03    P010 100          1.0              10     10         1.0

分析：

rn1: rn1 =(rn11-1) / (rn12-1)

第一行,(1-1)/(10-1)=0/9=0

第二行,(2-1)/(10-1)=1/9=0.1111111111111111

第四行,(4-1)/(10-1)=3/4=0.3333333333333333

rn2: 按照deptno分组，deptno=G01的总行数为3

第一行，(1-1)/(3-1)=0

第二行，(2-1)/(3-1)=0.5

第三行，(3-1)/(3-1)=1