Hive学习笔记：04 SQL的窗口函数、OVER的使用

本文将介绍Hive SQL中窗口函数、分析函数以及Over的使用，其实不仅仅是Hive，对于很多数数据库来说同样也适用，比如Mysql8，Oracle，MSSQL等传统的关系型数据库。

测试环境准备

如有一张表stock_hq，表格中的数据如下所示：

窗口和Over

窗口其实就是一个数据范围，它指定了我们统计计算分些数据范围。在Spark和Flink中我们知道，窗口有全局窗口和滚动窗口之分，同样在SQL的窗口中也有类似的概念。在SQL中，窗口是通过Over来实现的。

OVER语法

OVER( [PARTITION BY xx] [ORDER BY XX] [Window specifications ])

在Over()中可以由上述3者进行不同的组合，或者3者都可以不指定。

PARTITION BY: 开窗字段

PARTITON BY 选项是可选的，它可以指定一个或者多个字段进行开窗，如果不指定开窗字段，则只有“一个窗口”。

• 示例1：不指定开窗字段，不指定排序，不定义窗口大小，则默认表中所有的数据都在一个窗口中

SELECT SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over() AS SUM_AMOUNT FROM stock_hq;
1

输出:

很明显，上述数据中Sum(Amount)统计的是所有记录的和，也就是说所有数据在同一窗口中。

• 示例2：指定一个开窗字段

SELECT SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over(PARTITION BY SECCODE) AS SUM_AMOUNT FROM stock_hq;
1

上述语句中指定了一个开窗字段，没有指定排序和窗口大小，则分成多个窗口，每个窗口中的数据是每个SECCODE的所有数据。

• 示例3：指定多个开窗字段

select BLOCK,SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over(PARTITION BY BLOCK,SECCODE) AS SUM_AMOUNT FROM stock_hq;
1

输出：

开窗字段值相同的数据分在同一个窗口中(类似于group by).

ORDER BY: 排序

指定了Order By后，那么窗口就不再是“静态”的了，而成了一个动态滚动的窗口，废话不多说，上菜：

• 示例1：不指定分区，只指定Order by

select TDATE,SECCODE, AMOUNT,SUM(AMOUNT) over(order by SECCODE) AS SUM_AMOUNT FROM stock_hq;
1

输出：

如果只单独指定了Order By，Order By字段相同的数据会先分成一组做一个统计，然后再到下一个组如000002.sz的数据时会将000002.sz的所有的数据先做个统计，再累加上一个分组000001.sz的统计结果。

• 示例2：指定分区，指定排序

select TDATE,SECCODE, AMOUNT,SUM(AMOUNT) over(partition by SECCODE order by TDATE) AS SUM_AMOUNT FROM stock_hq;
1

在每一个窗口中，每一行的统计结果为上一行的统计结果加上当前行的值。

Window Specifications ：窗口定义

窗口的定义主要用于指定窗口的大小，有如下几种语义进行指定：

(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
1
2
3

语法解析

UNBOUNDED 无边界的
PRECEDING 当前行的前
FOLLOWING 当前行后跟多少行

示例组合：

• ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING 当前行的前3行到前一行
• ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING 当前行的前3行+当前行+当前行的后一行
• ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING 第一行到当前行的前2行
• ROWS BETWEEN CURRENT ROW AND UNBOUND FOLLWING 当前行到末尾行
• ROWS BETWEEN 3 FOLLOWING AND UNBOUND FOLLOWING 从当前行往后数3行开始到末尾
• ROWS BETWEEN 3 FOLLOWING AND 10 FOLLOWING 从当前行的后面第3行开始到后面第10行之间的数据。

ROWS和RANGE的区别

由于我们上面的日期是连续的，所以需要删除某一天的数据，让效果看起来更明显。

delete from stock_hq where tdate = 20221115;
1

• ROWS是根据数据的物理顺序来指定窗口的大小，可以不指定排序字段(采用数据库的默认顺序)。

如：ROWS BETWEEN 3 PRECEDING AND CURRENT ROW.
在000001.sz窗口中，假如当前行的日期是20221117：到当前行时统计的是当前行前3行的值加当前行的值也就是740。统计的范围是固定的，与当前行的值无关。

示例：

select SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over( partition BY SECCODE order by TDATE ROWS BETWEEN 3 PRECEDING AND CURRENT Row)   AS SUM_AMOUNT FROM stock_hq;
1

输出：

• RANGE 必须指定一个排序字段，且排序字段必须是数字类型或时间类型，窗口的大小和当前行的排序字段的值有关。

如：RANGE BETWEEN 3 PRECEDING AND CURRENT ROW.
假如当前行的日期是20221117，到当前行时统计的是日期大于等于20221114到当前行20221117这一范围上的值，如下示例中是530，是一个逻辑上的窗口设定，与当前值有关。

select SECCODE,TDATE,AMOUNT,SUM(AMOUNT) over( partition BY SECCODE order by TDATE RANGE BETWEEN 3 PRECEDING AND CURRENT Row) AS SUM_AMOUNT FROM stock_hq;
1

输出：

窗口函数

LEAD

• 函数原型：LEAD(column,rows,default_value)
• 用途：将窗口的数据整体上移指定的行，上移后空缺的值使用指定的default_value填充。

示例：每个窗口的数据整体往上移一行，空缺的值默认为NULL

select TDATE,SECCODE, AMOUNT,LEAD(AMOUNT,1) over(partition by SECCODE order by TDATE) AS LEAD_AMOUNT FROM stock_hq;
1

输出：

LAG

• 函数原型：LAG(column,rows,default_value)
• 用途：将窗口的数据整体下移指定的行，下移后空缺的值使用指定的default_value填充。

select TDATE,SECCODE, AMOUNT,LAG(AMOUNT,1) over(partition by SECCODE order by TDATE) AS LEAD_AMOUNT FROM stock_hq;
1

输出：

FIRST_VALUE

• 函数原型：FIRST_VALUE(column,Bool)

最多两个参数，第一个参数是列名，第二个参数是一个bool值，默认是false。如果设为true，则会跳过NULL值找第一个不为NULL的值。

Tips：有些关系型数据库中只有一个参数，请注意。

• 用途：取窗口中的第一个值

LAST_VALUE

• 函数原型：LAST_VALUE(column,Bool)

最多两个参数，第一个参数是列名，第二个参数是一个bool值，默认是false。如果设为true，则会跳过NULL值找第一个不为NULL的值。

• 用途：取窗口中的最后一个值。