【hive 函数】Hive分析函数和窗口函数

2024-09-07 06:32
文章标签 分析 函数 窗口 hive

本文主要是介绍【hive 函数】Hive分析函数和窗口函数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

拿一个例子来说
数据集：

cookie1,2015-04-10 10:00:02,url2  
cookie1,2015-04-10 10:00:00,url1  
cookie1,2015-04-10 10:03:04,1url3  
cookie1,2015-04-10 10:50:05,url6  
cookie1,2015-04-10 11:00:00,url7  
cookie1,2015-04-10 10:10:00,url4  
cookie1,2015-04-10 10:50:01,url5  
cookie2,2015-04-10 10:00:02,url22  
cookie2,2015-04-10 10:00:00,url11  
cookie2,2015-04-10 10:03:04,1url33  
cookie2,2015-04-10 10:50:05,url66  
cookie2,2015-04-10 11:00:00,url77  
cookie2,2015-04-10 10:10:00,url44  
cookie2,2015-04-10 10:50:01,url55

窗口函数

  1. FIRST_VALUE 取出分组内排序后，截止到当前行，第一个值
  2. LAST_VALUE 取出分组内排序后，截止到当前行，最后一个值
  3. LEAD(col, n,DEFAULT) 用于统计窗口内往下第n行的值。第一个参数为列名，第二个参数为往下第n行(可选，默认为1)，第三个参数为默认值(当往下第n行为NULL时，取默认值)
  4. LAG(col,n,DEFAULT) 与lead相反，用于统计窗口内往下第n个值。第一个参数为列名，第二个参数为往上第n行(可选，默认为1)

FIRST_VALUE
取分组内排序后，截止到当前行，第一个值

SELECT cookieid,  
createtime,  
url,  
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1  
FROM lxw1234;  cookieid  createtime            url     rn      first1  
---------------------------------------------------------  
cookie1 2015-04-10 10:00:00     url1    1       url1  
cookie1 2015-04-10 10:00:02     url2    2       url1  
cookie1 2015-04-10 10:03:04     1url3   3       url1  
cookie1 2015-04-10 10:10:00     url4    4       url1  
cookie1 2015-04-10 10:50:01     url5    5       url1  
cookie1 2015-04-10 10:50:05     url6    6       url1  
cookie1 2015-04-10 11:00:00     url7    7       url1  
cookie2 2015-04-10 10:00:00     url11   1       url11  
cookie2 2015-04-10 10:00:02     url22   2       url11  
cookie2 2015-04-10 10:03:04     1url33  3       url11  
cookie2 2015-04-10 10:10:00     url44   4       url11  
cookie2 2015-04-10 10:50:01     url55   5       url11  
cookie2 2015-04-10 10:50:05     url66   6       url11  
cookie2 2015-04-10 11:00:00     url77   7       url11

LAST_VALUE

取分组内排序后，截止到当前行，最后一个值

SELECT cookieid,  
createtime,  
url,  
LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2    
FROM lxw1234;  cookieid  createtime            url     last2  
----------------------------------------------  
cookie1 2015-04-10 10:00:02     url2    url5  
cookie1 2015-04-10 10:00:00     url1    url5  
cookie1 2015-04-10 10:03:04     1url3   url5  
cookie1 2015-04-10 10:50:05     url6    url5  
cookie1 2015-04-10 11:00:00     url7    url5  
cookie1 2015-04-10 10:10:00     url4    url5  
cookie1 2015-04-10 10:50:01     url5    url5  
cookie2 2015-04-10 10:00:02     url22   url55  
cookie2 2015-04-10 10:00:00     url11   url55  
cookie2 2015-04-10 10:03:04     1url33  url55  
cookie2 2015-04-10 10:50:05     url66   url55  
cookie2 2015-04-10 11:00:00     url77   url55  
cookie2 2015-04-10 10:10:00     url44   url55  
cookie2 2015-04-10 10:50:01     url55   url55

