hive 开窗 占比 累计 hive窗口函数 rownumber

1. SUM(), MIN(),MAX(),AVG()等聚合函数

对一定窗口期内的数据进行聚合。

示例：

SELECT *, 
SUM(a.pv) OVER (PARTITION BY cookieid ORDER BY create_time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv1,
SUM(a.pv) OVER (PARTITION BY cookieid ORDER BY create_time ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS pv2
FROM lxy AS a;

 基本的语法是ROWS BETWEEN 一个时间点 AND 一个时间点。

2. 新增加序号列NTILE, ROW_NUMBER(), RANK(), DENSE_RANK()

2.1ROW_NUMBER（）

　　row_number的用途的非常广泛，排序最好用他，一般可以用来实现web程序的分页，他会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。row_number用法实例:

select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order]

 

2.2  RANK（）

over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。示例：

select RANK() OVER(order by [UserId]) as rank,* from [Order]

2.3 DENSE_RANK（）

　　dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第四名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。将上面的Sql语句改由dense_rank函数来实现。

示例：

select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

2.4 NTILE（）

　　ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。

示例：

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]

对于NTILE函数，传入的参数n是指要切分成多少份，返回对应的序号。

3. LAG, LEAD, FIRST_VALUE, LAST_VALUE

这几个函数可以通过字面意思记得，LAG是迟滞的意思，也就是对某一列进行往后错行；LEAD是LAG的反义词，也就是对某一列进行提前几行；FIRST_VALUE是对该列到目前为止的首个值，而LAST_VALUE是到目前行为止的最后一个值。

SELECT *,
LAG(pv, 2) OVER(PARTITION BY cookid2 ORDER BY log_date) AS lag1,
LEAD(pv, 2, 0) OVER(PARTITION BY cookid2 ORDER BY log_date) AS lead1,
FIRST_VALUE() OVER(PARTITION BY cookid2 ORDER BY log_date) AS first_pv,
FIRST_VALUE() OVER(PARTITION BY cookid2 ORDER BY log_date) AS last_pv,
LAST_VALUE() OVER(PARTITION BY cookid2 ORDER BY log_date) AS current_last_pv
FROM lxy3;

LAG和LEAD里面都是传入三个参数，分别是排序的列名，滞后/往前的行数，以及默认填充值。因为我们在这里的LEAD()里面设置默认填充值为0，所以对于cookid后面两行缺失值填充为0。 

4. GROUPING SET, CUBE, ROLL UP

4.1 GROUPING SET（）

SELECT month,
day,
count(DISTINCT cookieid) AS count_id,
GROUPING__ID
FROM lxw1234
GROUP BY month, day
GROUPING SETS(month, day)
ORDER BY GROUPING__ID;

GROUPING SET(key1, key2)相当于是对不同字段进行group操作以后，再进行union all的操作。

注意：

1.GROUPING_ID是自动生成的，是进行了GROUPING_SET()的操作之后。

2.下划线有两个需要先做GROUP BY操作再传入GROUPING SETS等价于先group再union all的做法

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

4.2CUBE（）

CUBE就是比以上的GROUPING SETS多了一个两列的整合，也就是笛卡尔乘积。

SELECT month,
day,
count(DISTINCT cookieid) AS count_id,
GROUPING__ID
FROM lxw1234
GROUP BY month, day
WITH CUBE
ORDER BY GROUPING__ID;