Hive-Sql分析函数汇总

已于 2023-03-04 23:07:48 修改
阅读量3.1k 收藏 34
点赞数 5
分类专栏： sql hive HiveSql面试题 文章标签： hive sql
于 2020-05-28 16:41:31 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/godlovedaniel/article/details/106408169
版权
sql 同时被 3 个专栏收录
111 篇文章 46 订阅
订阅专栏
HiveSql面试题
98 篇文章 119 订阅
订阅专栏
hive
40 篇文章 15 订阅
订阅专栏

目录

  0 引 言

  1. 聚合分析函数

  2.排名分析函数

  3.数学分析函数

  4.行偏移量分析函数

  5.多维分析函数

 0 引 言

   分析函数在sql中非常重要，对于sqlboy来说是必须要掌握的 ，本文对Hive-Sql分析函数进行汇总和总结，Hive-Sql分析函数主要有以下五类：

  • 聚合分析函数
  • 排名分析函数
  • 数学分析函数
  • 行偏移量分析函数
  • 多维分析函数

其思维导图如下所示：

 1. 聚合分析函数

  •         sum 求和
  •        count 统计记录数
  •        min 求最小值
  •        max 求最大值
  •        avg 求均值

具体案例参考链接： Hive-SQL工作中常用函数总结及案例实战_莫叫石榴姐的博客-CSDN博客

  2.排名分析函数

  •        ROW_NUMBER  正常排序（行号）[1,2,3,4] -- 必须有order_by。适合于生成主键，连续序列或者不并列排名
  •        RANK  跳跃排序[1,2,2,4] -- 必须有order_by
  •       DENSE_RANK 密集排序/等位排序[1,2,2,3] -- 必须有order_by
  •       FIRST 从DENSE_RANK返回的集合中取出排在最前面的一个值的行
  •       LAST 从DENSE_RANK返回的集合中取出排在最后面的一个值的行
  •       FIRST_VALUE 返回窗口中第一行某字段值，取分组内排序后，截止到当前行，第一行某字段值
  •      LAST_VALUE 返回窗口中的最后一行某字段值，取分组内排序后，截止到当前行，最后一行某字段值

具体案例参考链接： Hive-SQL工作中常用函数总结及案例实战_莫叫石榴姐的博客-CSDN博客

  3.数学分析函数 

  •       NTILE 用于将分组数据按照顺序切分成n片（n桶），返回当前切片值（桶序号）.切片值就是桶的序号。类似于hive中分桶，用于求百分比
对于一组数字（1，2，3，4，5，6），ntile(2)切片后为（1，1，1，2，2，2）

（1，2，3，4，5，6，7），ntile(2)切片后为（1，1，1，1，2，2，2）

其序号的标定类似于hive中分桶的原理。
又叫分桶函数或分片函数。ntile(n),n表示分桶或分片的个数。
  •      COVAR_POP 返回一对表达式的总体协方差
  •      COVAR_SAMP 返回一对表达式的样本协方差
  •      CUME_DIST 计算一行在组中的相对位置。小于等于当前值的行数/分组内总行数
  •      PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
  •      PERCENTILE_DISC  返回一个与输入的分布百分比值相对应的数据值

    举例如下：

数据准备：

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
 
CREATE EXTERNAL TABLE lxw1234 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile location '/tmp/lxw11/';
 
 
hive> select * from lxw1234;
OK
d1      user1   1000
d1      user2   2000
d1      user3   3000
d2      user4   4000
d2      user5   5000
CUME_DIST
–CUME_DIST 小于等于当前值的行数/分组内总行数
 –比如，统计小于等于当前薪水的人数，所占总人数的比例
SELECT 
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM lxw1234;
 
dept    userid   sal   rn1       rn2 
-------------------------------------------
d1      user1   1000    0.2     0.3333333333333333
d1      user2   2000    0.4     0.6666666666666666
d1      user3   3000    0.6     1.0
d2      user4   4000    0.8     0.5
d2      user5   5000    1.0     1.0
 
rn1: 没有partition,所有数据均为1组，总行数为5，
     第一行：小于等于1000的行数为1，因此，1/5=0.2
     第三行：小于等于3000的行数为3，因此，3/5=0.6
rn2: 按照部门分组，dpet=d1的行数为3,
     第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

SELECT 
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内
RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值
SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM lxw1234;
 
dept    userid   sal    rn1    rn11     rn12    rn2
---------------------------------------------------
d1      user1   1000    0.0     1       5       0.0
d1      user2   2000    0.25    2       5       0.5
d1      user3   3000    0.5     3       5       1.0
d2      user4   4000    0.75    4       5       0.0
d2      user5   5000    1.0     5       5       1.0
 
