Hive之grouping sets用法详解

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

关键字:

GROUPING SETS: 根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL

GROUPING__ID:表示结果属于哪一个分组集合,属于虚字段

简单示例:

关于grouping sets的使用,通俗的说,grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

create table temp.score_grouping as
select
  grouping__id, ---grouping__id是两个下划线
    class,
    sex,
    course,
   avg(score)
from
    tableName
group by  --group by包含所需所有维度字段
    class,
    sex,
    course -------此处无逗号
grouping sets
(
   (class,  course),
   ( class,sex),
   (sex,course),
   (course)
)
select *
from temp.score_grouping
where grouping__id in ('2','6')

说明:
1、select子句中的GROUPING__ID是两个下划线;
2、group by后面放的字段表示要分组聚合的全部字段;
3、grouping sets前没有逗号;
4、grouping_id的计算:
它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据,若组合中出现即为1,反正则为0,group by 后字段先出现的放在最低位,依次排开:
比如 group by class,sex,course,则二进制的顺序为:course sex class ,grouping sets字段出现则为1,反之则为0,
比如(class, course), 二进制为 101,十进制则为5,则grouping__id为5,同理grouping__id为6,则组合为(sex,course),二进制为110;

实例一:

-- 正确语句
select province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,GROUPING__ID
from 
(
select t1.province
	,t1.city
	,t2.catgory_id
	,t2.catgory_name
	,t1.goodsid
	,t1.goodsname
	,sales_qty
	,sales_amt
from temp.goods_sale_info t1
left join
temp.goods_info t2
on t1.goodsid=t2.goodsid
) t  --在表t的基础上使用grouping sets函数
group by 
	province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
	grouping sets(
	(province,catgory_id,catgory_name) 
	--这里仅有3个字段,但select中列有不在sets中的非分组字段city、goodsid、goodsname,hive不报错,presto会报错
	)

注意:
1.双表联结的结果出来之后再用grouping sets,即先有t 表,在表t的基础上使用grouping sets函数;
2.sets中的字段不应该含表名;

presto中grouping sets函数

示例:

-- 如果group by写上单一字段
select province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,grouping(province,city,catgory_id,catgory_name,goodsid,goodsname)
from 
(
select t1.province
	,t1.city
	,t2.catgory_id
	,t2.catgory_name
	,t1.goodsid
	,t1.goodsname
	,sales_qty
	,sales_amt
from temp.goods_sale_info t1
left join
temp.goods_info t2
on t1.goodsid=t2.goodsid
) t  --也是要在表t的基础上使用grouping sets函数
group by 
    province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
    ,grouping sets( --这里记得加上逗号,
	(province,catgory_id,catgory_name),
	(province,catgory_id,catgory_name,goodsid,goodsname),
	(province,city),
	(province)
	)

注意:
1、函数grouping要与group by、grouping sets配合使用
2、函数grouping()中列出sets中所有分组涉及的字段,运行后grouing()列生成结果为二进制转化来的十进制数字;,出现为0,不出现为1,按照顺序,早出现的放高位,依次类推;

SELECT origin_state, origin_zip, destination_state, sum(package_weight),
       grouping(origin_state, origin_zip, destination_state)
FROM shipping
GROUP BY GROUPING SETS (
        (origin_state)---011  3,
        (origin_state, origin_zip) --001  1,
        (destination_state));---110   6
origin_state | origin_zip | destination_state | _col3 | _col4
--------------+------------+-------------------+-------+-------
California   | NULL       | NULL              |  1397 |     3
New Jersey   | NULL       | NULL              |   225 |     3
New York     | NULL       | NULL              |     3 |     3
California   |      94131 | NULL              |    60 |     1
New Jersey   |       7081 | NULL              |   225 |     1
California   |      90210 | NULL              |  1337 |     1
New York     |      10002 | NULL              |     3 |     1
NULL         | NULL       | New Jersey        |    58 |     6
NULL         | NULL       | Connecticut       |  1562 |     6
NULL         | NULL       | Colorado          |     5 |     6
(10 rows)

3、group by后面只跟grouping sets(),不加select中的单一字段,否则函数grouping sets无作用

-- 如果group by写上单一字段
select province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,grouping(province,city,catgory_id,catgory_name,goodsid,goodsname)
from 
(
select t1.province
	,t1.city
	,t2.catgory_id
	,t2.catgory_name
	,t1.goodsid
	,t1.goodsname
	,sales_qty
	,sales_amt
from temp.goods_sale_info t1
left join
temp.goods_info t2
on t1.goodsid=t2.goodsid
) t  --也是要在表t的基础上使用grouping sets函数
group by 
    province
	,city
	,catgory_id
	,catgory_name
	,goodsid
	,goodsname
    ,grouping sets( --这里记得加上逗号,
	(province,catgory_id,catgory_name),
	(province,catgory_id,catgory_name,goodsid,goodsname),
	(province,city),
	(province)
	)

4、不用的分组字段不要在select子句中写出

-- 与hive不同,如果不出现在grouping sets中的字段,select子句写上会报错
-- 比如sets中不涉及city、goodsid、goodsname,select子句中写出来报错
select province
-- 	,city
	,catgory_id
	,catgory_name
-- 	,goodsid
-- 	,goodsname
	,sum(sales_qty) as sales_qty
	,sum(sales_amt) as sales_amt
	,grouping(province,catgory_id,catgory_name)