分析函数

  1. ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列，比如，按照pv降序排列，生成分组内每天的pv名次，ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录，获取一个session中的第一条refer等
  2. RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
  3. DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
  4. CUME_DIST() 小于等于当前值的行数除以分组内总行数。比如，统计小于等于当前薪水的人数所占总人数的比例
  5. PERCENT_RANK() 分组内当前行的RANK值-1/分组内 总行数-1
  6. NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN,比如NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PERCEDING AND CURRENT ROW)

累计操作：sum

## 创建数据表
create table orders(user_id string,device_id string,user_type string,price float,sales int);## 添加数据orders.txt
zhangsa test1   new     67.1    2
lisi    test2   old     43.32   1
wanger  test3   new     88.88   3
liliu   test4   new     66.0    1
tom     test5   new     54.32   1
tomas   test6   old     77.77   2
tomson  test7   old     88.44   3
tom1    test8   new     56.55   6
tom2    test9   new     88.88   5
tom3    test10  new     66.66   5## 开窗函数案例
selectuser_id,user_type,sales,-- 默认从起点到当前所有重复行sum(sales) over(partition by user_type order by sales asc) as sales_1,-- 从起点到当前所有重复行与sales_1结果相同sum(sales) over(partition by user_type order by sales asc range between unbounded preceding and current row) as sales_2,-- 从起点到当前行，结果与sale_1结果不同sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) as sales_3,-- 当前行加上往前3行sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) as sales_4,-- 当前范围往上加3行sum(sales) over(partition by user_type order by sales asc range between 3 preceding and current row) as sales_5,-- 当前行+往前3行+往后1行sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) as sales_6,--sum(sales) over(partition by user_type order by sales asc range between 3 preceding and 1 following) as sales_7,-- 当前行+之后所有行sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) as sales_8,--sum(sales) over(partition by user_type order by sales asc range between current row and unbounded following) as sales_9,-- 分组内所有行sum(sales) over(partition by user_type) as sales_10
fromorders
order byuser_type,sales,user_id;##上述查询结果如下：| user_id  | user_type  | sales  | sales_1  | sales_2  | sales_3  | sales_4  | sales_5  | sales_6  | sales_7  | sales_8  | sales_9  | sales_10  |
|----------|------------|--------|----------|----------|----------|----------|----------|----------|----------|----------|----------|-----------|
| liliu    | new        | 1      | 2        | 2        | 2        | 2        | 2        | 4        | 4        | 22       | 23       | 23        |
| tom      | new        | 1      | 2        | 2        | 1        | 1        | 2        | 2        | 4        | 23       | 23       | 23        |
| zhangsa  | new        | 2      | 4        | 4        | 4        | 4        | 4        | 7        | 7        | 21       | 21       | 23        |
| wanger   | new        | 3      | 7        | 7        | 7        | 7        | 7        | 12       | 7        | 19       | 19       | 23        |
| tom2     | new        | 5      | 17       | 17       | 17       | 15       | 15       | 21       | 21       | 11       | 16       | 23        |
| tom3     | new        | 5      | 17       | 17       | 12       | 11       | 15       | 16       | 21       | 16       | 16       | 23        |
| tom1     | new        | 6      | 23       | 23       | 23       | 19       | 19       | 19       | 19       | 6        | 6        | 23        |
| lisi     | old        | 1      | 1        | 1        | 1        | 1        | 1        | 3        | 3        | 6        | 6        | 6         |
| tomas    | old        | 2      | 3        | 3        | 3        | 3        | 3        | 6        | 6        | 5        | 5        | 6         |
| tomson   | old        | 3      | 6        | 6        | 6        | 6        | 6        | 6        | 6        | 3        | 3        | 6         |
注意

RANK、ROW_NUMBER、DENSE_RANK

