【Hive】grouping sets() 函数

这篇具有很好参考价值的文章主要介绍了【Hive】grouping sets() 函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1. 多维函数说明

  1. 多维函数作用:是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的group by结果集进行 union all
  2. 与union all 效率对比:Grouping Sets 版本的 SQL 不仅在表达上更加简洁,在性能上也更加高效
  3. Hive中多维函数有哪些?
    1. grouping sets()
    2. with cube
    3. with rollup

2. grouping sets()

(1) 语法

假设现在有如下3个SQL:

-- 第一个sql:以a维度计算num
select a, sum(num) from t group by a

-- 第二个sql:以b维度计算num
select b, sum(num) from t group by b

-- 第三个sql:以a, b维度计算num
select a, b, sum(num) from t group by a, b

如何把这三个SQL写到一个SQL中?

  1. 方式一:用 union all

    select 
    	a,			 -- 只有a维度
    	null as b,   -- 没有b维度 
    	sum(num) as num
    from t group by a
    union all
    select 
    	null,     -- 没有a维度
    	b,        -- 只有b维度
    	sum(num) as num 
    from t group by b
    union all
    select 
    	a,   -- 有a维度
    	b,   -- 也有维度
    	sum(num) as num 
    from t group by a, b
    
  2. 方式二:用 grouping sets() 函数

    -- 将这两个合在一起的更简单的写法:使用 grouping sets
    select 
    	a,
    	b,
    	sum(num) as num 
    from t
    group by a, b
    grouping sets (a, b, (a, b))  -- sets后面的括号指名了要取出现哪些维度
    order by grouping__id   -- grouping__id表示结果属于哪一个分组集合,以二进制的形式表达
    
    1. 其他有用的文档:Hive/Presto中函数grouping sets用法详解(踩坑总结,看到赚到)
    2. 注意:grouping sets会把group by子句中没有参与聚合的维度列置null。也就是说在结果上完全等价于方式一。

(2) grouping__id字段

select a,b,c,grouping__id from test group by a,b,c
grouping sets (
	(a,b),
	(a, c),
	(a, b, c)
)
  1. 在Hive中grouping__id的数字规则:根据group by 字段 ,从低到高,group sets中出现的字段是1没出现是0
    因此,上述SQL中 (a, b)对应 011(a,c)对应 101
  2. Spark中grouping__id的数字规则:根据group by 字段 ,从高到低,group sets中出现的字段是0没出现是1
    因此,上述SQL中 (a, b)对应 001(a,c)对应 010

(3) 例子

  1. cookie_info.txt 文件:

    2018-03,2018-03-10,cookie1
    2018-03,2018-03-10,cookie5
    2018-03,2018-03-12,cookie7
    2018-04,2018-04-12,cookie3
    2018-04,2018-04-13,cookie2
    2018-04,2018-04-13,cookie4
    2018-04,2018-04-16,cookie4
    2018-03,2018-03-10,cookie2
    2018-03,2018-03-10,cookie3
    2018-04,2018-04-12,cookie5
    2018-04,2018-04-13,cookie6
    2018-04,2018-04-15,cookie3
    2018-04,2018-04-15,cookie2
    2018-04,2018-04-16,cookie1
    
  2. 导入数据

    CREATE TABLE cookie_info(
    	month STRING,
    	day STRING,
    	cookieid STRING
    ) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ',';
    
    load data local inpath '/root/hivedata/cookie_info.txt' into table cookie_info;
    
    select * from cookie_info;
    
  3. 分别统计每一天的cookie和每月的cookie并联合在一张表中

    SELECT
    	month,
    	day,
    	COUNT(DISTINCT cookieid) AS nums,
    	GROUPING__ID
    FROM cookie_info
    GROUP BY month,day
    GROUPING SETS (month,day)
    ORDER BY GROUPING__ID;
    

    结果:
    grouping sets函数,# Hive,hive,增强聚合函数,grouping sets

3. with cube

cube() 函数表示根据GROUP BY的维度的所有组合进行聚合。

对于cube来说,如果有n个维度,则所有组合的总个数是: 2 n 2^{n} 2n

比如cube有a,b,c 3个维度,则所有组合情况是: (a,b,c),(a,b),(a,c),(a),(b,c),(b),(c),()

SELECT
	month,
	day,
	COUNT(DISTINCT cookieid) AS nums,
	GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;


--等价于
select NULL, NULL, nums, GROUPING__ID from (SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info)
UNION ALL
select month, NULL, nums, GROUPING__ID  from (SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month)
UNION ALL
select NULL, day, nums, GROUPING__ID  from (SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day)
UNION ALL
select month, day, nums, GROUPING__ID from (SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;)

4. with rollup

rollup() 函数是cube的子集,以最左侧的维度为主。

