巩义专业网站建设价格,h5网页制作基础教程,买手机的网站,平台很重要经典的句子GROUPING SETS
该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达#xff0c;它仅仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包含()数据集#xff0c;则表示整体聚合。 Aggregate Query with GRO…GROUPING SETS
该关键字可以实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达它仅仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包含()数据集则表示整体聚合。 Aggregate Query with GROUPING SETS Equivalent Aggregate Query with GROUP BY SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) ) SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b UNION SELECT null, null, SUM( c ) FROM tab1 SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a) SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) ) SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b SELECT a,b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS (a,b) SELECT a, null, SUM( c ) FROM tab1 GROUP BY a UNION SELECT null, b, SUM( c ) FROM tab1 GROUP BY b
ROLLUP
扩展了GROUTING SETS。
其中count(d) 可以换成其他聚合函数例如sum(d)
select a, b, c, count(d) from table group by a, b, c WITH ROLLUP;
// 等价于下面语句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a),());
CUBE
扩展了GROUTING SETS对各种条件进行聚合。
其中count(d) 可以换成其他聚合函数例如sum(d)
select a, b, c,count(d) from table group by a, b, c WITH ROLLUP;
// 等价于下面语句
select a, b, c from table group by a, b, c
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),());
聚合条件 HAVING
having用于在组内进行过滤。
select cid,max(price) mx from orders group by cid having mx 1000;
//等价于下面的子查询语句
select t.cid, t.mx from (select cid, max(price) mx from orders group by cid) t
where t.mx 1000;
Cubes and Rollups
The general syntax is WITH CUBE/ROLLUP. It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.It might be also worth mentioning here that
GROUP BY a, b, c WITH CUBE is equivalent to
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).ROLLUP clause is used with GROUP BY to compute the aggregate at the hierarchy levels of a dimension.
GROUP BY a, b, c with ROLLUP assumes that the hierarchy is a drilling down to b drilling down to c.GROUP BY a, b, c, WITH ROLLUP is equivalent to GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
实例
转载地址 Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
GROUPING SETS,GROUPING__ID,CUBE,ROLLUP
这几个分析函数通常用于OLAP中不能累加而且需要根据不同维度上钻和下钻的指标统计比如分小时、天、月的UV数。
Hive版本为 apache-hive-0.13.1
数据准备 2015-03,2015-03-10,cookie12015-03,2015-03-10,cookie52015-03,2015-03-12,cookie72015-04,2015-04-12,cookie32015-04,2015-04-13,cookie22015-04,2015-04-13,cookie42015-04,2015-04-16,cookie42015-03,2015-03-10,cookie22015-03,2015-03-10,cookie32015-04,2015-04-12,cookie52015-04,2015-04-13,cookie62015-04,2015-04-15,cookie32015-04,2015-04-15,cookie22015-04,2015-04-16,cookie1CREATE 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;OK2015-03 2015-03-10 cookie12015-03 2015-03-10 cookie52015-03 2015-03-12 cookie72015-04 2015-04-12 cookie32015-04 2015-04-13 cookie22015-04 2015-04-13 cookie42015-04 2015-04-16 cookie42015-03 2015-03-10 cookie22015-03 2015-03-10 cookie32015-04 2015-04-12 cookie52015-04 2015-04-13 cookie62015-04 2015-04-15 cookie32015-04 2015-04-15 cookie22015-04 2015-04-16 cookie1GROUPING 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 12015-04 NULL 6 1NULL 2015-03-10 4 2NULL 2015-03-12 1 2NULL 2015-04-12 2 2NULL 2015-04-13 3 2NULL 2015-04-15 2 2NULL 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 12015-04 NULL 6 1NULL 2015-03-10 4 2NULL 2015-03-12 1 2NULL 2015-04-12 2 2NULL 2015-04-13 3 2NULL 2015-04-15 2 2NULL 2015-04-16 2 22015-03 2015-03-10 4 32015-03 2015-03-12 1 32015-04 2015-04-12 2 32015-04 2015-04-13 3 32015-04 2015-04-15 2 32015-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 dayUNION 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 02015-03 NULL 5 12015-04 NULL 6 1NULL 2015-04-12 2 2NULL 2015-04-13 3 2NULL 2015-04-15 2 2NULL 2015-04-16 2 2NULL 2015-03-10 4 2NULL 2015-03-12 1 22015-03 2015-03-10 4 32015-03 2015-03-12 1 32015-04 2015-04-16 2 32015-04 2015-04-12 2 32015-04 2015-04-13 3 32015-04 2015-04-15 2 3等价于SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234UNION 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 dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,dayROLLUP
是CUBE的子集以最左侧的维度为主从该维度进行层级聚合。 比如以month维度进行层级聚合SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID FROM lxw1234 GROUP BY month,dayWITH ROLLUP ORDER BY GROUPING__ID;month day uv GROUPING__ID---------------------------------------------------NULL NULL 7 02015-03 NULL 5 12015-04 NULL 6 12015-03 2015-03-10 4 32015-03 2015-03-12 1 32015-04 2015-04-12 2 32015-04 2015-04-13 3 32015-04 2015-04-15 2 32015-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 02015-04-13 NULL 3 12015-03-12 NULL 1 12015-04-15 NULL 2 12015-03-10 NULL 4 12015-04-16 NULL 2 12015-04-12 NULL 2 12015-04-12 2015-04 2 32015-03-10 2015-03 4 32015-03-12 2015-03 1 32015-04-13 2015-04 3 32015-04-15 2015-04 2 32015-04-16 2015-04 2 3可以实现这样的上钻过程天月的UV-天的UV-总UV这里根据天和月进行聚合和根据天聚合结果一样因为有父子关系如果是其他维度组合的话就会不一样Grouping_ID函数
当我们没有统计某一列时它的值显示为null这可能与列本身就有null值冲突这就需要一种方法区分是没有统计还是值本来就是null。写一个排列组合的算法就马上理解了grouping_id其实就是所统计各列二进制和
直接拿官方文档一个例子O(∩_∩)O哈哈~
Column1 (key)Column2 (value)1NULL1122333NULL45
hql统计 SELECT key, value, GROUPING__ID, count(*) from T1 GROUP BY key, value WITH ROLLUP
统计结果如下 NULLNULL0 0061NULL1 1021NULL3 111113 1112NULL1 101223 1113NULL1 1023NULL3 111333 1114NULL1 101453 111
GROUPING__ID转变为二进制如果对应位上有值为null说明这列本身值就是null。通过类DataFilterNull.py 扫描可以筛选过滤掉列中null、“”统计结果),
总结
cube的分组组合最全是各个维度值的笛卡尔包含null组合
rollup的各维度组合应满足前一维度为null后一位维度必须为null前一维度取非null时下一维度随意
grouping sets则为自定义维度根据需要分组即可。
ps:通过grouping sets的使用可以简化SQL比group by单维度进行union性能更好。
这种函数需要结合实际场景和数据去使用和研究只看说明的话很难理解。 官网的介绍 https://cwiki.apache.org/confluence/display/Hive/EnhancedAggregation%2CCube%2CGroupingandRollup
转发:https://www.2cto.com/database/201708/671294.html
转发https://blog.csdn.net/zhoudetiankong/article/details/52527142
参考:https://blog.csdn.net/suiyingli39/article/details/53540861
参考:https://blog.csdn.net/moon_yang_bj/article/details/17200367
依据上面两篇博客以及官网整理
