Hive 窗口函数大全

这篇具有很好参考价值的文章主要介绍了Hive 窗口函数大全。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

窗口函数概述

窗口序列函数

row_number

dense_rank

窗口边界

滑动窗口

lag 获取上一行数据

lead 获取下一行数据

窗口专用计算函数

sum累加函数

max最大值

min最小值

avg平均值

count累计次数

first_value首行值

last_value末行值

cume_dist分布统计

percent_rank 秩分析函数

nitle数据切片函数


窗口函数概述

over窗口函数说明:

function(arg) over (partition by {partition columns} order by {order columns} desc/asc)

partition columns:当前行中根据指定的列对partition columns列相同值归到一个分区中;

order columns:在相同值的partition columns列分区中,按照order columns列值进行排序,可以指定升序或是降序,默认是升序

function(arg):对应的窗口数据计算函数

窗口序列函数

row_number

在窗口内会对所有数值,输出不同的序号,序号唯一且连续,如:1、2、3、4、5。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。

示例:

SELECT 
ROW_NUMBER() OVER (PARTITION BY province ) AS row_number 
,user_id 
,province 
FROM tmp_cube

结果

row_number    user_id    province
1    137    云南省
2    139    云南省
3    138    云南省
4    136    云南省
5    135    云南省
6    140    云南省
1    133    北京
2    132    北京
3    134    北京
1    124    广东省
2    127    广东省
3    151    广东省
4    123    广东省
5    225    广东省
6    126    广东省

会对相同数值,输出相同的序号,而且下一个序号间断,如:1、1、3、3、5。

示例

SELECT rank() OVER (PARTITION BY province order by part ) AS rank
        ,province
        ,part
FROM    tmp_cube

结果

rank    province    part
1    云南省    01
1    云南省    01
3    云南省    02
3    云南省    02
5    云南省    03
5    云南省    03
1    北京    01
2    北京    02
3    北京    03
1    广东省    01
1    广东省    01
3    广东省    02
3    广东省    02
5    广东省    03
5    广东省    03

dense_rank

会对相同数值,输出相同的序号,但下一个序号不间断,如:1、1、2、2、3。

示例:

SELECT 
dense_rank() OVER (PARTITION BY province order by part ) AS dense_rank 
,province 
,part 
FROM tmp_cube ;

结果

dense_rank    province    part
1    云南省    01
1    云南省    01
2    云南省    02
2    云南省    02
3    云南省    03
3    云南省    03
1    北京    01
2    北京    02
3    北京    03
1    广东省    01
1    广东省    01
2    广东省    02
2    广东省    02
3    广东省    03
3    广东省    03

窗口边界

控制窗口范围,必须配合over窗口的order by排序

参数解释:

n行数

unbounded 不限行数(修饰preceding和following) preceding 在前N行 following 在后N行 current row 当前行

举例说明 :

-- 窗口中整个的范围(over 窗口函数默认是整个窗口范围)

rows between unbounded preceding and unbounded following

-- 从 前无限行 到 当前行

rows between unbounded preceding and current row

-- 从 当前行的前2行 到 当前行

rows between 2 preceding and current row

-- 从 当前行 到 当前行后2行

rows between current row and 2 following

-- 当前行 到 后不限行 rows between current row and unbounded following

滑动窗口

lag 获取上一行数据

LAG(col,n):配合over使用,取窗口范围往前第 n 行数据的值

lead 获取下一行数据

LEAD(col,n):配合over使用,取窗口范围往后第 n 行数据的值

窗口专用计算函数

sum累加函数

实现效果:按照yyyymm统计截至到当前行的sum(num)值;

sum(num) over(partition by user_id,yyyy order by yyyymm asc )
SELECT user_id ,yyyymm ,integral ,sum(integral) over (partition by user_id order by yyyymm) as sum FROM user_totaluser_total ;

结果

user_id    yyyymm    integral    sum
195    202206    20060.0    20060.0
195    202207    23028.0    43088.0
195    202208    20150.0    63238.0
195    202209    20170.0    83408.0
195    202210    20284.0    103692.0
195    202211    20150.0    123842.0
195    202212    20944.0    144786.0
195    202301    \N    144786.0
400    202206    0.0    0.0
400    202207    20384.0    20384.0
400    202208    20150.0    40534.0
400    202209    0.0    40534.0
400    202210    20150.0    60684.0
400    202211    0.0    60684.0
400    202212    0.0    60684.0
400    202301    \N    60684.0
405    202206    0.0    0.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    52502.0
405    202210    25916.0    78418.0
405    202211    0.0    78418.0
405    202212    0.0    78418.0

