图解HIVE开窗函数（Windowing functions小白进）

1、概述

• 原名：Windowing functions
• 译名：开窗函数
• 应用场景举例：移动平均线……

图解开窗

2、相关函数

函数名	🔉	直译	语法	说明
over	ˈoʊvər	prep. 在……期间	OVER ()	开窗必备
preceding	prɪˈsiːdɪŋ	adj. 前述的； v. 在…之前（precede+ing）	2 PRECEDING	当前行的前2行
following	ˈfɒləʊɪŋ	adj. 接着的； prep. 在（某事）以后	3 FOLLOWING	当前行的后3行
unbounded	ʌnˈbaʊndɪd	无限的	UNBOUNDED PRECEDING	起点 (替代了数字的位置)

3、示例数据

--删库
DROP DATABASE IF EXISTS trade CASCADE;
--建库
CREATE DATABASE trade LOCATION '/trade';
USE trade;
--建表
CREATE TABLE sales
(
    ymd  STRING COMMENT "日期",
    name STRING COMMENT "顾客",
    sale INT COMMENT "消费"
) COMMENT "日期用户销量汇总表"
    LOCATION '/trade/sales';
--插数据
INSERT INTO TABLE sales
VALUES ('2020-01-01', '剑圣', 10),
       ('2020-01-02', '守望者', 15),
       ('2020-01-04', '守望者', 29),
       ('2020-01-05', '剑圣', 46),
       ('2020-01-07', '守望者', 50),
       ('2020-01-08', '先知', 62),
       ('2020-01-08', '剑圣', 55),
       ('2020-02-03', '巫妖', 23),
       ('2020-03-10', '剑圣', 12),
       ('2020-03-10', '巫妖', 12),
       ('2020-04-05', '先知', 42),
       ('2020-04-06', '先知', 42),
       ('2020-04-09', '剑圣', 13),
       ('2020-04-09', '守望者', 68);
--查看
SELECT name,collect_list(sale) FROM sales GROUP BY name;

4、collect_list：把多行数据合并成列表

SELECT name,collect_list(sale),collect_list(ymd) FROM sales GROUP BY name;

collect_list

5、over：滑窗函数

5.1、不分区的窗口就是全部数据

SELECT name,collect_list(sale) OVER() FROM sales;

over

5.2、窗口+分区

SELECT name,sale,collect_list(sale) OVER(PARTITION BY name) FROM sales;

over+partition

5.3、窗口+分区+分区内排序

SELECT name,sale,collect_list(sale) OVER(PARTITION BY name ORDER BY sale) FROM sales;

over+partition+order

5.4、窗口+分区+分区内排序+窗口范围

5.4.1、默认窗口范围：首行~当前行

5.4.2、指定滑窗范围

over+partition+order+rows between+preceding+following

6、滑窗配合其它函数

6.1、滑窗+排序排名

SELECT
    name,
    sale,
    RANK() OVER (PARTITION BY name ORDER BY sale DESC)
FROM sales;

rank

6.2、滑窗+统计

SELECT
    name,
    sale,
    COLLECT_LIST(sale) OVER (
        PARTITION BY name ORDER BY sale
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    COUNT(sale) OVER (
        PARTITION BY name ORDER BY sale
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    SUM(sale) OVER (
        PARTITION BY name ORDER BY sale
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM sales;

count、sum

6.3、拉链

函数名	🔉	直译
lag	læɡ	n. 落后；迟延；vi. 滞后；adj. 最后的
lead	liːd	vt. 领导；n. 领导；导线；vi. 导致

SELECT
    name,
    LAG(ymd,1,'1900-01-01') OVER (PARTITION BY name ORDER BY ymd),
    ymd,
    LEAD(ymd,1,'2099-12-31') OVER (PARTITION BY name ORDER BY ymd)
FROM sales;

lag、lead

7、range和rows的区别

SELECT
    name,
    sale,
    RANK() OVER (PARTITION BY name ORDER BY sale) AS r_rank,
    COLLECT_LIST(sale) OVER (
        PARTITION BY name ORDER BY sale
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c_rows,
    COLLECT_LIST(sale) OVER (
        PARTITION BY name ORDER BY sale
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS c_range
FROM sales;

结果如下，留意黄色部分

name	sale	r_rank	c_rows	c_range
剑圣	10	1	[10]	[10]
剑圣	12	2	[10,12]	[10,12]
剑圣	13	3	[10,12,13]	[10,12,13]
剑圣	46	4	[10,12,13,46]	[10,12,13,46]
剑圣	55	5	[10,12,13,46,55]	[10,12,13,46,55]
先知	42	1	[42]	[42,42]
先知	42	1	[42,42]	[42,42]
先知	62	3	[42,42,62]	[42,42,62]
巫妖	12	1	[12]	[12]
巫妖	23	2	[12,23]	[12,23]
守望者	15	1	[15]	[15]
守望者	29	2	[15,29]	[15,29]
守望者	50	3	[15,29,50]	[15,29,50]
守望者	68	4	[15,29,50,68]	[15,29,50,68]

8、应用场景

https://yellow520.blog.youkuaiyun.com/article/details/119835292