窗⼝函数应⽤之移动范围计算【详细剖析窗⼝函数】
（HiveSql⾯试题4详解）
⽬ 录
0 需求描述
有如下数据表：
A 2015-
01  5
A 2015-
0115
B 2015-
015
A 2015-
018
B 2015-
0125
A 2015-
015
A 2015-
024
A 2015-
026
B 2015-
0210
B 2015-
025
A 2015-
0316
A 2015-
0322
B 2015-
0323
B 2015-
0310
B 2015-
0311
需求如下：
每个⽤户截⽌到每⽉为⽌的最⼤单⽉访问次数和累计到该⽉的总访问次数结果如下：
⽤户⽉份当⽉访问次数最⼤访问次数总访问次数
A      2015-01  33  33  33
A      2015-02  10  33  43
A      2015-03  38  38  81
B      2015-01  30  30  30
B      2015-02  15  30  45
B      2015-03  44  44  89
1 需求实现
1.1 数据准备
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
1.2 创建hive表
drop table if exists dan_test.pview
CREATE TABLE dan_test.class (
user_id string,
month string,
pv string
)
ROW format delimited FIELDS TERMINATED BY ",";
1.3 导⼊数据
load data local inpath "/home/centos/dan_" into table pview;
1.4 需求实现
1.4.1 需求分析
从最终的需求可以看出，我们计算的结果是随着⾏的变化⽽变化，我们把这类问题称为移动计算。在hivesql中其实解决此类问题我们是通过移动窗⼝来解决的，类似于spark中的滑动窗⼝。那么控制此类⾏的变化范围hive中给出了具体的⽅法--窗⼝⼦句。
1.4.2 窗⼝函数的理解
窗⼝：over()，分析函数如：row_number(),max(),lag()等。分析函数+窗⼝函数：窗⼝的本质就是指明
了分析函数分析数据时要处理的数据范围（作⽤域）。窗⼝分为静态窗⼝和移动窗⼝（也叫滑动窗⼝），静态窗⼝指分析数据的范围是固定不变的。滑动窗⼝指按照⾏的变化，窗⼝数据也随着变换，不同的⾏对应着不同的窗⼝数据（类似于与spark中的滑动窗⼝，随着时间的变化，窗⼝数据也发⽣着变
化）。窗⼝也是SQL编程的思维本质，就是对范围内的数据进⾏处理。
我们可以形象的把over()⼦句理解成开窗⼦句，即打开⼀个窗⼝，窗⼝内包含多条记录，over()会给每⼀⾏开⼀个窗⼝。如下图，总共有5
条记录，每⼀⾏代表⼀条记录，over()在每⼀条记录的基础上打开⼀个窗⼝，给r1记录打开w1窗⼝，窗⼝内只包含⾃⼰，给r2打开w2窗⼝，窗⼝内包含r1、r2，给r3打开w3窗⼝，窗⼝内包含r1、r2、r3，以此类推....
由上我们不难发现，在使⽤over()⼦句进⾏查询的时候， 不仅可以查询到每条记录的信息，还可以查询到这条记录对应窗⼝内的所有记录的聚合信息，所以我们通常结合聚合函数和over()⼦句⼀起使⽤。
1.4.3 窗⼝⼦句
窗⼝函数包括三个窗⼝⼦句。分组：partition by；排序：order by；窗⼝⼤⼩：rows.使⽤语法如下：
over(partition by xxx order by yyy rows between zzz)
窗⼝⼦句范围⼤⼩的控制：
rows或（range）⼦句往往来控制窗⼝边界范围的，其语法如下：
ROWS between CURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND  UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENT R 或
RANGE between [num] PRECEDING  AND [num]FOLLOWING
CURRENT ROW：当前⾏；
n PRECEDING：往前n⾏数据；
n FOLLOWING：往后n⾏数据；
rows函数的使用方法及实例UNBOUNDED：起点，UNBOUNDED PRECEDING 表⽰从前⾯的起点， UNBOUNDED FOLLOWING表⽰到后⾯的终点；
如下图：
窗⼝⼦句可以⽤来更精细的描述窗⼝，注意有⼏个函数是不⽀持窗⼝⼦句的：Rank, NTile,DenseRank,CumeDisk,PercentRank,Lead,Lag.⼦句意义
PRECEDING往前
FOLLOWING往后
CURRENT ROW当前⾏
UNBOUNDED起点（⼀般结合PRECEDING，FOLLOWING使⽤）
UNBOUNDED PRECEDING表⽰该窗⼝最前⾯的⾏（起点）
UNBOUNDED FOLLOWING表⽰该窗⼝最后⾯的⾏（终点）
⽤法实例：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从该窗⼝的起点到当前⾏
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING：从前2⾏到后1⾏
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW：从前2⾏到当前⾏
1.4.4 rows与range的区别
rows：rows是真实的⾏数，也就是我们实际中所说的1,连续的⾏数。
range:range是逻辑上的⾏数，所谓的逻辑⾏指的就是需要通过计算才能知道是哪⼀⾏。range后⾯跟计算表达式，对order by后⾯的某个字段值进⾏计算，计算后的结果表⽰其真正的范围。（逻辑偏移量构成）。
两者区别如下：
id 列
1
1
3
6
6
6
7
8
9
分析下⾯两个语句：
SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum
第⼀个为物理上的rows：表⽰从当前⾏为参考点，数据范围为前⼀⾏与后两⾏范围内求得的结果。数据范围为：
当前⾏为第⼀⾏时：数据范围如下图所⽰
sum（id）=1+1+3=5
当前⾏为第⼆⾏时：数据范围如下图所⽰
sun(id)=1+1+3+6=11
当前⾏为第三⾏时：数据范围如下图所⽰
sum(id) = 1+3 +6 +6=16
......
整个过程如下图所⽰：
整个窗⼝的变化过程就像按照每⼀⾏进⾏移动，移动的数据范围由窗⼝⼦句指定
第⼆个为逻辑上的range：数据的范围需要按照id进⾏计算。计算公式为：
RANGE BETWEEN 1 preceding AND 2 following。翻译为：当前⾏的值（此处为id的值，具体是以order by 后字段进⾏计算的）id-1=
当为第⼀⾏时：id=1，计算公式为id-1=
sum(id)=1+1+3=5
当为第⼆⾏时：id = 1，计算同上，0=
当为第三⾏时：id=3，计算公式为id-1=
sum(id)=3
当为第四⾏时：id=6, 计算公式为id-1=
sum(id) = 6+6+6+7+8=33
依次类推，计算出其他⾏。也就是按照物理⾏去移动，只不过窗⼝的数据范围不是物理⾏，⽽是需要计算，计算所得的值的范围所在的⾏。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。