程序员经验分享-hwhale

Hive 窗口函数大全

2023-11-01

目录

窗口函数概述

窗口序列函数

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份，取第一份数据即可）

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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

大数据hive

hive

大数据

Hadoop

Hive 窗口函数大全 的相关文章

随机推荐

  • Gateway网关

    搭建网关服务 1 创建新的moddule 引入SpringCloudGateway的依赖和nacos的服务发现依赖

  • 寻找数组中第k大的数

    题目 有一个整数数组 请你根据快速排序的思路 找出数组中第K大的数 给定一个整数数组a 同时给定它的大小n和要找的K K在1到n之间 请返回第K大的数 保证答案存在 测试样例 1 3 5 2 2 5 3 返回 2 方法一 利用冒泡排序 进行

  • Java循环结构—多重循环及continue break（基础）

    目录 含义 语法格式 while循环 do while循环 for循环 执行规律 for循环案例 continue break return 含义 1 多重循环指一个循环语句的循环体中再包含循环语句 又称嵌套循环 2 循环语句内可以嵌套多层

  • minio在linux服务器部署过程

    1 先下载到服务器上 wget https dl min io server minio release linux amd64 minio 2 权限 chmod x minio 3 运行 minio server usr software

  • 【Qt】QTimer的简单使用

    定义定时器对象 QTimer myTimer 动态分部内存空间 myTimer new QTimer this 启动定时器 myTimer gt start 100 定时器超时事件 QTimer timeout 停止定时器 myTimer

  • 自学成才秘籍！机器学习&深度学习经典资料汇总

    自学成才秘籍 机器学习 深度学习经典资料汇总 Brief History of Machine Learning 介绍 这是一篇介绍机器学习历史的文章 介绍很全面 从感知机 神经网络 决策树 SVM Adaboost到随机森林 DeepLe

  • kali linux sudo命令注意

    sudo 的使用 举例 软件更新命令 在root用户下 直接 apt update 若在非root用户下 需要输入 sudo apt update

  • 「技术分享」腾讯疯狂扩招中，只有不断Up知识才有可能拿高薪

    今年的秋招基本已经进入大规模的开奖季了 很多小伙伴收获不错 拿到了心仪的offer 各大论坛和社区里也看见不少小伙伴慷慨地分享了常见的面试题和八股文 为此咱这里也统一做一次大整理和大归类 这也算是划重点了 俗话说得好 他山之石 可以攻玉 多

  • springmvc源码学习（二十）对响应数据进行压缩原理

    目录 前言 一 配置 二 原理 1 参数的注入 2 CompressionHttpHandlerFactory的创建 3 压缩的预言 总结 前言 为了节省带宽 在响应数据比较大的情况下 可以对响应数据进行压缩 返回给前端页面压缩数据 一 配

  • 获取浏览器窗口（window）的宽高

    以下三种方法能够确定浏览器窗口的尺寸 浏览器的视口 不包括工具栏和滚动条 1 对于Internet Explorer Chrome Firefox Opera Safari 浏览器窗口的内部高度 window innerHeight 浏览器

  • MATLAB中删除矩阵或向量中Nan数据

    将A中NaN值去掉 B A isnan A 参考博客

  • 微前端qiankun使用+踩坑

    背景 项目使用qiankun 改造的背景 项目A 项目B 项目C 项目A和项目B具有清晰的服务边界 从服务类型的角度能够分为两个项目 在公司项目一体化的背景下 所有的项目又应该是一个项目 项目B研发启动的时候 1 由于开发时间紧张 2 项目

  • Android项目工程结构介绍

    Android项目工程结构介绍 1 gradle和 idea Android Studio自动生成的文件 打包的时候一般会删掉再进行打包 2 app 项目的代码资源都在其中 也是我们工作的核心目录 build 编译生成文件 生成的apk就在

  • Scroller与computeScroll处理滑动

    背景 最近在纯手写一个 slidingMenu 里面用到了 Scroller与computeScroll处理滑动 由于我也是第一次遇到这种东西 我这暴脾气 实在忍不住要记住一下 以供大家参考 更重要的是方便自己以后回忆 知识点讲解 实现滚动

  • 01_08_桶排序（Bucket Sort）

    桶排序 Bucket Sort 桶排序 Bucket Sort 介绍 是一种排序算法 适用于数据范围较小且分布均匀的浮点数数据 它将待排序序列划分为若干个桶 区间 对每个桶中的元素进行排序 然后按顺序合并所有桶的元素得到最终有序序列 桶排序

  • RFID 复杂事件检测算法-毕业论文

    摘 要 本论文首先介绍了RFID技术的概念 工作原理 发展过程 应用背景等信息 然后对本系统所需的硬件条件 即RFID阅读器的特性和配置等信息进行说明 接下来介绍了基于RFID的仓储管理系统的开发背景 探讨了数据库的功能特点 做出了系统需求

  • shell grep 详解说明，实战造就英雄，苦练成就神话

    shell grep 详解说明 当您使用Shell中的grep命令时 它允许您在文本文件或标准输入中搜索匹配某个模式的行 并输出结果 下面是grep命令的详细说明和参数介绍表格 参数 描述 i 忽略大小写进行匹配 默认情况下 grep区分大

  • 制作cmd小游戏_小伙利用Python自制一个推箱子小游戏！

    导语 月初更波python制作小游戏系列吧用python写了个推箱子小游戏 在这里分享给大家 让我们愉快地开始吧 小伙利用Python自制一个推箱子小游戏 开发工具 Python版本 3 6 4 相关模块 pygame模块 以及一些Pyth

  • [转]QT中窗口刷新事件的学习总结

    QT中窗口刷新事件的学习总结 一 主要理解一下几个方法和属性 1 QWidget QScrollView viewport const 2 void QWidget paintEvent QPaintEvent 虚 保护 3 void QW

  • Hive 窗口函数大全

    目录 窗口函数概述 窗口序列函数 row number dense rank 窗口边界 滑动窗口 lag 获取上一行数据 lead 获取下一行数据 窗口专用计算函数 sum累加函数 max最大值 min最小值 avg平均值 count累计次

热门标签