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日
    浏览(51)
  • 大数据期资料2023 Beta版 - Hadoop、HDFS、MapReduce、Hive、ZooKeeper、Kafka、HBase详解

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

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

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

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

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

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

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

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

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

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

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

    2024年02月17日
    浏览(45)
  • 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日
    浏览(56)
  • Spark、RDD、Hive 、Hadoop-Hive 和传统关系型数据库区别

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

    2024年02月13日
    浏览(46)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包