Hive SQL grouping sets 用法
SQL 用法 hive Sets grouping
2023-09-14 08:58:38 时间
概述
GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
这几个分析函数通常用于OLAP中,不能累加,而且需要根据不同维度上钻和下钻的指标统计,比如,分小时、天、月的UV数。
GROUPING SETS和GROUPING__ID
说明
在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL
GROUPING__ID,表示结果属于哪一个分组集合。
查询语句:
select month, day, count(distinct cookieid) as uv, GROUPING__ID from cookie.cookie5 group by month,day grouping sets (month,day) order by GROUPING__ID;
等价于:
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day
查询结果
结果说明
第一列是按照month进行分组
第二列是按照day进行分组
第三列是按照month或day分组是,统计这一组有几个不同的cookieid
第四列grouping_id表示这一组结果属于哪个分组集合,根据grouping sets中的分组条件month,day,1是代表month,2是代表day
再比如:
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM cookie5 GROUP BY month,day GROUPING SETS (month,day,(month,day)) ORDER BY GROUPING__ID;
等价于:
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day UNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5 GROUP BY month,day
CUBE
说明
根据GROUP BY的维度的所有组合进行聚合
查询语句
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM cookie5 GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID;
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM cookie5 UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day UNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5 GROUP BY month,day
查询结果
ROLLUP
说明
是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合
查询语句
-- 比如,以month维度进行层级聚合
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM cookie5 GROUP BY month,day WITH ROLLUP ORDER BY GROUPING__ID;
可以实现这样的上钻过程:
月天的UV->月的UV->总UV
--把month和day调换顺序,则以day维度进行层级聚合:
可以实现这样的上钻过程:
天月的UV->天的UV->总UV
(这里,根据天和月进行聚合,和根据天聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样)
https://www.cnblogs.com/qingyunzong/p/8798987.html
感谢博主!
相关文章
- sql服务器系统时间格式,SQL Server 日期格式和日期操做
- SQL Server 2016 TempDb里的显著提升
- MySQL动态SQL:实现动态查询的高效方式。(mysql动态sql)
- MySQL的更新SQL实用技巧(mysql更新sql)
- 实用技巧:精通Oracle常用SQL(oracle常用sql)
- 语句MySQL中常用SQL语句实用指南(mysql常用sql)
- Building Complex Database Applications Made Easy with Linux QT and SQL(linuxqtsql)
- 采用SQL正则替换MSSQL——最佳实践详解(sql正则替换mssql)
- SQL Server出现错误0:解决方案(sqlserver错误0)
- SQL Server无法正常启动的解决方案(sqlserver起不来)
- 使用SQL Server提升算法性能的方法(sqlserver算法)
- 变SQL Server端口号更改:指南与实践(sqlserver端口改)
- 表使用SQL Server建立表格的小白指南(sqlserver建)
- SQL Server入门:一个小白的快乐学习之旅(sqlserver小白)
- 深入浅出MySQL主键SQL,轻松学会数据库设计(mysql主键sql)
- Oracle SQL跟踪工具的使用指南(oracle跟踪sql工具)
- 账号揭秘:如何隐藏SQL Server账号(隐藏sqlserver)
- 使用Oracle SQL实现数据脱敏(oracle sql脱敏)
- PHPmysqli增强批量执行sql语句的实现代码