0


SQL使用技巧(6)HIVE开窗函数

专题:SQL使用技巧——实践是检验SQL函数的唯一标准

开窗函数的使用铁律:不要滥用 先想明白自己要实现什么样的功能,然后再去实践。没有目的的尝试段不可取,会难以理解各开窗函数的真正用法。

一.构建数据

如果没有现成可用HIVE库,可以参见本人 大数据单机学习环境搭建 系列文章。
1.1Hive建表

-- 建表droptable func_wins;createtable func_wins(`id`bigint,`name` string,`trad_amt`intcomment'营业金额',`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(partitionby province orderby trad_amt desc) rn
  ,rank()over(partitionby province orderby trad_amt desc) rk
  ,dense_rank()over(partitionby province orderby trad_amt desc) dr
from func_wins;

在这里插入图片描述

三.sum开窗(重点内容)

3.1累加与求和

-- sum()开窗加了order by 是累加不是求和select*-- 求和,分组内所有行,sum(trad_amt)over(partitionby province) sm_amt
  -- 求和,分组内所有行,sum(trad_amt)over(partitionby province,city) sm_amt2
  -- 累加,分组内逐个累加,sum(trad_amt)over(partitionby province orderby id) addup_amt
  -- 累加,无分组,不断累加直至结束,sum(trad_amt)over(orderby 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(partitionby province orderby id) amt1
  -- 2 求和, 分组内所有行,sum(trad_amt)over(partitionby province) amt2
  -- 3 同2, 从头到尾,分组内所有行,sum(trad_amt)over(partitionby province orderby id rowsbetweenunboundedprecedingandunboundedfollowing) amt3
  -- 4 向前3行至当前行,sum(trad_amt)over(partitionby province orderby id rowsbetween3precedingandcurrentrow) amt4
  -- 5 向前2行 向后1行,sum(trad_amt)over(partitionby province orderby id rowsbetween2precedingand1following) amt5
  -- 6 当前行至最后一行,sum(trad_amt)over(partitionby province orderby id rowsbetweencurrentrowandunboundedfollowing) 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(partitionby province) sm_amt
  ,sum(trad_amt)over(partitionby province orderby id rowsbetweenunboundedprecedingandunboundedfollowing) sm_amt2
  ,sum(trad_amt)over(partitionby province orderby 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(partitionby province) cnt1
  ,count(1)over(partitionby province orderby trad_amt) cnt2
  ,count(1)over(partitionby province orderby trad_amt rowsBETWEENUNBOUNDEDPRECEDINGandUNBOUNDEDFOLLOWING) cnt2a
  ,count(1)over(partitionby province,trad_amt) cnt3
  ,count(1)over(partitionby province,trad_amt orderby id) cnt4
from func_wins;

在这里插入图片描述

4.2计数与排序

开窗计数结果不等同于开窗排序结果,要留心区分两者差异,避免错误使用。
使用场景:各组排名前2的人员获奖,但每组获奖人员必须在2人以内,且要绝对公平,即并列排名第2的不能获奖,此时便可以使用count() over()满足要求。具体见下方数据差异。

select*,count(1)over(partitionby province orderby trad_amt) cnt1
  ,rank()over(partitionby province orderby trad_amt) rk1
  ,dense_rank()over(partitionby province orderby trad_amt) dk1
  ,count(1)over(orderby trad_amt) cnt2
  ,rank()over(orderby trad_amt) rk2
  ,dense_rank()over(orderby trad_amt) dk2
from func_wins;

在这里插入图片描述

五.max和min开窗

开窗获取分组内最大值最小值,用来计算差距、数据标准化都是有用的。

-- max()开窗 min()开窗select*,max(trad_amt)over(partitionby province) max_amt
  ,max(trad_amt)over(partitionby province,city) max_amt_asse
  ,min(trad_amt)over(partitionby province) min_amt
  ,min(trad_amt)over(partitionby 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(partitionby province orderby id) lag_amt
  ,lead(trad_amt)over(partitionby province orderby id) lead_amt
  ,lead(trad_amt,1,0)over(partitionby province orderby id) lead_1_amt
  ,lead(trad_amt,2,0)over(partitionby province orderby 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(partitionby province orderby id) first_amt
  ,first_value(trad_amt)over(partitionby province orderby id desc) first_amt2
  ,last_value(trad_amt)over(partitionby province) last_amt
  ,last_value(trad_amt)over(partitionby province orderby id) last_amt2
  ,last_value(trad_amt)over(partitionby province orderby id rowsbetweenunboundedprecedingandunboundedfollowing) last_amt3
from func_wins;

在这里插入图片描述

八.ntile开窗

ntile

将每个分组内的数据分为指定的若干个桶里,并且为每一个桶分配一个桶编号。分配时会尽量平均分配,如果不能平均分配,优先分配较小编号的桶,并且各个桶中能放的行数最多相差1。

-- ntile 分桶函数select*,ntile(3)over(partitionby province orderby id) ntile_code
from func_wins;

在这里插入图片描述

最终提示:开窗函数虽好用,但不可滥用

纸上得来终觉浅,绝知此事要躬行。SQL之路只有一个标准答案——实践成真。


声明:本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考,不构成实际商业建议,可收藏可转发但请勿转载,如有雷同纯属巧合

标签: sql hive

本文转载自: https://blog.csdn.net/LMTX069/article/details/130955484
版权归原作者 赫加青空 所有, 如有侵权,请联系我们删除。

“SQL使用技巧(6)HIVE开窗函数”的评论:

还没有评论