窗口函数是可以在本行内做运算,得到多行<前几行,后几行等>的结果。 通用的窗口函数可以用下面的语法来概括:
Function() Over (Partition By Column1,Column2,Order By Column3 [Rows between .. and ..])
- Over关键字: 表示前面的函数是分析函数,不是普通的集合函数
- Partition By: 分组子句,表示分析函数的计算范围,进行分组
- Order By: 排序子句,表示分组后,组内的排序方式
- Rows /Range: 窗口子句,不是必须,是在分组后,组内的子分组即窗口[Rows between … and …]
- 如果没指定Row语句,则默认为
Rows BETWEEN unbounded preceding AND CURRENT ROW
。 - between前面是前面多少行,比方前面1行就是1 preceding,unbounded preceding是指当前行前面所有行
- and后面是当前行后面多少行,比方后面1行就是1 following,current row是指当前行
- Range是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内
- Rows是物理窗口,即根据order by 子句排序后,取的前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关)
- 如果没指定Row语句,则默认为
基础数据
我们先准备基础数据,新建用户比赛表,用户名字,对手,比赛结果<1表示胜利,0表示失败),比赛时间
CREATE TABLE tmp.user_match_table (
user_name string,
opponent string,
result int,
create_time timestamp);
然后我们插入一些数据:
INSERT INTO TABLE tmp.user_match_table values
('lily','willing',1,'2019-07-18 23:19:00'),
('willing','lily',0,'2019-07-18 23:19:00'),
('lily','god',0,'2019-07-18 23:20:00'),
('god','lily',1,'2019-07-18 23:20:00'),
('lily','Tom',1,'2019-07-19 22:19:00'),
('Tom','lily',0,'2019-07-19 22:19:00'),
('god','lily',0,'2019-07-19 23:19:00'),
('lily','god',1,'2019-07-19 23:19:00');
聚合型窗口函数
聚合型窗口函数包含max, min, sum, avg, count,collect_set,collect_list,concat_ws等聚合函数,可以计算类似累计至今的收入/开销/订单数目等数据。
累计至当前时间的每个人的得分情况
> SELECT *, SUM(result) OVER (PARTITION BY user_name ORDER BY create_time) AS result_sums FROM tmp.