实现效果:每一个都是的sum(num)值;

sum(num) over(partition by user_id,yyyy )
SELECT 
user_id 
,yyyymm 
,integral 
,sum(integral) over (partition by user_id) as sum 
FROM user_totaluser_total ;

结果

user_id    yyyymm    integral    sum
195    202301    \N    144786.0
195    202206    20060.0    144786.0
195    202207    23028.0    144786.0
195    202208    20150.0    144786.0
195    202209    20170.0    144786.0
195    202210    20284.0    144786.0
195    202211    20150.0    144786.0
195    202212    20944.0    144786.0
400    202301    \N    60684.0
400    202206    0.0    60684.0
400    202207    20384.0    60684.0
400    202208    20150.0    60684.0
400    202209    0.0    60684.0
400    202210    20150.0    60684.0
400    202211    0.0    60684.0
400    202212    0.0    60684.0
405    202207    38852.0    78418.0
405    202206    0.0    78418.0
405    202209    13650.0    78418.0
405    202208    0.0    78418.0
405    202210    25916.0    78418.0
405    202211    0.0    78418.0
405    202212    0.0    78418.0

max最大值

min(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,max(integral) over (partition by user_id)
 FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    23028.0
195    202208    20150.0    23028.0
195    202209    20170.0    23028.0
195    202206    20060.0    23028.0
195    202210    20284.0    23028.0
195    202211    20150.0    23028.0
195    202212    20944.0    23028.0
195    202301    \N    23028.0
400    202209    0.0    20384.0
400    202206    0.0    20384.0
400    202207    20384.0    20384.0
400    202208    20150.0    20384.0
400    202210    20150.0    20384.0
400    202211    0.0    20384.0
400    202212    0.0    20384.0
400    202301    \N    20384.0
405    202206    0.0    38852.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    38852.0
405    202210    25916.0    38852.0
405    202211    0.0    38852.0
405    202212    0.0    38852.0

min最小值

min(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,min(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    23028.0
195    202208    20150.0    23028.0
195    202209    20170.0    23028.0
195    202206    20060.0    23028.0
195    202210    20284.0    23028.0
195    202211    20150.0    23028.0
195    202212    20944.0    23028.0
195    202301    \N    23028.0
400    202209    0.0    20384.0
400    202206    0.0    20384.0
400    202207    20384.0    20384.0
400    202208    20150.0    20384.0
400    202210    20150.0    20384.0
400    202211    0.0    20384.0
400    202212    0.0    20384.0
400    202301    \N    20384.0
405    202206    0.0    38852.0
405    202207    38852.0    38852.0
405    202208    0.0    38852.0
405    202209    13650.0    38852.0
405    202210    25916.0    38852.0
405    202211    0.0    38852.0
405    202212    0.0    38852.0

avg平均值

avg(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,avg(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    20683.714285714286
195    202208    20150.0    20683.714285714286
195    202209    20170.0    20683.714285714286
195    202206    20060.0    20683.714285714286
195    202210    20284.0    20683.714285714286
195    202211    20150.0    20683.714285714286
195    202212    20944.0    20683.714285714286
195    202301    \N    20683.714285714286
400    202209    0.0    8669.142857142857
400    202206    0.0    8669.142857142857
400    202207    20384.0    8669.142857142857
400    202208    20150.0    8669.142857142857
400    202210    20150.0    8669.142857142857
400    202211    0.0    8669.142857142857
400    202212    0.0    8669.142857142857
400    202301    \N    8669.142857142857
405    202206    0.0    11202.57142857143
405    202207    38852.0    11202.57142857143
405    202208    0.0    11202.57142857143
405    202209    13650.0    11202.57142857143
405    202210    25916.0    11202.57142857143
405    202211    0.0    11202.57142857143
405    202212    0.0    11202.57142857143

count累计次数

count(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,count(integral) over (partition by user_id) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202207    23028.0    7
195    202208    20150.0    7
195    202209    20170.0    7
195    202206    20060.0    7
195    202210    20284.0    7
195    202211    20150.0    7
195    202212    20944.0    7
195    202301    \N    7
400    202209    0.0    7
400    202206    0.0    7
400    202207    20384.0    7
400    202208    20150.0    7
400    202210    20150.0    7
400    202211    0.0    7
400    202212    0.0    7
400    202301    \N    7
405    202206    0.0    7
405    202207    38852.0    7
405    202208    0.0    7
405    202209    13650.0    7
405    202210    25916.0    7
405    202211    0.0    7
405    202212    0.0    7

first_value首行值

first_value(expr) OVER([partition_by_clause] order_by_clause [window_clause]);

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,first_value(yyyymm) over (partition by user_id order by yyyymm) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202206    20060.0    202206
195    202207    23028.0    202206
195    202208    20150.0    202206
195    202209    20170.0    202206
195    202210    20284.0    202206
195    202211    20150.0    202206
195    202212    20944.0    202206
195    202301    \N    202206
400    202206    0.0    202206
400    202207    20384.0    202206
400    202208    20150.0    202206
400    202209    0.0    202206
400    202210    20150.0    202206
400    202211    0.0    202206
400    202212    0.0    202206
400    202301    \N    202206
405    202206    0.0    202206
405    202207    38852.0    202206
405    202208    0.0    202206
405    202209    13650.0    202206
405    202210    25916.0    202206
405    202211    0.0    202206
405    202212    0.0    202206

last_value末行值

last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause])

示例:

SELECT 
user_id 
,yyyymm 
,integral 
,last_value(yyyymm) over (partition by user_id order by yyyymm) 
FROM user_total

结果

user_id    yyyymm    integral    _c3
195    202206    20060.0    202206
195    202207    23028.0    202207
195    202208    20150.0    202208
195    202209    20170.0    202209
195    202210    20284.0    202210
195    202211    20150.0    202211
195    202212    20944.0    202212
195    202301    \N    202301
400    202206    0.0    202206
400    202207    20384.0    202207
400    202208    20150.0    202208
400    202209    0.0    202209
400    202210    20150.0    202210
400    202211    0.0    202211
400    202212    0.0    202212
400    202301    \N    202301
405    202206    0.0    202206
405    202207    38852.0    202207
405    202208    0.0    202208
405    202209    13650.0    202209
405    202210    25916.0    202210
405    202211    0.0    202211
405    202212    0.0    202212

cume_dist分布统计

如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number ofrows)。

如果是降序排列,则统计:大于等于当前值的行数/总行数

示例:

统计小于等于当前工资的人数占总人数的比例

SELECT 
name
, dept_no
, salary
, cume_dist() OVER (ORDER BY salary) as cume_dist 
FROM data;

结果:

+-------+-------+------+---------+
|name   |dept_no|salary|cume_dist|
+-------+-------+------+---------+
|rose   |2      |4000  |0.125    |
|jack   |2      |5000  |0.375    |
|steven |3      |5000  |0.375    |
|john   |1      |6000  |0.5      |
|jerry  |2      |6600  |0.625    |
|tom    |1      |8000  |0.75     |
|richard|3      |9000  |0.875    |
|mike   |1      |10000 |1.0      |
+-------+-------+------+---------+

根据部门统计小于等于当前工资的人数占部门总人数的比例:

SELECT 
name
, dept_no
, salary
, cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_dist 
FROM data;

percent_rank 秩分析函数

返回order by列的百分比排名;

计算逻辑:(RANK-1)/(N-1)

即:(rank - 1) / (the number of rows in the window or partition - 1)

select row,value,rank() over() ,PERCENT_RANK() over(partition by 1 order by value) from tablename;

结果:

Row#    Value    Rank    Calculation    PERCENT_RANK
1    15    1    (1-1)/(7-1)    0.0000
2    20    2    (2-1)/(7-1)    0.1666
3    20    2    (2-1)/(7-1)    0.1666
4    20    2    (2-1)/(7-1)    0.1666
5    30    5    (5-1)/(7-1)    0.6666
6    30    5    (5-1)/(7-1)    0.6666
7    40    7    (7-1)/(7-1)    1.0000

nitle数据切片函数

nitle(n),n指将分组内的数据按照order列进行排序切分成n个区,排名序号依次排名为1,2,3,4,5.....,并返回数据切片排名序号;

如,各地区销售额排名:

select 
province
,yyyymm
,gvm
,ntile(5) over(partition by province order by gvm desc) 
from total

结果:

province    yyyymm    gvm    _c3
上海    202210    3416560    1
上海    202206    3050450    1
上海    202207    2974400    2
上海    202209    2611310    2
上海    202208    2353780    3
上海    202205    2002650    3
上海    202204    1556750    4
上海    202211    1510340    5
云南省    202207    3819660    1
云南省    202204    3605550    1
云南省    202210    3493000    2
云南省    202206    3432000    2
云南省    202205    3272100    3
云南省    202209    3123720    3
云南省    202208    3089060    4
云南省    202211    1853150    5

后续可以根据ntile(5)的结果挑选第几切片的数据出来;通常结合n值,用于筛选前20%、10%等数据;

根据上面示例,求各地销售额前20%的数据:

(求前20%,即将数据切分城5份,取第一份数据即可)文章来源地址https://www.toymoban.com/news/detail-613412.html

select * from 
( select province,yyyymm,gvm,ntile(5) as ntile over(partition by province order by gvm desc) from total ) 
where ntile=1

到了这里,关于Hive 窗口函数大全的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • (07)Hive——窗口函数详解

            窗口函数可以拆分为【窗口+函数】。窗口函数官网指路: LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundation https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics 窗口: over(),指明函数要处理的 数据范围 函数: 指明函数 计算逻辑 window_nam

    2024年02月19日
    浏览(36)
  • Hive窗口函数全解

    在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数

    2024年02月03日
    浏览(49)
  • Hive窗口函数整理

    Hive 中的窗口函数允许你在结果集的一个特定“窗口”内对行进行计算。这些窗口可以是物理的(基于行在数据中的实际位置)或逻辑的(基于行的一些排序标准)。窗口函数在处理排名、计算累计和或计算移动平均值等问题时特别有用。 以下是一些 Hive 中的常见窗口函数:

    2024年01月18日
    浏览(44)
  • Hive--时间函数大全

    注:第二个参数为时间格式,默认是’yyyy-MM-dd HH:mm:ss’ 注:to_date() 默认转化格式为’yyyy-MM-dd’ 注:next_day()第⼆个参数⽀持⼩写、⼤写、缩写(su/sun/sunday)

    2024年02月07日
    浏览(45)
  • hive函数大全

    在hive内部有许多函数,如下: 内置运算符 关系运算符 算术运算符 逻辑运算符 复杂类型函数 内置函数内置聚合函数 数学函数 收集函数 类型转换函数 日期函数 条件函数 字符函数 内置聚合函数 内置表生成函数 1.1关系运算符 等值比较: = 等值比较:= 不等值比较: 和!= 小于比

    2024年02月14日
    浏览(34)
  • Hive窗口函数-lead/lag函数

    前面我们学习的first_value和last_value 取的是排序后的数据截止当前行的第一行数据和最后一行数据 Lag和Lead分析函数可以在一次查询中取出当前行后N行和前N行的数据,虽然可以不用排序,但是往往只有在排序的场景下取前面或者后面N 行数据才有意义 这种操作可以代替表的自

    2024年02月16日
    浏览(58)
  • hive窗口函数计算累加值

    rows是物理窗口,是哪一行就是哪一行,与当前行的值(order by key的key的值)无关,只与排序后的行号相关,就是我们常规理解的那样。 range是逻辑窗口,与当前行的值有关(order by key的key的值),在key上操作range范围。 简要:如果当前行的值有重复的,range会默认把重复的值加

    2024年02月11日
    浏览(31)
  • Hive 窗口函数超详细教程

    在 SQL 开发中,有时我们可以使用聚合函数将多行数据按照规则聚集在一行,但是我们又想同时得到聚合前的数据,单纯的聚合函数是做不到的,怎么办呢?这时我们的窗口函数就闪亮登场了。窗口函数兼具分组和排序功能,又叫分析函数! 语法如下:

    2024年02月04日
    浏览(38)
  • hive窗口分析函数使用详解系列二之分组排序窗口函数

    我们讨论面试中各大厂的SQL算法面试题,往往核心考点就在于窗口函数,所以掌握好了窗口函数,面对SQL算法面试往往事半功倍。 已更新第一类聚合函数类,点击这里阅读 hive窗口函数聚合函数类 本节介绍Hive聚合函数中的第二类聚合函数:分组排序窗口函数。 这些函数的用

    2024年04月13日
    浏览(36)
  • Hive之窗口函数lag()/lead()

    lag()与lead函数是跟偏移量相关的两个分析函数 通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤,该操作可代替表的自联接,且效率更高 lag()/lead() lag(col,n,DEFAULT)用于统计窗口内往上第n行值  第

    2024年02月15日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包