hive sql之 窗口函数。_hivesql窗口函数

一、row_number 函数
区别于其它排序函数，相当于多了一个排序的列。
数据准备：
name money
ming 12
yang 23
ming 35
ming 54
yang 43
1、按照其中money 进行排序并增加一行。

select name,money,row_number() over (order by money) from lei_test;
结果：
ming    12    1
yang    23    2
ming    35    3
yang    43    4
ming    54    5
1
2
3
4
5
6
7

2、row_number 按照name 进行分组，并按照 money 进行排序。

select name,money ,row_number() over(partition by name order by money) from lei_test;
ming    12    1
ming    35    2
ming    54    3
yang    23    1
yang    43    2
1
2
3
4
5
6

3、实现分组取row_number 为确定值的数据（比如 row_number 等于一），即取出的数据是按照name进行分区，并按照money 进行排序后的数据。

select name,money,tb from (select name,money,row_number() over (partition by name order by money)) tb from lei_test ) where tb =1;
结果：
ming    12    1
yang    23    1
1
2
3
4

二 、
1、rank() 函数和row_number 函数类似就是他会跳过成绩相同的数据（如果 按照 name partiton并按照money进行order by 的化他相同的money 就会 row_number 会相同）
例如：

select name、money,rank() over (partition by name order by money) from lei_test;
结果为：
ming    12    1
ming    12    1
ming    35    3
ming    54    4
yang    23    1
yang    43    2
1
2
3
4
5
6
7
8

2、dense_rank
dense_rank 函数和row_number函数类似。（区别：相同成绩会并列，序列排序）

select name,money,dense_rank() over(partition by name order by money) from lei_test;
结果
ming    12    1
ming    12    1
ming    35    2
ming    54    3
yang    23    1
yang    43    2
1
2
3
4
5
6
7
8

3、cume_dist() 小于等于当前值的行数/分组内总行数的比例。

select name,money cume_dist() over (partition by name order by money) from lei_test;
结果为：
ming    12    0.5
ming    12    0.5
ming    35    0.75
ming    54    1.0
yang    23    0.5
yang    43    1.0
1
2
3
4
5
6
7
8

4、percent_rank() 分组当前行的rank值-1/分组的总行数-1

select name,money,percent_rank() over(partition by name order by money) from lei_test;
结果为
ming    12    0.0
ming    12    0.0
ming    35    0.6666666666666666
ming    54    1.0
yang    23    0.0
yang    43    1.0
1
2
3
4
5
6
7
8

5、ntile(n)函数，将数据分成n份

select name,money ,ntile(2) over (partition by name order by money) from lei_test;
结果为：
ming    12    1
ming    12    1
ming    35    2
ming    54    2
yang    23    1
yang    43    2
1
2
3
4
5
6
7
8

三、窗口函数
1、LEAG(col，n,DEFAULT)用于统计窗口内往上的n行值（上面可能只是给个 排序行数 这个返回的值）

select name,money,lead（money,1）over (partition by name order by money) from lei_test;
返回结果：
ming    12    12
ming    12    35
ming    35    54
ming    54    NULL
yang    23    43
yang    43    NULL
1
2
3
4
5
6
7
8

与之对应的是显示他之下的值。
select name,money,lag(money,1)
over(partition by name order by money)
from lei_test;

2、得到排序后的第一个值

select name,money first_value(money) over (partition by name ordre  by money) from lei_test;
返回结果为：
ming    12    12
ming    12    12
ming    35    12
ming    54    12
yang    23    23
yang    43    23
1
2
3
4
5
6
7
8

3、取出分组内排序后，截止到当前行，最后一个值。

select name.money,last_value(money) over (partition by name order by money) from lei_test:
返回的结果为
ming    12    12
ming    12    12
ming    35    35
ming    54    54
yang    23    23
yang    43    43
1
2
3
4
5
6
7
8

参考：https://blog.csdn.net/qq_22222499/article/details/83154912