rn1: rn1 = (rn11-1) / (rn12-1) 
	   第一行,(1-1)/(5-1)=0/4=0
	   第二行,(2-1)/(5-1)=1/4=0.25
	   第四行,(4-1)/(5-1)=3/4=0.75
rn2: 按照dept分组，
     dept=d1的总行数为3
     第一行，(1-1)/(3-1)=0
     第三行，(3-1)/(3-1)=1

4.不同行字段值比较分析函数（行偏移量分析函数）

  • LAG 可以访问结果集中的其它行而不用进行自连接。可以访问当前行之前的行，前偏移量函数，如果没有指定，偏移量默认为1
  • LEAD LEAD与LAG相反，LEAD可以访问当前行之后的行，后偏移量函数，用法同LAG函数

    *注：以上两个函数功能非常强大，使用较广 ，可以跨行取任意一个字段的值进行分析比较，采用上述函数避免了自连接操作，提高了效率。面试中也经常会被考察到，如求连续三天登录的用户等问题。

5.多维分析函数

  • GROUPING SETS 在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL
  • GROUPING__ID 表示结果属于哪一个分组集合
  • CUBE 根据GROUP BY的维度的所有组合进行聚合
  • ROLLUP 是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。
数据准备：

2015-03,2015-03-10,cookie1
2015-03,2015-03-10,cookie5
2015-03,2015-03-12,cookie7
2015-04,2015-04-12,cookie3
2015-04,2015-04-13,cookie2
2015-04,2015-04-13,cookie4
2015-04,2015-04-16,cookie4
2015-03,2015-03-10,cookie2
2015-03,2015-03-10,cookie3
2015-04,2015-04-12,cookie5
2015-04,2015-04-13,cookie6
2015-04,2015-04-15,cookie3
2015-04,2015-04-15,cookie2
2015-04,2015-04-16,cookie1
 
