Html5移动网站微信群推广网站
5个小案例说清楚-窗口函数（开窗函数）
结论先行:
首先，写开窗函数一定要先理解开窗的定义和为什么开窗，开窗分几种场景；
写sql，需要心平气和，一步一步进行推导，这样可以找到写sql的逻辑，最终达到万变不离其宗。
over(): 就是为每条数据都开启一个窗口. 窗口的大小默认为当前数据集的大小.
over(partition by…): 会按照分区字段将数据分到不同的区中.
会为每个分区中的每条数据都开启一个窗口.
窗口的大小默认为当前分区数据集的大小
over(order by …): 会为每条数据都开启一个窗口,窗口的大小默认为起点到当前行
over(rows between … and …) :划分窗口大小
over(partition by … order by … ) :
会按照分区字段将数据分到不同的区中.
会为每个分区中的每条数据都开启一个窗口.
窗口的大小默认为当前分区数据集中起点到当前行
1.相关函数说明
over():指定分析函数工作的数据窗口大小,也就是可操作的数据集大小，这个数据窗口大小可能会随着行的改变而变化；
current row:当前行
n preceding:往前n行数据
n following:往后n行数据
unbounded:起点
unbounded preceding:表示到前面的起点
unbounded following:表示到后面的重点
lag(col, n, default_val):往前第n行数据
lead(col, n, default_val):往后第n行数据
ntile(n):把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。注意：n必须为int类型
2.准备表和数据：
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
--创建表
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
--导入数据
load data local inpath "/opt/module/hive-3.1.2/datas/business.txt" into table business;
需求一:
查询在2017年4月份购买过的顾客及总人数
分步分析得出：
a)查询2017年4月份的数据
b)查询购买过的顾客名称
c)求总人数这里用到了开窗函数over()表示在所有的当前数据集中。
注意：要一步一步分析，切记上来囫囵吞枣，没有搞清楚需求，就开始写
--a)查询2017年4月份的数据
selectname,orderdate,cost
frombusiness
where orderdate like '2017-04%'; =>t1
+-------+-------------+-------+
| name | orderdate | cost |
+-------+-------------+-------+
| jack | 2017-04-06 | 42 |
| mart | 2017-04-08 | 62 |
| mart | 2017-04-09 | 68 |
| mart | 2017-04-11 | 75 |
| mart | 2017-04-13 | 94 |
+-------+-------------+-------+
-- b)查询购买过的顾客名称
selectdistinct t1.name
fromt1 ; => t2
-- c)求总人数
selectt2.name,count(t2.name) over() total_num
fromt2 ;
-- d)组装
selectt2.name,count(t2.name) over() total_num
from(
selectdistinct t1.name
from(
selectname,orderdate,cost
frombusiness
where orderdate like '2017-04%')t1 )t2 ; --结果可得
+----------+------------+
| t2.name | total_num |
+----------+------------+
| mart | 2 |
| jack | 2 |
+----------+------------+
需求二：
查询顾客的购买明细及月购买总额
分析：
每行的窗口数据为该月份本人的购买次数
所以需要按照月份进行分区
需要用到 over(partition by monthNum)此题可以扩展成两个题；
扩展1
查询顾客的购买明细及所有顾客月购买总额
selectname,orderdate,cost,sum(cost) over(partition by month(orderdate)) total_cost
frombusiness ;
--结果可得
+-------+-------------+-------+-------------+
| name | orderdate | cost | total_cost |
+-------+-------------+-------+-------------+
| jack | 2017-01-01 | 10 | 205 |
| jack | 2017-01-08 | 55 | 205 |
| tony | 2017-01-07 | 50 | 205 |
| jack | 2017-01-05 | 46 | 205 |
| tony | 2017-01-04 | 29 | 205 |
| tony | 2017-01-02 | 15 | 205 |
| jack | 2017-02-03 | 23 | 23 |
| mart | 2017-04-13 | 94 | 341 |
| jack | 2017-04-06 | 42 | 341 |
| mart | 2017-04-11 | 75 | 341 |
| mart | 2017-04-09 | 68 | 341 |
| mart | 2017-04-08 | 62 | 341 |
| neil | 2017-05-10 | 12 | 12 |
| neil | 2017-06-12 | 80 | 80 |
+-------+-------------+-------+-------------+
扩展1
查询顾客的购买明细及每个顾客的月购买总额
selectname,orderdate,cost,sum(cost) over(partition by name, month(orderdate)) total_cost
frombusiness ;
--查询结果
+-------+-------------+-------+-------------+
| name | orderdate | cost | total_cost |
+-------+-------------+-------+-------------+
| jack | 2017-01-05 | 46 | 111 |
| jack | 2017-01-08 | 55 | 111 |
| jack | 2017-01-01 | 10 | 111 |
| jack | 2017-02-03 | 23 | 23 |
| jack | 2017-04-06 | 42 | 42 |
| mart | 2017-04-13 | 94 | 299 |
| mart | 2017-04-11 | 75 | 299 |
| mart | 2017-04-09 | 68 | 299 |
| mart | 2017-04-08 | 62 | 299 |
| neil | 2017-05-10 | 12 | 12 |
| neil | 2017-06-12 | 80 | 80 |
| tony | 2017-01-04 | 29 | 94 |
| tony | 2017-01-02 | 15 | 94 |
| tony | 2017-01-07 | 50 | 94 |
+-------+-------------+-------+-------------+
14 rows selected (20.778 seconds)需求三：
求每个顾客的购买明细及将每个顾客的cost按照日期进行累加分析：窗口函数用到了order by 和 rows between unbounded preceding and current row
selectname,orderdate,cost,sum(cost) over(order by orderdate rows between unbounded preceding and current row)
frombusiness ;
-- 查询结果
+-------+-------------+-------+---------------+
| name | orderdate | cost | sum_window_0 |
+-------+-------------+-------+---------------+
| jack | 2017-01-01 | 10 | 10 |
| tony | 2017-01-02 | 15 | 25 |
| tony | 2017-01-04 | 29 | 54 |
| jack | 2017-01-05 | 46 | 100 |
| tony | 2017-01-07 | 50 | 150 |
| jack | 2017-01-08 | 55 | 205 |
| jack | 2017-02-03 | 23 | 228 |
| jack | 2017-04-06 | 42 | 270 |
| mart | 2017-04-08 | 62 | 332 |
| mart | 2017-04-09 | 68 | 400 |
| mart | 2017-04-11 | 75 | 475 |
| mart | 2017-04-13 | 94 | 569 |
| neil | 2017-05-10 | 12 | 581 |
| neil | 2017-06-12 | 80 | 661 |
+-------+-------------+-------+---------------+扩展需求：
求每个顾客的购买明细及
起点到当前行的累加
上一行到当前行的累加
当前行到下一行的累加
上一行到下一行的累加
当前行到终点的累加
selectname,orderdate,cost,sum(cost) over(order by orderdate rows between unbounded preceding and current row) up_c,sum(cost) over(order by orderdate rows between 1 preceding and current row) 1p_c,sum(cost) over(order by orderdate rows between current row and 1 following) c_1f,sum(cost) over(order by orderdate rows between 1 preceding and 1 following) 1p_1f,sum(cost) over(order by orderdate rows between current row and unbounded following) c_uf
frombusiness;
--执行结果
+-------+-------------+-------+-------+-------+-------+--------+-------+
| name | orderdate | cost | up_c | 1p_c | c_1f | 1p_1f | c_uf |
+-------+-------------+-------+-------+-------+-------+--------+-------+
| jack | 2017-01-01 | 10 | 10 | 10 | 25 | 25 | 661 |
| tony | 2017-01-02 | 15 | 25 | 25 | 44 | 54 | 651 |
| tony | 2017-01-04 | 29 | 54 | 44 | 75 | 90 | 636 |
| jack | 2017-01-05 | 46 | 100 | 75 | 96 | 125 | 607 |
| tony | 2017-01-07 | 50 | 150 | 96 | 105 | 151 | 561 |
| jack | 2017-01-08 | 55 | 205 | 105 | 78 | 128 | 511 |
| jack | 2017-02-03 | 23 | 228 | 78 | 65 | 120 | 456 |
| jack | 2017-04-06 | 42 | 270 | 65 | 104 | 127 | 433 |
| mart | 2017-04-08 | 62 | 332 | 104 | 130 | 172 | 391 |
| mart | 2017-04-09 | 68 | 400 | 130 | 143 | 205 | 329 |
| mart | 2017-04-11 | 75 | 475 | 143 | 169 | 237 | 261 |
| mart | 2017-04-13 | 94 | 569 | 169 | 106 | 181 | 186 |
| neil | 2017-05-10 | 12 | 581 | 106 | 92 | 186 | 92 |
| neil | 2017-06-12 | 80 | 661 | 92 | 80 | 92 | 80 |
+-------+-------------+-------+-------+-------+-------+--------+-------+
14 rows selected (17.403 seconds)
需求四：
查询每个顾客上次 和 下次 的购买时间
分析：
lag(col, n, default_val):往前第n行数据
lead(col, n, default_val):往后第n行数据
selectname,orderdate,cost,lag(orderdate, 1, '1977-01-01') over(partition by name order by orderdate) pre_ord,lead(orderdate, 1, '9999-01-01') over(partition by name order by orderdate) nex_ord
frombusiness;
--结果
+-------+-------------+-------+-------------+-------------+
| name | orderdate | cost | pre_ord | nex_ord |
+-------+-------------+-------+-------------+-------------+
| jack | 2017-01-01 | 10 | 1977-01-01 | 2017-01-05 |
| jack | 2017-01-05 | 46 | 2017-01-01 | 2017-01-08 |
| jack | 2017-01-08 | 55 | 2017-01-05 | 2017-02-03 |
| jack | 2017-02-03 | 23 | 2017-01-08 | 2017-04-06 |
| jack | 2017-04-06 | 42 | 2017-02-03 | 9999-01-01 |
| mart | 2017-04-08 | 62 | 1977-01-01 | 2017-04-09 |
| mart | 2017-04-09 | 68 | 2017-04-08 | 2017-04-11 |
| mart | 2017-04-11 | 75 | 2017-04-09 | 2017-04-13 |
| mart | 2017-04-13 | 94 | 2017-04-11 | 9999-01-01 |
| neil | 2017-05-10 | 12 | 1977-01-01 | 2017-06-12 |
| neil | 2017-06-12 | 80 | 2017-05-10 | 9999-01-01 |
| tony | 2017-01-02 | 15 | 1977-01-01 | 2017-01-04 |
| tony | 2017-01-04 | 29 | 2017-01-02 | 2017-01-07 |
| tony | 2017-01-07 | 50 | 2017-01-04 | 9999-01-01 |
+-------+-------------+-------+-------------+-------------+需求五：
需求五: 查询前20%时间的订单信息分析：ntile(n):把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。注意：n必须为int类型
--将数据分成5组
selectname,orderdate,cost,ntile(5) over(order by orderdate) gid
frombusiness ; =>t1
--取第一组的数据
selectt1.name,t1.orderdate,t1.cost
fromt1
where t1.gid = 1;
--组合
selectt1.name,t1.orderdate,t1.cost
from(
selectname,orderdate,cost,ntile(5) over(order by orderdate) gid
frombusiness )t1 where t1.gid = 1 ;
-- 结果
+----------+---------------+----------+
| t1.name | t1.orderdate | t1.cost |
+----------+---------------+----------+
| jack | 2017-01-01 | 10 |
| tony | 2017-01-02 | 15 |
| tony | 2017-01-04 | 29 |
+----------+---------------+----------+