from 
(
select t1.province
	,t1.city
	,t2.catgory_id
	,t2.catgory_name
	,t1.goodsid
	,t1.goodsname
	,sales_qty
	,sales_amt
from temp.goods_sale_info t1
left join
temp.goods_info t2
on t1.goodsid=t2.goodsid
) t
group by 
	grouping sets(
	(province,catgory_id,catgory_name)
	)

5、函数grouping中要将grouping sets所有分组组合用到的字段取并集列出文章来源地址https://www.toymoban.com/news/detail-786829.html

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

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

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

相关文章

  • Hive——DDL(Data Definition Language)数据定义语句用法详解

    IF NOT EXISTS :可选参数, 表示如果数据库已经存在,则不会创建,避免出现重复创建的情况。 COMMENT :可选参数, 用于添加数据库的注释 。 LOCATION :可选参数, 指定数据库的存储路径 WITH DBPROPERTIES :可选参数, 用于设置数据库的额外属性 。 示例: 创建一个名为\\\"mydataba

    2024年04月27日
    浏览(45)
  • (最新版本)hive4.0.0 + hadoop3.3.4 集群安装(无坑版)-大数据学习系列(一)

    网上的找的文档大多残缺不靠谱,所以我整理了一份安装最新版本的hive4..0.0+hadoop3.3.4的学习环境,可以提供大家安装一个完整的hive+hadoop的环境供学习。 由于在公司担任大数据的培训工作后续还会更新一些基础的文章,希望能帮助到大家。 机器信息 Hostname k8s-master k8s-node1 k8

    2024年02月06日
    浏览(50)
  • 大数据期资料2023 Beta版 - Hadoop、HDFS、MapReduce、Hive、ZooKeeper、Kafka、HBase详解

    了解大数据概念、Hadoop、HDFS、MapReduce、Hive、ZooKeeper、Kafka、HBase等技术,包括特点、命令操作和启动关闭方法。获取2023年大数据资料Beta版。

    2024年02月06日
    浏览(230)
  • hive的concat()、concat_ws()和collect_list()、collect_set()的用法

    concat():函数在连接字符串的时候,只要其中一个是NULL,那么将返回NULL。 concat_ws():函数在连接字符串的时候,只要有一个字符串不是NULL,就不会返回NULL。concat_ws():函数需要指定分隔符。 他们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重,而collect_s

    2024年02月04日
    浏览(49)
  • Linux安装Zookeeper、Hadoop、Hive、Hbase全家桶系列

    目录 Linux安装配置Zookeeper Linux安装配置Hadoop Linux安装Hbase 新建文件夹 下载到指定文件夹 官网 Apache ZooKeeper 解压到指定文件 查看 进入目录 创建日志/数据文件夹 复制配置文件 使用vim进入zoo.cfg 修改为如下:  记录一下zookeeper的路径然后 刷新资源 运行 查看状态 创建文件夹加

    2024年02月14日
    浏览(46)
  • 大数据之Hadoop数据仓库Hive

    Hive 是一个构建在 Hadoop 之上的数据仓库,它可以将结构化的数据文件映射成表,并提供类 SQL 查询功能,用于查询的 SQL 语句会被转化为 MapReduce 作业,然后提交到 Hadoop 上运行。 特点: 简单、容易上手 (提供了类似 sql 的查询语言 hql),使得精通 sql 但是不了解 Java 编程的人也

    2024年02月01日
    浏览(48)
  • HDFS 跨集群数据同步(hive,hadoop)

    两个不同的HDFS 集群数据迁移( A集群的数据 - B 集群) 采用的是 SHELL 脚本  按表进行; 日期分区进行; #!/bin/bash ##################### #创建人:DZH #创建日期: 2020-04 #内容: 数据迁移 ##################### ##################################### [ \\\"$#\\\" -ne 0 ] FILE=$1 path=$(cd `dirname $0`; pwd) ############## 获取执

    2024年04月27日
    浏览(56)
  • 大数据技术之Hadoop学习(七)——Hive数据仓库

    目录 素材 一、数据仓库简介 1、数据仓库的认识 (1)数据仓库是面向主题的。 (2)数据仓库是随时间变化的。 (3)数据仓库相对稳定 (4)OLTP和OLAP 2、数据仓库的结构 (1)数据源 (2)数据存储及管理 (3)OLAP 服务器 (4)前端工具 3、数据仓库的数据模型 (1)星状模

    2024年02月17日
    浏览(42)
  • Spark、RDD、Hive 、Hadoop-Hive 和传统关系型数据库区别

    Hive Hadoop Hive 和传统关系型数据库区别 Spark 概念 基于内存的分布式计算框架 只负责算 不负责存 spark 在离线计算 功能上 类似于mapreduce的作用 MapReduce的缺点 运行速度慢 (没有充分利用内存) 接口比较简单,仅支持Map Reduce 功能比较单一 只能做离线计算 Spark优势 运行速度快

    2024年02月13日
    浏览(43)
  • hive查看数据库出现org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

    在启动hive后,使用show databses查看数据库时发现,出现了这个错误 根据搜索查找以及分析得知:可能是hive的数据库MySQL在安装的时候没有初始化,初始化数据库即可 schematool -dbType mysql -initSchema  1.在MySQL中删除元数据 drop database metastore; 2.进入hive中的bin里面 ,输入格式化命令

    2024年02月07日
    浏览(55)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包