【常用操作】高阶聚合函数

⌘ 总结

cube的分组组合最全，是各个维度值的笛卡尔（包含null）组合；
rollup的各维度组合应满足，前一维度为null后一位维度必须为null，前一维度取非null时，下一维度随意；
grouping sets则为自定义维度，根据需要分组即可。
PS:通过grouping sets的使用可以简化SQL，比group by单维度进行union all性能更好。

⌘ 数据准备

⌘⌘ 表结构

CREATE TABLE test (f1 string,  
                   f2 string,  
                   f3 string,  
                   cnt int) ROW FORMAT delimited FIELDS TERMINATED BY '\t' stored AS textfile;  

⌘⌘ 原始数据

A A B 1  
B B A 1  
A A A 2

⌘ grouping sets

实现同一数据集的多重group by操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达。

⌘⌘ 示例一

查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')  
FROM test  
GROUP BY f1,  
         f2,  
         f3  
GROUPING SETS((f1),(f1,f2))

grouping sets查询结果：

⌘⌘ 示例二

SELECT year_month
  ,DATE
  ,SUM(pv) AS pv
  ,GROUPING__ID
FROM data
GROUP BY year_month,DATE 
GROUPING SETS(year_month, DATE)
ORDER BY GROUPING__ID;
等价于：
SELECT year_month
  ,NULL
  ,SUM(pv) AS pv
  ,1 AS GROUPING__ID
FROM data
GROUP BY year_month
UNION ALL
SELECT NULL
  ,DATE
  ,SUM(pv) AS pv
  ,2 AS GROUPING__ID
FROM data
GROUP BY DATE;

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

⌘ cube

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

⌘⌘ 示例一

查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       **rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0') **** **
FROM test  
GROUP BY f1,  
         f2,  
         f3 WITH CUBE;

with cube查询结果

⌘⌘ 示例二

多维数据集CUBE 运算符可用于生成 n 维的多维数据集，即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计，例如：

-- 此 SELECT 语句返回的结果集既显示了 Item 中每个值的小计，也显示了 Item 中所有值的总计：
SELECT CASE WHEN (GROUPING(Item) = 1)THEN 'ALL'
    ELSE ISNULL(Item, 'UNKNOWN')
    END AS Item
  ,SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item
WITH CUBE;

⌘⌘ 示例三

SELECT year_month
  ,DATE
  ,SUM(pv) AS pv
  ,GROUPING__ID
FROM data
GROUP BY year_month
  ,DATE
WITH CUBE
ORDER BY GROUPING__ID;

等价于：

SELECT NULL
  ,NULL
  ,SUM(pv) AS pv
  ,0 AS GROUPING__ID
FROM data
UNION ALL
SELECT year_month
  ,NULL
  ,SUM(pv) AS pv
  ,1 AS GROUPING__ID
FROM data
GROUP BY year_month
UNION ALL
SELECT NULL
  ,DATE
  ,SUM(pv) AS pv
  ,2 AS GROUPING__ID
FROM data
GROUP BY DATE
UNION ALL
SELECT year_month
  ,DATE
  ,SUM(pv) AS pv
  ,3 AS GROUPING__ID
FROM data
GROUP BY year_month
  ,DATE;

结果： 
+----------+----------+----+------------+
|year_month|date      |pv  |grouping__id|
+----------+----------+----+------------+
|2015-03   |2015-03-10|11  |0           |
|2015-03   |2015-03-12|3   |0           |
|2015-04   |2015-04-13|11  |0           |
|2015-04   |2015-04-12|14  |0           |
|2015-04   |2015-04-16|7   |0           |
|2015-04   |null      |32  |1           |
|2015-03   |null      |14  |1           |
|null      |null      |46  |3           |
+----------+----------+----+------------+

⌘ with rollup

查询语句

SELECT f1,  
       f2,  
       f3,  
       sum(cnt),  
       GROUPING__ID,  
       rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')  
FROM test  
GROUP BY f1,  
         f2,  
         f3 WITH ROLLUP;

with rollup查询结果

---

⌘ 拓展

结果分析函数 GROUPING() 和 GROUPING__ID：

⌘⌘ 通用型方法：NVL() 函数处理null值

注意：前提是处理好原本字段中的null值

NVL(Item,‘ALL’) as Item

SELECT 
nvl(Item,'ALL') Item,
nvl(Color,'ALL') Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color 
WITH CUBE;

⌘⌘ 结果集中的NULL值处理一 GROUPING() 函数：

注意：GROUPING() 函数 不能在hive中使用

CUBE 或 ROLLUP 等操作所生成的空值带来一个问题：如何区分 CUBE 或 ROLLUP 等操作所生成的 NULL 值和从实际数据中返回的 NULL 值？这个问题可用 GROUPING 函数解决。当行由 CUBE 或 ROLLUP 等运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 等运算符添加时，该函数将导致附加列的输出值为 0。

在 CUBE 或 ROLLUP 等操作中，所生成的 NULL 代表全体值。可将 SELECT 语句写成使用 GROUPING 函数将所生成的 NULL 替换为字符串 ALL 或 SUM。因为事实数据中的 NULL 表明数据值未知，所以 SELECT 语句还可译码为返回字符串 UNKNOWN 替代来自事实数据的 NULL。例如：

⌘⌘ 结果集中的NULL值处理二 GROUPING__ID：

GROUPING_ID 解释

1、GROUPING_ID 是hive生成的，是标识维度组合的十进制数字。

2、Grouping_ID 的生成规则：在 grouping sets 的组合中存在的字段使用 1 表示，否则使用 0 表示。（对称赋值）

例如1：

grouping sets 中有3组字段组合：grouping sets(month,day,(month,day))，分析如下：

（1）month => month,null : 0 1 换算成十进制 1

（2）day => null,day :1,0 换算成十进制 2

（3）(month,day) => (month,day):1,1 换算成十进制 3

例如2：

如果有 group by platform, position, title, bg

grouping sets(platform, position, title, bg,(platform, position),(platform, position, title),(platform, position, title, bg))  

组合为 ：

（1）platform,null,null,null :0001 换算成十进制为 1

（2）null,null,titile,null ：0100 换算成十进制为 4

（3）(platform, position, title, bg)：1111 换算成十进制 15

其他的就不具体细说了。

有了Grouping_ID 维度组合也就可以更加方便组合。

比如 使用hive语句：

if(cast(GROUPING__ID as int) & 4 = 0, 'all', title) as title

表示 组合中是否有title，若对title进行了group by操作，则使用title原有值，若没有进行group by 操作，则将其置为’ALL’