root@test 03:53: select * from t1; +----+-------+------+ | id | name | flag | +----+-------+------+ | 1 | lu | 1 | | 2 | heng | 1 | | 3 | xing | 2 | | 4 | li | 2 | | 5 | wang | 3 | | 6 | zhang | 2 | +----+-------+------+
ROW_NUMBER
ROW_NUMBER用于返回分区范围内的行号
root@test 03:07: select id,name,row_number() over w as 'row_number',flag from t1 window w as (partition by flag order by name); +----+------+------------+------+ | id | name | row_number | flag | +----+------+------------+------+ | 2 | heng | 1 | 1 | | 1 | lu | 2 | 1 | | 4 | li | 1 | 2 | | 3 | xing | 2 | 2 | | 5 | wang | 1 | 3 | +----+------+------------+------+
root@test 03:08: select id,name,row_number() over(partition by flag order by name) as 'row_number',flag from t1; +----+------+------------+------+ | id | name | row_number | flag | +----+------+------------+------+ | 2 | heng | 1 | 1 | | 1 | lu | 2 | 1 | | 4 | li | 1 | 2 | | 3 | xing | 2 | 2 | | 5 | wang | 1 | 3 | +----+------+------------+------+
root@test 03:47: select id,flag,nth_value(name,2) over w as 'nth_value' from t1 WINDOW w AS (partition by flag ORDER BY id); +----+------+-----------+ | id | flag | nth_value | +----+------+-----------+ | 1 | 1 | NULL | | 2 | 1 | heng | | 3 | 2 | NULL | | 4 | 2 | li | | 5 | 3 | NULL | +----+------+-----------+
root@test 03:48: select id,flag,nth_value(name,3) over w as 'nth_value' from t1 WINDOW w AS (partition by flag ORDER BY id); +----+------+-----------+ | id | flag | nth_value | +----+------+-----------+ | 1 | 1 | NULL | | 2 | 1 | NULL | | 3 | 2 | NULL | | 4 | 2 | NULL | | 6 | 2 | zhang | | 5 | 3 | NULL | +----+------+-----------+
root@test 04:15: select id,flag,lag(name,1,'default') over w as 'lag' from t1 WINDOW w AS ( ORDER BY id); +----+------+---------+ | id | flag | lag | +----+------+---------+ | 1 | 1 | default | | 2 | 1 | lu | | 3 | 2 | heng | | 4 | 2 | xing | | 5 | 3 | li | | 6 | 2 | wang | +----+------+---------+
CUME_DIST
返回一个值在一组值中的累积分布;即分区值小于或等于当前行值的百分比
root@test 04:19: select id,flag,cume_dist() over w as 'cume_dist' from t1 WINDOW w AS ( partition by flag order BY id); +----+------+--------------------+ | id | flag | cume_dist | +----+------+--------------------+ | 1 | 1 | 0.5 | | 2 | 1 | 1 | | 3 | 2 | 0.3333333333333333 | | 4 | 2 | 0.6666666666666666 | | 6 | 2 | 1 | | 5 | 3 | 1 | +----+------+--------------------+