CREATE EXTERNAL TABLE lxw1234 (
month STRING,
day STRING, 
cookieid STRING 
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
stored as textfile location '/tmp/lxw11/';
 
 
hive> select * from lxw1234;
OK
2015-03 2015-03-10      cookie1
2015-03 2015-03-10      cookie5
2015-03 2015-03-12      cookie7
2015-04 2015-04-12      cookie3
2015-04 2015-04-13      cookie2
2015-04 2015-04-13      cookie4
2015-04 2015-04-16      cookie4
2015-03 2015-03-10      cookie2
2015-03 2015-03-10      cookie3
2015-04 2015-04-12      cookie5
2015-04 2015-04-13      cookie6
2015-04 2015-04-15      cookie3
2015-04 2015-04-15      cookie2
2015-04 2015-04-16      cookie1
GROUPING SETS
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM lxw1234 
GROUP BY month,day 
GROUPING SETS (month,day) 
ORDER BY GROUPING__ID;
 
month      day            uv      GROUPING__ID
------------------------------------------------
2015-03    NULL            5       1
2015-04    NULL            6       1
NULL       2015-03-10      4       2
NULL       2015-03-12      1       2
NULL       2015-04-12      2       2
NULL       2015-04-13      3       2
NULL       2015-04-15      2       2
NULL       2015-04-16      2       2
 
 
等价于 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
再如：

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM lxw1234 
GROUP BY month,day 
GROUPING SETS (month,day,(month,day)) 
ORDER BY GROUPING__ID;
 
month         day             uv      GROUPING__ID
------------------------------------------------
2015-03       NULL            5       1
2015-04       NULL            6       1
NULL          2015-03-10      4       2
NULL          2015-03-12      1       2
NULL          2015-04-12      2       2
NULL          2015-04-13      3       2
NULL          2015-04-15      2       2
NULL          2015-04-16      2       2
2015-03       2015-03-10      4       3
2015-03       2015-03-12      1       3
2015-04       2015-04-12      2       3
2015-04       2015-04-13      3       3
2015-04       2015-04-15      2       3
2015-04       2015-04-16      2       3
 
 
等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day
其中的 GROUPING__ID，表示结果属于哪一个分组集合。

CUBE
根据GROUP BY的维度的所有组合进行聚合。

SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM lxw1234 
GROUP BY month,day 
WITH CUBE 
ORDER BY GROUPING__ID;
 
 
month  			    day             uv     GROUPING__ID
--------------------------------------------
NULL            NULL            7       0
2015-03         NULL            5       1
2015-04         NULL            6       1
NULL            2015-04-12      2       2
NULL            2015-04-13      3       2
NULL            2015-04-15      2       2
NULL            2015-04-16      2       2
NULL            2015-03-10      4       2
NULL            2015-03-12      1       2
2015-03         2015-03-10      4       3
2015-03         2015-03-12      1       3
2015-04         2015-04-16      2       3
2015-04         2015-04-12      2       3
2015-04         2015-04-13      3       3
2015-04         2015-04-15      2       3
 
 
 
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234
UNION ALL 
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month 
UNION ALL 
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合：
SELECT 
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM lxw1234 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;
 
month  			    day             uv     GROUPING__ID
---------------------------------------------------
NULL             NULL            7       0
2015-03          NULL            5       1
2015-04          NULL            6       1
2015-03          2015-03-10      4       3
2015-03          2015-03-12      1       3
2015-04          2015-04-12      2       3
2015-04          2015-04-13      3       3
2015-04          2015-04-15      2       3
2015-04          2015-04-16      2       3
 
可以实现这样的上钻过程：
月天的UV->月的UV->总UV
--把month和day调换顺序，则以day维度进行层级聚合：
 
SELECT 
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM lxw1234 
GROUP BY day,month 
WITH ROLLUP 
ORDER BY GROUPING__ID;
 
 
day  			      month              uv     GROUPING__ID
-------------------------------------------------------
NULL            NULL               7       0
2015-04-13      NULL               3       1
2015-03-12      NULL               1       1
2015-04-15      NULL               2       1
2015-03-10      NULL               4       1
2015-04-16      NULL               2       1
2015-04-12      NULL               2       1
2015-04-12      2015-04            2       3
2015-03-10      2015-03            4       3
2015-03-12      2015-03            1       3
2015-04-13      2015-04            3       3
2015-04-15      2015-04            2       3
2015-04-16      2015-04            2       3
 
可以实现这样的上钻过程：
天月的UV->天的UV->总UV
（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）
这种函数，需要结合实际场景和数据去使用和研究，只看说明的话，很难理解。

参考链接

Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP – lxw的大数据田地

hive窗口函数必备宝典_小二货007的博客-CSDN博客_hive窗口函数

莫叫石榴姐
关注
  • 5
    点赞
  • 踩
  • 34
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive窗口分析函数（案例详细讲解）
lightupworld的博客
09-10 3162
Hive窗口分析函数一、语法结构二、窗口函数三、Over从句四、分析函数五、COUNT、SUM、MIN、MAX、AVG详解1.数据准备参考资料 一、语法结构 分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置) over()函数中包括三个函数：包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。 我们在使用over()窗口函数时，over()函数中
Hive Sql中均值、方差、标准差、皮尔逊相关系数、偏度、峰度统计字段
别说话写代码的博客
08-14 2万+
SELECT mean(age) AS '均值', variance(age) AS '方差', stddev(age) AS '标准差', corr(age,yearsmarried) AS '两个指标的相关系数', skewness(age) AS 'skewness偏度', kurtosis(age) AS 'kurtosis峰度' FROM table
SQL数据分析常用函数
最新发布
zwldn的博客
05-20 1153
本文汇总了数据分析中处理和操作数据常用的SQL 函数，并提供了相关示例。
hive -- stddev , stddev_pop , stddev_samp , var_pop , var_samp（计算方差标准差等）
Yu的博客
05-16 1万+
原文链接 标准差是方差的平方根 1.方差公式： m为x1,x2…xn数列的期望值（平均数） s^2 = [(x1-m)^2 + (x2-m)^2 + … (xn-m)^2]/n s即为标准差 s^2为方差。 2.实例： Hive> select * from dim_row_num limit 10; OK 0 1 2 3 4 5 6 7 8 9hive> se
hive中的多维函数:
qq_50166024的博客
12-29 221
1.grouping sets() 2.rollup 3.cube import org.apache.spark.sql.{DataFrame, SparkSession} object Sss { def main(args: Array[String]): Unit = { val session: SparkSession = SparkSession.builder().appName(“a”).master(“local[*]”).getOrCreate() val list=List.
HiveSql常用函数总结
qq_41495340的博客
07-30 1159
COALESCE()函数 主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下： COALESCE ( expression,value1,value2……,valuen) COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。 COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。 如果expression不为空值则返回expression；否则判断value1是否是空值， 如果va
apache-hive-1.2.1-bin.tar.gz
12-29
Hive 提供了数据汇总、离线分析以及结构化数据的查询功能，非常适合大数据处理场景。 "apache-hive-1.2.1-bin.tar.gz" 是 Apache Hive 1.2.1 版本的二进制发行包，采用 tar.gz 压缩格式。这个版本的 Hive 在当时是...
HIVE函数汇总-开窗函数篇
04-02
【Hive 开窗函数详解】 开窗函数是 Hive 数据处理中的一个重要工具，它允许用户在不使用 Group By...了解和熟练掌握 Hive 的开窗函数，能够帮助用户在大数据处理中实现复杂的数据分析任务，提高数据处理效率和准确性。
apache-hive-1.1.0-bin.tar.gz
08-20
Hive 提供了一个数据层抽象，将结构化的数据文件映射为一张数据库表，并提供简单的 SQL 查询功能，方便数据汇总、分析。`apache-hive-1.1.0-bin.tar.gz` 是 Apache Hive 1.1.0 版本的二进制发行包，包含了运行 Hive ...
hive-0.13.0-bin.tar.gz
07-29
Apache Hive 是一个基于 Hadoop 的数据仓库工具，它允许用SQL（HQL，Hive Query Language）语句进行数据查询、分析和汇总，极大地简化了大数据处理的复杂性。在Hive 0.13.0 版本中，它提供了更多增强的功能和性能...
hive-笔记.docx
05-15
Hive 是一个基于 Hadoop 的数据仓库工具，它为海量数据集提供了便捷的数据汇总、分析和查询能力。Hive 的设计初衷是解决直接使用 Hadoop MapReduce 进行复杂查询时面临的困难，如学习成本高、开发周期短和实现复杂...
hive 分析函数及用法
11-13 96
hive 分析函数及用法 hive 常用于olap(On-Line Analytical Processin 在线分析处理)领域,如下,分析函数不同于聚合函数,聚合函数会返回一个值但是分析函数会返回一个数据集,通过配合可以对数据集进行分析 分区排序 动态group by 求...
Hive的分析函数操作
tianqinglei的博客
08-22 335
分析函数 -------------- 0.11之后支持的，扫描多个输入的行计算每行的结果。通常和OVER, PARTITION BY, ORDER BY,windowing 配合使用。和传统分组结果不一样，传统结果没组中只有一个结果(max)。 分析函数的结果会出现多次，和每条记录都连接输出。 Function (arg1,..., argn) OVER ([PARTITION
Hive分析函数一
Xlucas的博客
10-12 986
lag lead first_value last_value版本0.13.1准备的数据 create table emp_function ( deptno string, sal int, hiredate string) row format delimited fields terminated by ‘|’ 10|1300|1982-01-23 00:
Hive分析函数和窗口函数
yanzhelee
08-23 1530
Hive分析函数和窗口函数在Hive 0.11之后支持的，扫描多个输入的行计算每行的结果。通常和OVER,PARTITION BY, ORDER BY, WINDOWING配合使用。和传统的分组结果不一样，传统的结果每组中只有一个结果。分析函数的结果会出现多次，和每条记录都连接输出。
HIVE 窗口及分析函数 应用场景
helloxiaozhe的博客
02-28 8269
窗口函数应用场景： （1）用于分区排序 （2）动态Group By （3）Top N （4）累计计算 （5）层次查询 hive中一般取top n时，row_number(),rank,dense_rank()这三个函数就派上用场了， 先简单说下这三函数都是排名的，不过呢还有点细微的区别。 通过代码运行结果一看就明白了。 ROW_NUMBER() OVER函数的基本用法 语法：R...
Hive的窗口函数
dacoolbaby的专栏
10-18 334
Window Function又称做窗口函数，分析函数。在Oracle里面就有这样的分析函数，用于数据统计分析之用。 在Hive里面，数据分析也提出相似的需求。 使用方法如下：select t.*, row_number() over(partition by area sort by updated_date ) as rnofrom table_name t over()表示当前查询的...
Hive聚合函数及采样函数详解
热门推荐
yhao2014的专栏
06-03 2万+
本文主要使用实例对Hive内建的一些聚合函数、分析函数以及采样函数进行比较详细的讲解。
hive支持sql大全（7-8：字符串函数 /集合统计函数）
Octemull的博客
07-27 3633
七、字符串函数1. 字符串长度函数：length 语法: length(string A) 返回值: int 说明：返回字符串A的长度 举例： hive> select length('abcedfg') from lxw_dual; 7 2. 字符串反转函数：reverse 语法: reverse(string A) 返回值: string
大数据学习：Hive函数.pdf
12-07
1. 聚合函数 - 聚合函数是Hive的核心组成部分，它们用于汇总数据。例如： - COUNT()：返回指定列或所有行的非空值数量，以及DISTINCT列的唯一值数量。 - SUM()：计算指定列的总和，区分或不区分重复值。 - AVG()...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值