开窗函数的使用铁律：不要滥用 先想明白自己要实现什么样的功能，然后再去实践。没有目的的尝试段不可取，会难以理解各开窗函数的真正用法。
一.构建数据
如果没有现成可用HIVE库，可以参见本人 大数据单机学习环境搭建 系列文章。
1.1Hive建表
-- 建表
drop table func_wins;
create table func_wins(
`id` bigint,
`name` string,
`trad_amt` int comment '营业金额',
`province` string,
`city` string)
row format serde
'org.apache.hadoop.hive.serde2.lazy.lazysimpleserde'
with serdeproperties (
'field.delim'=',',
'serialization.format'=',')
stored as inputformat
'org.apache.hadoop.mapred.textinputformat'
outputformat
'org.apache.hadoop.hive.ql.io.hiveignorekeytextoutputformat'
location
'hdfs://192.168.31.128:9000/user/hive/warehouse/func_wins'
;
1.2数据准备
1,张三,30,广东省,中山市
2,李四,30,广东省,佛山市
3,和尚,10,广东省,东莞市
4,政委,60,广东省,东莞市
5,李委,40,浙江省,金华市
6,王委,80,浙江省,台州市
7,张委,100,浙江省,宁波市
8,赵委,100,浙江省,衢州市
9,白委,50,浙江省,绍兴市
1.3保存为HDFS文件
# put数据入库
hadoop fs -put /home/func_wins.txt /user/hive/warehouse/func_wins/
1.4验证数据
select * from func_wins;
二.排序开窗
2.1函数特性row_number() over() 不存在重复的排名，即使 order by 的值完全一样也分大小，在取数必须唯一时特别适用；rank() over() 存在重复的排名，排名会占位置，符合日常生活中的排序方式，并列之后会出现空挡；dense_rank() over() 存在重复排名，且排名不占位置，所有的排序序号是连续的。
排序函数的基本规则：能靠前的肯定不会让靠后，5个人排名可能只有前4名，没有第5名，能并列第4绝对不会并列成第5。当排序函数与其它函数结果相似时，这一点非常重要，能否等价互换一定要慎重。具体差异见4.2章节内容。
2.2示例检验
-- row_number()无重复排序, rank()占位排序, dense_rank()不占位排序
select *
,row_number() over(partition by province order by trad_amt desc) rn
,rank() over(partition by province order by trad_amt desc) rk
,dense_rank() over(partition by province order by trad_amt desc) dr
from func_wins;
三.sum开窗（重点内容）
3.1累加与求和
-- sum()开窗加了order by 是累加不是求和
select *
-- 求和，分组内所有行
,sum(trad_amt) over(partition by province) sm_amt
-- 求和，分组内所有行
,sum(trad_amt) over(partition by province,city) sm_amt2
-- 累加，分组内逐个累加
,sum(trad_amt) over(partition by province order by id) addup_amt
-- 累加，无分组，不断累加直至结束
,sum(trad_amt) over(order by id) addup_amt2
from func_wins;
3.2窗口表达式
窗口表达式提供了控制行范围的能力，计算变的非常灵活，具体示例如下
关键字是 rows between, 选项如下
preceding 往前
following 往后
current row 当前行
unbounded 边界
unbounded preceding 表示从前面的起点
unbounded following 表示到后面的终点
select *
-- 1 累加
,sum(trad_amt) over(partition by province order by id) amt1
-- 2 求和, 分组内所有行
,sum(trad_amt) over(partition by province) amt2
-- 3 同2, 从头到尾，分组内所有行
,sum(trad_amt) over(partition by province order by id rows between unbounded preceding and unbounded following) amt3
-- 4 向前3行至当前行
,sum(trad_amt) over(partition by province order by id rows between 3 preceding and current row) amt4
-- 5 向前2行 向后1行
,sum(trad_amt) over(partition by province order by id rows between 2 preceding and 1 following) amt5
-- 6 当前行至最后一行
,sum(trad_amt) over(partition by province order by id rows between current row and unbounded following) amt6
from func_wins;
3.3场景模拟
示例 bal1:营业收入场景,已知两省份最终销售额都是1000,计算每笔 收入 后的累计销售金额，其中id代表业务产生的先后顺序；
示例 bal2:账户消费场景,已知两省份最终余额都是1000,计算每笔 开支 后账户余额，其中id代表消费产生的先后顺序；
示例 bal3:销售目标场景,两省份的销售目标都是1000,计算每一笔 交易 后距离目标还差多少，其中id代表业务产生的先后顺序；
select t.*,(1000-sm_amt+addup_amt) bal1,(1000+sm_amt-addup_amt) bal2,(1000-addup_amt) as bal3
from
(select *
,sum(trad_amt) over(partition by province) sm_amt
,sum(trad_amt) over(partition by province order by id rows between unbounded preceding and unbounded following) sm_amt2
,sum(trad_amt) over(partition by province order by id) addup_amt
from func_wins) t
;
四.count开窗
4.1计数规则
聚合函数的另一个常用开窗是count开窗——分组计数。当count() over()带有 order by 的时候，分组计的都是到当前order by值的数量，不同于排序函数，但可在特殊情况下使用此特性。
下方示例中，cnt1、cnt3、cnt4是较为常见的用法，其中cnt3、cnt4相结合的方式有被用于拉链表的案例。
-- count()开窗加了order by 相当于是rank()排序开窗函数了
select *
,count(1) over(partition by province) cnt1
,count(1) over(partition by province order by trad_amt) cnt2
,count(1) over(partition by province order by trad_amt rows BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) cnt2a
,count(1) over(partition by province,trad_amt) cnt3
,count(1) over(partition by province,trad_amt order by id) cnt4
from func_wins;
4.2计数与排序
开窗计数结果不等同于开窗排序结果，要留心区分两者差异，避免错误使用。
使用场景：各组排名前2的人员获奖，但每组获奖人员必须在2人以内，且要绝对公平，即并列排名第2的不能获奖，此时便可以使用count() over()满足要求。具体见下方数据差异。
select *
,count(1) over(partition by province order by trad_amt) cnt1
,rank() over(partition by province order by trad_amt) rk1
,dense_rank() over(partition by province order by trad_amt) dk1
,count(1) over(order by trad_amt) cnt2
,rank() over(order by trad_amt) rk2
,dense_rank() over(order by trad_amt) dk2
from func_wins;
五.max和min开窗
开窗获取分组内最大值最小值，用来计算差距、数据标准化都是有用的。
-- max()开窗 min()开窗
select *
,max(trad_amt) over(partition by province) max_amt
,max(trad_amt) over(partition by province,city) max_amt_asse
,min(trad_amt) over(partition by province) min_amt
,min(trad_amt) over(partition by province,city) min_amt_asse
from func_wins;
六.lead和lag开窗
数据的上下漂移，计算同环比时非常实用。
lag(col,N,default) 作用于col字段，向上取N行，如果向上为空给个默认值，没有默认值就是null
lead(col,N,default) 作用于col字段，向下取N行，如果向下为空给个默认值，没有默认值就是null
-- lead 和 lag, 数据的上线漂移
select *
,lag(trad_amt) over(partition by province order by id) lag_amt
,lead(trad_amt) over(partition by province order by id) lead_amt
,lead(trad_amt,1,0) over(partition by province order by id) lead_1_amt
,lead(trad_amt,2,0) over(partition by province order by id) lead_2_amt
from func_wins;
七.first_value和last_value开窗
last_value()默认从第一条到当条，与sum和count开窗相似，同样也可以通过窗口表达式灵活使用（3.2章节）。
-- last_value()默认从第一条到当条
select *
,first_value(trad_amt) over(partition by province order by id) first_amt
,first_value(trad_amt) over(partition by province order by id desc) first_amt2
,last_value(trad_amt) over(partition by province) last_amt
,last_value(trad_amt) over(partition by province order by id) last_amt2
,last_value(trad_amt) over(partition by province order by id rows between unbounded preceding and unbounded following) last_amt3
from func_wins;
八.ntile开窗
ntile 将每个分组内的数据分为指定的若干个桶里，并且为每一个桶分配一个桶编号。分配时会尽量平均分配，如果不能平均分配，优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
-- ntile 分桶函数
select *
,ntile(3) over(partition by province order by id) ntile_code
from func_wins;
最终提示：开窗函数虽好用，但不可滥用
纸上得来终觉浅，绝知此事要躬行。SQL之路只有一个标准答案——实践成真。文章来源：https://www.toymoban.com/news/detail-474436.html
声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，可收藏可转发但请勿转载，如有雷同纯属巧合文章来源地址https://www.toymoban.com/news/detail-474436.html
到了这里，关于SQL使用技巧(6)HIVE开窗函数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
