当前位置: 首页 > backend >正文

大数据学习(112)-HIVE中的窗口函数

🍋🍋大数据学习🍋🍋

🔥系列专栏: 👑哲学语录: 用力所能及,改变世界。
💖如果觉得博主的文章还不错的话,请点赞👍+收藏⭐️+留言📝支持一下博主哦🤞


在 Apache Hive 中,窗口函数(Window Functions)是一类强大的 SQL 函数,用于对查询结果集的一个“窗口”或“分组”进行计算,而不减少结果集的行数。以下是 Hive 中常用的窗口函数分类及示例:

1. 排名函数

函数描述
ROW_NUMBER()为结果集中的每一行分配一个唯一的行号,行号从 1 开始。
RANK()为结果集中的每一行分配一个排名,遇到相同值时跳过排名。
DENSE_RANK()为结果集中的每一行分配一个排名,遇到相同值时不跳过排名。
NTILE(n)将结果集划分为 n 个桶,并为每一行分配一个桶号。

示例

SELECT 
employee_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_department,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_in_department,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_number_in_department
FROM employees;

2. 聚合函数(作为窗口函数)

函数描述
SUM()计算窗口内数值的总和。
AVG()计算窗口内数值的平均值。
MIN()返回窗口内的最小值。
MAX()返回窗口内的最大值。
COUNT()计算窗口内的行数。
SELECT 
employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id) AS total_salary_in_department,
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_in_department
FROM employees;

3. 分析函数

函数描述
LAG(column, n, default)返回当前行之前第 n 行的值,如果超出范围则返回 default
LEAD(column, n, default)返回当前行之后第 n 行的值,如果超出范围则返回 default
FIRST_VALUE(column)返回窗口内第一行的值。
LAST_VALUE(column)返回窗口内最后一行的值(需结合 ROWS BETWEEN 子句使用)。
NTH_VALUE(column, n)返回窗口内第 n 行的值。
SELECT 
employee_id,
salary,
LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS first_salary_in_department
FROM employees;

4. 累积函数

函数描述
CUME_DIST()计算当前行在窗口内的累积分布(小于等于当前值的比例)。
PERCENT_RANK()计算当前行在窗口内的百分比排名。
SELECT 
employee_id,
salary,
CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist_in_department,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS percent_rank_in_department
FROM employees;

5. 窗口定义

窗口函数通常与 OVER 子句一起使用,OVER 子句定义了窗口的范围和排序方式。

OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ...]
[ROWS|RANGE BETWEEN ... AND ...]
)
  • PARTITION BY:将结果集划分为多个分区,窗口函数在每个分区内独立计算。
  • ORDER BY:定义窗口内的排序顺序。
  • ROWS|RANGE BETWEEN:定义窗口的边界(可选)。
SELECT 
employee_id,
salary,
SUM(salary) OVER (
PARTITION BY department_id 
ORDER BY hire_date 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_salary_in_department
FROM employees;

6. 窗口边界

  • UNBOUNDED PRECEDING:窗口从分区的第一行开始。
  • UNBOUNDED FOLLOWING:窗口到分区的最后一行结束。
  • CURRENT ROW:窗口仅包含当前行。
  • n PRECEDING:窗口包含当前行之前的 n 行。
  • n FOLLOWING:窗口包含当前行之后的 n 行。
SELECT 
employee_id,
salary,
AVG(salary) OVER (
PARTITION BY department_id 
ORDER BY hire_date 
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_salary_in_department
FROM employees;

总结

Hive 中的窗口函数为数据分析提供了强大的工具,可以用于排名、聚合、累积计算、前后值比较等操作。常见的窗口函数包括:

  • 排名函数ROW_NUMBER()RANK()DENSE_RANK()NTILE()
  • 聚合函数SUM()AVG()MIN()MAX()COUNT()
  • 分析函数LAG()LEAD()FIRST_VALUE()LAST_VALUE()NTH_VALUE()
  • 累积函数CUME_DIST()PERCENT_RANK()
http://www.xdnf.cn/news/1088.html

相关文章:

  • Cursor这类编程Agent软件的模型架构与工作流程
  • Flink介绍——实时计算核心论文之Dataflow论文详解
  • OpenCV物体计数示例
  • Go语言和Python 3的协程对比
  • Spark-Streaming
  • Linux kernel signal原理(下)- aarch64架构sigreturn流程
  • Spring Boot 主模块 spring-boot 核心技术解析:从启动类到内嵌容器的无缝支持
  • dify工作流之text-2-e-sql,大模型写sql并执行
  • 深入理解 Spring @Configuration 注解
  • SpringBoot原生实现分布式MapReduce计算
  • Java高频面试之并发编程-05
  • 新能源汽车可视化大屏系统毕业设计
  • Node.js 模块导入的基本流程
  • 【Harmony】常用工具类封装
  • Jupyter Notebook 中切换/使用 conda 虚拟环境的方式(解决jupyter notebook 环境默认在base下面的问题)
  • Python爬虫从入门到实战详细版教程Char01:爬虫基础与核心技术
  • 【网络】代理服务器收尾及高级IO
  • vue项目中axios统一或单独控制接口请求时间
  • WPS Office安卓版云文档同步速度与PDF转换体验测评
  • 音视频小白系统入门课-3
  • 媒体发稿攻略,解锁新闻发稿成长新高度
  • 双指针-11.盛水最多的容器-力扣(LeetCode)
  • vue作业-周报管理系统
  • python 执行顺序
  • 程序员学商务英文之Terms of Payment Packing
  • 日志文件太大,如何分卷压缩便于传输
  • pwm驱动呼吸灯
  • 【NCCL】transport建立(一)
  • Express项目实战大事件后台 API 项目(五)——文章管理
  • java集合框架day1————集合体系介绍