select
user_id,user_type,sales,
RANK() over (partition by user_type order by sales desc) as r,
ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
orders;##上述查询结果如下| user_id | user_type | sales |  r  | rn  | dr  |
| ------- | --------- | ----- | --- | --- | --- |
| tom1    | new       | 6     | 1   | 1   | 1   |
| tom3    | new       | 5     | 2   | 2   | 2   |
| tom2    | new       | 5     | 2   | 3   | 2   |
| wanger  | new       | 3     | 4   | 4   | 3   |
| zhangsa | new       | 2     | 5   | 5   | 4   |
| tom     | new       | 1     | 6   | 6   | 5   |
| liliu   | new       | 1     | 6   | 7   | 5   |
| tomson  | old       | 3     | 1   | 1   | 1   |
| tomas   | old       | 2     | 2   | 2   | 2   |
| lisi    | old       | 1     | 3   | 3   | 3   |

这篇关于【hive 函数】Hive分析函数和窗口函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1144318。

相关文章

MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Python内置函数之classmethod函数使用详解

Python内置函数之classmethod函数使用详解

《Python内置函数之classmethod函数使用详解》：本文主要介绍Python内置函数之classmethod函数使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 类方法定义与基本语法2. 类方法 vs 实例方法 vs 静态方法3. 核心特性与用法(1编程客
阅读更多...
Python函数作用域示例详解

Python函数作用域示例详解

《Python函数作用域示例详解》本文介绍了Python中的LEGB作用域规则,详细解析了变量查找的四个层级,通过具体代码示例,展示了各层级的变量访问规则和特性,对python函数作用域相关知识感兴趣... 目录一、LEGB 规则二、作用域实例2.1 局部作用域（Local）2.2 闭包作用域（Enclos
阅读更多...
怎样通过分析GC日志来定位Java进程的内存问题

怎样通过分析GC日志来定位Java进程的内存问题

《怎样通过分析GC日志来定位Java进程的内存问题》：本文主要介绍怎样通过分析GC日志来定位Java进程的内存问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、GC 日志基础配置1. 启用详细 GC 日志2. 不同收集器的日志格式二、关键指标与分析维度1.
阅读更多...
MySQL count()聚合函数详解

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式？总结深入剖析一下 mysql 中的 COUNT
阅读更多...
MySQL 中 ROW_NUMBER() 函数最佳实践

MySQL 中 ROW_NUMBER() 函数最佳实践

《MySQL中ROW_NUMBER()函数最佳实践》MySQL中ROW_NUMBER()函数,作为窗口函数为每行分配唯一连续序号,区别于RANK()和DENSE_RANK(),特别适合分页、去重... 目录mysql 中 ROW_NUMBER() 函数详解一、基础语法二、核心特点三、典型应用场景1. 数据分
阅读更多...
MySQL中的表连接原理分析

MySQL中的表连接原理分析

《MySQL中的表连接原理分析》：本文主要介绍MySQL中的表连接原理分析,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、环境3、表连接原理【1】驱动表和被驱动表【2】内连接【3】外连接【4编程】嵌套循环连接【5】join buffer4、总结1、背景
阅读更多...
MySQL数据库的内嵌函数和联合查询实例代码

MySQL数据库的内嵌函数和联合查询实例代码

《MySQL数据库的内嵌函数和联合查询实例代码》联合查询是一种将多个查询结果组合在一起的方法,通常使用UNION、UNIONALL、INTERSECT和EXCEPT关键字,下面：本文主要介绍MyS... 目录一.数据库的内嵌函数1.1聚合函数COUNT([DISTINCT] expr)SUM([DISTIN
阅读更多...
Python get()函数用法案例详解

Python get()函数用法案例详解

《Pythonget()函数用法案例详解》在Python中,get()是字典（dict）类型的内置方法,用于安全地获取字典中指定键对应的值,它的核心作用是避免因访问不存在的键而引发KeyError错... 目录简介基本语法一、用法二、案例：安全访问未知键三、案例：配置参数默认值简介python是一种高级编
阅读更多...
python 常见数学公式函数使用详解(最新推荐)

python 常见数学公式函数使用详解(最新推荐)

《python常见数学公式函数使用详解(最新推荐)》文章介绍了Python的数学计算工具,涵盖内置函数、math/cmath标准库及numpy/scipy/sympy第三方库,支持从基础算术到复杂数... 目录python 数学公式与函数大全1. 基本数学运算1.1 算术运算1.2 分数与小数2. 数学函数
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2