遇到以下问题如何解决?
- 查询客户各个日期的历史累积购买金额
- 查询每个客户第一次(首购)或前N次购买记录
- 查询每个客户最后一次购买记录
- 某活动推广后,每天的累积数据
历史累积
hive 语法:
SELECT userid user_id, pay_datekey pay_datekey, pay_amount pay_amount, SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey ROWS BETWEENUNBOUNDED PRECEDING AND CURRENT ROW ) accumulate_pay_amount FROM order_table
窗口分析函数
OVER(PARTITION BY COLUMN ROWS BETWEEN ..)
ROWS ((CURRENT ROW) | (UNBOUNDED | [num]) PRECEDING) AND (UNBOUNDED | [num]) FOLLOWING
- PRECEDING:往前
- FOLLOWING:往后
- CURRENT ROW:当前行
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED
FOLLOWING:表示到后面的终点
如果不指定ROWS BETWEEN,默认为从起点到当前值
SUM(pay_amount) OVER (PARTITION BY userid ORDER BY pay_datekey ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING)
聚合函数
除了可以计算到当前行的sum聚合函数之外,还支持以下 标准聚合函数
- COUNT
- MIN
- MAX
- AVG
比如计算:截止某天历史平均购买交易额、最小购买金额等。
序列函数
为每一行加一个序号
- row_number()
- rank()
- dense_rank()
- percent_rank() 分组内当前行的RANK值-1/分组内总行数-1
- ntile(n) 将数据集分成 n片,返回分片号
- cume_dist 小于等于当前值的行数和分组内总行数的比值
效果
SELECT user_id, pay_datekey, pay_amount, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) row_number, RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) rank, DENSE_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) dense_rank, PERCENT_RANK() OVER (PARTITION BY userid ORDER BY pay_datekey ASC) percent_rank, NTILE(2) OVER (PARTITION BY userid ORDER BY pay_datekey ASC) ntile FROM order_detail
LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值
(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
LEAD
与LAG相反,LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值. 第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值
(当往下第n行为NULL时候,取默认值,如不指定,则为NULL
FIRST_VALUE
取分组内排序后,第一个值.应用场景,如求第一次购买日期。
LAST_VALUE
取分组内排序后,最后一个值.应用场景,如求第一次购买日期
相关推荐
大数据分析工具 hive 高级分析函数的使用与优化,笔记整理!
HIVE函数大全,包括目前HIVE可用的所有函数,里面有详细说明。HIVE是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。
02.hive内置函数--窗口分析函数--row_number_over.mp4
很详细的hive内置函数大全,适用于工作、学习;常做hive统计分析的你,值得拥有. 很详细的hive内置函数大全,适用于工作、学习;常做hive统计分析的你,值得拥有.
几乎所有hive函数一览无余,每个函数均有例子,帮助大家更好的利用hive分析数据
Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。...hive函数大全.doc Hive用户手册中文版.pdf Hive用户指南-V1.1.doc 喜欢的朋友可以下载学习。
03.hive窗口分析函数--应用场景--累积报表--用传统方法实现.mp4
hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的sql查询功能,可以将sql语句转换为MapReduce任务进行运行。 其优点是学习成本低,可以通过类SQL语句快速实现简单的...
Hive原理/Hive SQL/Hive 函数/数据仓库分层和建模/Hive sql优化/数据倾斜
Hive提供了丰富的内置函数,方便用户对数据进行处理和分析。本文将详细介绍Hive函数的应用,包括聚合函数、数学函数、集合函数、类型转换函数、日期函数、条件函数、字符串函数和表生成函数等。 聚合函数 聚合函数...
Hive提供了多种数据分析函数,包括SUM、MAX、MIN、AVG等。在该资源中,我们使用MAX函数来查询2020年中当日综合总票房最多的10天。 知识点7:Hive数据排序 Hive提供了多种数据排序方式,包括ASC、DESC等。在该资源...
"Hive语法和常用函数" Hive是基于Hadoop分布式系统上的数据仓库,最早是由Facebook公司开发的,Hive极大的推进了Hadoop ecosystem在数据仓库方面上的发展。Facebook的分析人员中很多工程师比较擅长SQL而不善于开发...
大数据分析环境安装与设置,大数据分析课程,hadoop原理,hive操作,函数,Hadoop原理与概念,HQL核心技能1--常用函数--表连接
over():指定分析函数工作的数据窗口的大小,这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面,只对聚合函数生效。 current row:当前行 n preceding:往前n行数据 n following:往后n行数据 unbounded:...
Apache Hive 是基于Hadoop的一个数据仓库工具,用来进行数据提取、转化、加载,这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表,并提供...
窗口函数是Hive中一种强大的分析工具,可以对数据进行复杂的分析和计算。窗口函数可以像聚合函数一样对一组数据进行分析并返回结果,但不同之处在于,窗口函数不是将一组数据汇总成单个结果,而是为每一行数据都返回...
窗⼝函数也称为OLAP函数,OLAP 是OnLine Analytical Processing 的简称,意思是对数据库数据进⾏实时分析处理。例如,市场分析、创建财务报表、创建计划等⽇常性商务⼯作。窗⼝函数就是为了实现OLAP ⽽添加的标准SQL...
Hive语法和常用函数 Hive是一个基于Hadoop分布式系统上的数据仓库,由Facebook公司开发的,Hive极大的推进了Hadoop ecosystem在数据仓库方面上的发展。Hive提供了数据仓库的部分功能,包括数据ETL(抽取、转换、...
Hive 的开发使用需要了解 Hive 的基本概念和语法,包括 HiveQL 语句、Hive 函数、Hive 访问控制等。HiveQL 语句可以实现数据的 CRUD 操作,Hive 函数可以用于数据的处理和转换,Hive 访问控制可以用于数据的安全管理...