HIVE SQL 聚合函数与 rows between / range between详解

HIVE SQL 聚合函数与 rows between / range between详解

名词解释

unbounded：无边界
preceding：根据排序后的结果集选定的当前数据行往前
following：根据排序后的结果集选定的当前数据行往后
unbounded preceding：根据排序后的结果集选定的当前数据行往前，即到初始行结束
n preceding：往前n行，需要结合参数ROWS和RANGE的不同，所属含义将会有所不同
unbounded following：根据排序后的结果集选定的当前数据行往后，即到末尾行结束
n following 往后n行，需要结合参数ROWS和RANGE的不同，所属含义将会有所不同
current row 当前行

语法
(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 BETWEEN 3 PRECEDING AND 4 FOLLOWING：表示在当前顺序的前提下，往前3行数据和往后4行数据，总计8行，当然这个8行不是绝对的，比如排序后的第一条数据亦或排序后的最后一条数据，根据行数判定肯定不会是8行。这个可以理解为物理行数。

RANGE BETWEEN 3 PRECEDING AND 4 FOLLOWING：表示在当前顺序的前提下，当前的数值进行加减的一个范围。比如我是以年龄进行排序，当前的记录为5岁，那么实际取值的范围是[2,9]之间的所有数据，这里可能根据数据情况的情况不止8条数据哦，可多可少。这个可以理解为逻辑行数。

举例

1. RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING【此句为默认，在开窗时不添加也是默认有】

   sum(score) over ()

   同：sum(score) over (order by datetimes RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

2. 互联网行业的活跃用户语境用法

   场景：在同一个月中存在15天登录行为的客户为活跃用户：

   count(distinct datetimes) over (order by datetimes RANGE BETWEEN 7 PRECEDING AND 7 FOLLOWING)

   datetimes为时间字段，在使用该方法前，需将其转化为yyyyMMdd且将其转换为数值类型，方可进行使用。因为他需要同代码中的7进行加减，否则会报错。

   前7后7：当前时间datetimes减7和加7。例如：20240415是当前这条记录的时间，那么根据range的规则需要在此范围内[20240408,20240422]这个闭区间内的数据进行计算。【需要留意的是数字这块必须要大于0的正整数，0已经有CURRENT ROW契合】

3. 计分领域的语境用法，实际就是以某一顺序维度做累加

   场景：篮球场上在不同时段，各队总比分的场景。一般而言某队得分都会插入一条数据

   其中某队的得分计算法

   sum(score) over (order by datetimes ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW)

   以时间为维度进行顺序排序，而后通过锁定当前行【CURRENT ROW】时间线往前推的所有记录【UNBOUNDED FOLLOWING】的得分【score】进行累加