1. 启发
在业务上，我们常常会碰到这样的需求
例如原始表如下：
| uid | state | time |
|---|---|---|
| a | 1 | 0 |
| a | null | 1 |
| a | null | 2 |
| a | 3 | 3 |
| b | null | 4 |
| b | 2 | 5 |
| b | null | 6 |
需要变成如下：
| uid | state | time |
|---|---|---|
| a | 1 | 0 |
| a | 1 | 1 |
| a | 1 | 2 |
| a | 3 | 3 |
| b | null | 4 |
| b | 2 | 5 |
| b | 2 | 6 |
简单来讲就是，对所有空值，复制每个分组下排序后的最后一个非空值，这种需求也叫做The Last non-NULL Puzzle。对于这种问题，pandas有ffill函数可以简单轻易的解决这个问题，但是在SQL中就没这么畅快了。
对与这个问题有多个解法，以下是笔者找到的一种，作为抛砖引玉
代码如下：
select
uid
, coalesce(last_value(state, true) over(partition by uid order by time rows between unbounded preceding and current row)) state
, time
from values
('a', 1, 0),
('a', null, 1),
('a', null, 2),
('a', 3, 3) ,
('b', null, 4),
('b', 2, 5),
('b', 2, 6)
as tab(uid, state, time);
/*
output:
uid state2 time
a 1 0
a 1 1
a 1 2
a 3 3
b NULL 4
b 2 5
b 2 6
*/
解释：
over()开窗函数中，unbounded preceding and current row表示从第一行到目前这一行
last_value()表示取一个数组中的最后一个值
coalesce()表示聚合一个数组，剔除所有的空值
2. 拓展
在Hive中，SQL中额外添加了一些函数：
2.1 开窗函数中的聚合函数
- LEAD() 返回分组中的上一个值
- LAG() 返回分组中的下一个值
- FIRST_VALUE() 返回分组中的第一个值
- LAST_VALUE() 返回分组中的最后一个值
2.2 OVER() 表达式中
OVER()中开窗，可以使用ROWS表达式子分别对行进行选择/RANGE表达式对范围进行选择，例如：
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
reference:
https://cwiki.apache.org/confluence/display/hive/languagemanual+windowingandanalytics
网友评论