hive中控制窗口范围rows between的使用

1.rows between的作用

在使用hsql完成一些业务需求的时候,我们经常会用到窗口函数,某些场景
下会涉及到需要控制窗口函数的范围来满足业务需求,这个时候就需要用到rows
between了,使用rows between可以根据自己的需求任意的控制窗口函数的范围。

2.rows between的使用

首先我们有一张如下的数据表

sid	day_time	sale
101	2020-01-03	10
102	2020-01-04	20
103	2020-01-05	30
104	2020-01-06	40
104	2020-01-07	50
105	2020-02-05	33
106	2020-02-07	43
107	2020-02-08	53
108	2020-02-09	63
109	2020-02-10	73
110	2020-03-07	21
111	2020-03-08	31
112	2020-03-09	41
113	2020-03-10	51
114	2020-03-11	61
115	2020-03-12	71
116	2020-03-13	81

2.1 准备数据 

vim sale_year_month

101,2020-01-03,10
102,2020-01-04,20
103,2020-01-05,30
104,2020-01-06,40
104,2020-01-07,50
105,2020-02-05,33
106,2020-02-07,43
107,2020-02-08,53
108,2020-02-09,63
109,2020-02-10,73
110,2020-03-07,21
111,2020-03-08,31
112,2020-03-09,41
113,2020-03-10,51
114,2020-03-11,61
115,2020-03-12,71
116,2020-03-13,81

2.2 创建表，导入数据

create database test;

use test;

set hive.exec.mode.local.auto=true;

create external table sales_year_month(
 sid string,
 day_time string, 
 sale int
)
row format delimited fields terminated by ',' STORED AS TEXTFILE;

load data local inpath '/home/bigdataservice/load_data_from_kafka/sales_year_month.txt' into table sales_year_month;

2.3 实战需求

2.3.1 求当前日期销售额和后面所有日期的销售额总和。

这里就需要使用使用row between来控制范围，范围就是最后一行到当前行

sql如下：
# 语句一
SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(rows between current row and unbounded following) sum_sales
FROM
sales_year_month;

-- 语法解析
rows between current row and unbounded following
/* 这里面current row就是指当前行,unbounded following就是指最后一行,
and前面和后面的关系就是范围,从and后面的到and前面,但是and前面是不可以
使用unbounded following的,这样使用是错误的*/


# 语句二, 这种更好一些，因为题目隐含了需要按日期排序，所以语句指定按日期排序
SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(order by day_time ASC) sum_sales
FROM
sales_year_month;

查询结果如下：

sid	day_time	sale	sum_sales
101	2020-01-03	10	772
102	2020-01-04	20	762
103	2020-01-05	30	742
104	2020-01-06	40	712
104	2020-01-07	50	672
105	2020-02-05	33	622
106	2020-02-07	43	589
107	2020-02-08	53	546
108	2020-02-09	63	493
109	2020-02-10	73	430
110	2020-03-07	21	357
111	2020-03-08	31	336
112	2020-03-09	41	305
113	2020-03-10	51	264
114	2020-03-11	61	213
115	2020-03-12	71	152
116	2020-03-13	81	81

通过查询结果可以看出来,sum_salses这一列中的每一行都是后面所有行到
当前行的总和。

2.3.2.求当前日期销售额和截止到当前日期日期的销售额总和。

这里就需要使用使用row between来控制范围，范围就是最后一行到当前行
sql如下：

# 语句一
SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(rows between unbounded preceding and current row) sum_sales
FROM
sales_year_month;
-- 语法解析
rows between unbounded preceding and current row
/*这里的unbounded preceding就是第一行数据,current row就是当前行
数据,关于and前和and后的关系前面说过了这里就不细说了*/

# 语句二, 这种更好一些，因为题目隐含了需要按日期排序，所以语句指定按日期排序
SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(order by day_time DESC) sum_sales
FROM
sales_year_month;

查询结果数据如下：

sid	day_time	sale_volume	sum_sales
101	2020-01-03	10	10
102	2020-01-04	20	30
103	2020-01-05	30	60
104	2020-01-06	40	100
104	2020-01-07	50	150
105	2020-02-05	33	183
106	2020-02-07	43	226
107	2020-02-08	53	279
108	2020-02-09	63	342
109	2020-02-10	73	415
110	2020-03-07	21	436
111	2020-03-08	31	467
112	2020-03-09	41	508
113	2020-03-10	51	559
114	2020-03-11	61	620
115	2020-03-12	71	691
116	2020-03-13	81	772

通过查询结果可以看出来,sum_salses这一列中的每一行都是前面所有行到
当前行的总和。

2.3.3 求当前日期和后面两天销售额的总和。

sql如下：

SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(rows between current row and 2 following) sum_sales
FROM
sales_year_month;
-- 语法解析
/*current row是指当前行,2 following就是后两行数据*/

查询结果如下：

sid	day_time	sale	sum_sales
101	2020-01-03	10	60
102	2020-01-04	20	90
103	2020-01-05	30	120
104	2020-01-06	40	123
104	2020-01-07	50	126
105	2020-02-05	33	129
106	2020-02-07	43	159
107	2020-02-08	53	189
108	2020-02-09	63	157
109	2020-02-10	73	125
110	2020-03-07	21	93
111	2020-03-08	31	123
112	2020-03-09	41	153
113	2020-03-10	51	183
114	2020-03-11	61	213
115	2020-03-12	71	152
116	2020-03-13	81	81

通过查询结果可以看出来,sum_salses这一列中的每一行都是后面两行到
当前行的总和。

2.3.4. 求当前日期和前一天销售额的总和。

sql如下：

SELECT
  sid,
  day_time,
  sale,
  sum(sale) over(rows between 1 preceding and current row) sum_sales
FROM
sales_year_month;
-- 语法解析
/*1 preceding 就是指上一行数据,current row就是指当前行数据*/

查询结果如下：

sid	day_time	sale	sum_sales
101	2020-01-03	10	10
102	2020-01-04	20	30
103	2020-01-05	30	50
104	2020-01-06	40	70
104	2020-01-07	50	90
105	2020-02-05	33	83
106	2020-02-07	43	76
107	2020-02-08	53	96
108	2020-02-09	63	116
109	2020-02-10	73	136
110	2020-03-07	21	94
111	2020-03-08	31	52
112	2020-03-09	41	72
113	2020-03-10	51	92
114	2020-03-11	61	112
115	2020-03-12	71	132
116	2020-03-13	81	152

通过查询结果可以看出来,sum_salses这一列中的每一行都是前面一行与
当前行的总和。

总结

通过以上几个例子可以看到，在已经按按照要求排好序的情况下，使用rows between可以很灵活的控制窗口函数的作用范围, 否则应当在over窗口内指定排序字段，这个在日常业务中如果能灵活使用可以很好的加快我们的工作效率