比如ROLLUP有a,b,c3个维度,则所有组合情况是:(a,b,c),(a,b),(a),()文章来源地址https://www.toymoban.com/news/detail-642508.html

SELECT
	month,
	day,
	COUNT(DISTINCT cookieid) AS nums,
	GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;


-- 等价于
select month, day, nums, GROUPING__ID from (SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;)
UNION ALL
select month, NULL, nums, GROUPING__ID  from (SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month)
UNION ALL
select NULL, NULL, nums, GROUPING__ID from (SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info)

到了这里,关于【Hive】grouping sets() 函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Hive中的常用concat函数——concat函数、concat_ws函数和group_concat函数

    连接参数的函数,返回结果为连接参数的字符串。如果有一个参数为 NULL ,则返回的结果为 NULL 。 concat() 的一个特殊形式,表示 concat with separator ,两个参数之间加上特定的分隔符。返回的是用指定分隔符连接参数的字符串。如果分割符为 null ,则返回 null ,参数为 null ,则

    2024年02月02日
    浏览(42)
  • 【hive】列转行—collect_set()/collect_list()/concat_ws()函数的使用场景

    在 Hive 中想实现按某字段分组,对另外字段进行合并,可通过collect_list()或者collect_set()实现。 collect_set()函数与collect_list()函数: 列转行 专用函数,都是将分组中的某列转为一个数组返回。有时为了字段拼接效果,多和concat_ws()函数连用。 collect_set()与collect_list()的区别: co

    2024年02月09日
    浏览(44)
  • 聚合函数+group by

    常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()。 COUNT()函数:统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数。 COUNT(*)计算表中总的行数,不管某列是否有数值或者为空值。 COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行。 对于

    2024年02月02日
    浏览(39)
  • hive SQL: case when + group by 的用法

    假设有一个数据表,包含了不同人员的信息,其中包括姓名、性别、年龄等字段。现在需要统计不同年龄区间的人数,并按照年龄区间进行分组。可以使用如下 SQL 语句实现: ``` SELECT    CASE      WHEN age BETWEEN 0 AND 10 THEN \\\'0-10\\\'      WHEN age BETWEEN 11 AND 20 THEN \\\'11-20\\\'      WHEN a

    2024年02月08日
    浏览(77)
  • hive中Distinct和group by去重的对比

            在Hive中, DISTINCT和GROUP BY都可以用于去重,但是它们背后的实现方式是不同的,因此它们的效率也是不同的。         DISTINCT是一种去重方法,它会扫描整个数据集,然后将重复的记录删除,只留下唯一的记录 。由于DISTINCT需要扫描整个数据集,因此它在处理

    2024年02月04日
    浏览(55)
  • Hive之set参数大全-10

    hive.llap.task.scheduler.node.reenable.max.timeout.ms 是Apache Hive中的一个配置属性,用于指定LLAP(Low Latency Analytical Processing)引擎任务调度器中重新启用节点的最大超时时间。 以下是使用SQL语言设置此属性的示例: 将 desired_value 替换为您希望设置的最大超时时间,通常是一个正整数,

    2024年01月22日
    浏览(32)
  • Hive之set参数大全-8

    hive.llap.execution.mode 是Apache Hive中的一个配置属性,用于指定LLAP(Low Latency Analytical Processing)的执行模式。该属性用于决定Hive查询是否使用LLAP引擎执行。 以下是使用SQL语言设置此属性的示例: 将 desired_mode 替换为您希望设置的执行模式。常见的模式包括: auto : 自动模式,

    2024年01月18日
    浏览(34)
  • Hive之set参数大全-9

    hive.llap.io.threadpool.size 是Apache Hive中的一个配置属性,用于指定LLAP(Low Latency Analytical Processing)引擎中的IO(输入/输出)线程池的大小。 以下是使用SQL语言设置此属性的示例: 将 desired_size 替换为您希望设置的线程池大小,通常是一个正整数。 如果您想在Hive的配置文件(通

    2024年01月18日
    浏览(32)
  • Hive之set参数大全-4

    hive.fetch.output.serde 是 Hive 的一个配置参数,用于指定在使用 FETCH 命令提取查询结果时的序列化/反序列化器。 以下是一个示例: 在上述示例中,将 hive.fetch.output.serde 设置为 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe ,表示在使用 FETCH 命令提取查询结果时使用 LazySimpleSerDe 进行序

    2024年02月03日
    浏览(33)
  • Hive之set参数大全-1

    hive.allow.udf.load.on.demand 是 Apache Hive 中的一个配置属性,用于控制是否允许在需要时按需加载用户定义函数(UDF)。 在 Hive 中,UDFs是用户编写的自定义函数,可以在 Hive SQL 查询中使用。这个配置属性的目的是在查询执行期间动态加载UDFs,而不是在Hive服务器启动时就加载所有

    2024年02月03日
    浏览(34)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包