Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP 操作指南

问题导读

1.GROUPING SETS与另外哪种方式等价？
2.根据GROUP BY的维度的所有组合进行聚合由哪个关键字完成？
3.ROLLUP与ROLLUP关系是什么？

 

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

Hive版本为 apache-hive-0.13.1数据准备

数据准备：

2019-03,2019-03-10,cookie1 2019-03,2019-03-10,cookie5 2019-03,2019-03-12,cookie7 2019-04,2019-04-12,cookie3 2019-04,2019-04-13,cookie2 2019-04,2019-04-13,cookie4 2019-04,2019-04-16,cookie4 2019-03,2019-03-10,cookie2 2019-03,2019-03-10,cookie3 2019-04,2019-04-12,cookie5 2019-04,2019-04-13,cookie6 2019-04,2019-04-15,cookie3 2019-04,2019-04-15,cookie2 2019-04,2019-04-16,cookie1 CREATE EXTERNAL TABLE zcg1234 ( month STRING, day STRING,  cookieid STRING  ) ROW FORMAT DELIMITED  FIELDS TERMINATED BY ','  stored as textfile location '/tmp/zcg1234/'; hive> select * from zcg1234; OK 2019-03 2019-03-10      cookie1 2019-03 2019-03-10      cookie5 2019-03 2019-03-12      cookie7 2019-04 2019-04-12      cookie3 2019-04 2019-04-13      cookie2 2019-04 2019-04-13      cookie4 2019-04 2019-04-16      cookie4 2019-03 2019-03-10      cookie2 2019-03 2019-03-10      cookie3 2019-04 2019-04-12      cookie5 2019-04 2019-04-13      cookie6 2019-04 2019-04-15      cookie3 2019-04 2019-04-15      cookie2 2019-04 2019-04-16      cookie1

GROUPING SETS

在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

SELECT  month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID  FROM zcg1234  GROUP BY month,day  GROUPING SETS (month,day)  ORDER BY GROUPING__ID; month      day            uv      GROUPING__ID ------------------------------------------------ 2019-03    NULL            5       1 2019-04    NULL            6       1 NULL       2019-03-10      4       2 NULL       2019-03-12      1       2 NULL       2019-04-12      2       2 NULL       2019-04-13      3       2 NULL       2019-04-15      2       2 NULL       2019-04-16      2       2 等价于  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM zcg1234 GROUP BY month  UNION ALL  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM zcg1234 GROUP BY day

 

其中的 GROUPING__ID，表示结果属于哪一个分组集合。

 

CUBE

根据GROUP BY的维度的所有组合进行聚合。

SELECT  month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID  FROM zcg1234  GROUP BY month,day  WITH CUBE  ORDER BY GROUPING__ID; month                              day             uv     GROUPING__ID -------------------------------------------- NULL            NULL            7       0 2019-03         NULL            5       1 2019-04         NULL            6       1 NULL            2019-04-12      2       2 NULL            2019-04-13      3       2 NULL            2019-04-15      2       2 NULL            2019-04-16      2       2 NULL            2019-03-10      4       2 NULL            2019-03-12      1       2 2019-03         2019-03-10      4       3 2019-03         2019-03-12      1       3 2019-04         2019-04-16      2       3 2019-04         2019-04-12      2       3 2019-04         2019-04-13      3       3 2019-04         2019-04-15      2       3 等价于 SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM zcg1234 UNION ALL  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM zcg1234 GROUP BY month  UNION ALL  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM zcg1234 GROUP BY day UNION ALL  SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM zcg1234 GROUP BY month,day

ROLLUP

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合： SELECT  month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID   FROM zcg1234  GROUP BY month,day WITH ROLLUP  ORDER BY GROUPING__ID;     month                              day             uv     GROUPING__ID --------------------------------------------------- NULL             NULL            7       0 2019-03          NULL            5       1 2019-04          NULL            6       1 2019-03          2019-03-10      4       3 2019-03          2019-03-12      1       3 2019-04          2019-04-12      2       3 2019-04          2019-04-13      3       3 2019-04          2019-04-15      2       3 2019-04          2019-04-16      2       3     可以实现这样的上钻过程： 月天的UV->月的UV->总UV     --把month和day调换顺序，则以day维度进行层级聚合： SELECT  day, month, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID   FROM zcg1234  GROUP BY day,month  WITH ROLLUP  ORDER BY GROUPING__ID;     day                                month              uv     GROUPING__ID ------------------------------------------------------- NULL            NULL               7       0 2019-04-13      NULL               3       1 2019-03-12      NULL               1       1 2019-04-15      NULL               2       1 2019-03-10      NULL               4       1 2019-04-16      NULL               2       1 2019-04-12      NULL               2       1 2019-04-12      2019-04            2       3 2019-03-10      2019-03            4       3 2019-03-12      2019-03            1       3 2019-04-13      2019-04            3       3 2019-04-15      2019-04            2       3 2019-04-16      2019-04            2       3 可以实现这样的上钻过程： 天月的UV->天的UV->总UV （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）

这种函数，需要结合实际场景和数据去使用和研究，只看说明的话，很难理解。

 

实操一遍才会有感觉哦，光看不行的。