共计 6105 个字符，预计需要花费 16 分钟才能阅读完成。
HQL里着实还有很多未发掘的东东，平时写的SQL都感觉很简单。直到最近做些统计报表发现一些新大陆。
grouping sets
grouping sets相当于多个group by分组统计后再union的逻辑，
那么你想统计多个维度的聚合就需要写多个子查询，使用grouping sets 可以轻松帮忙实现。
按省份和地市统计新增数
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, dishi)
-- cluster by sum_xinzeng
distribute by sum_xinzeng sort by sum_xinzeng desc;
OK
shengfen dishi sum_xinzeng
广东省 NULL 4345
上海市 NULL 4092
NULL 深圳市 1518
NULL 广州市 1438
NULL 嘉定区 1434
NULL 珠海市 1389
NULL 普陀区 1369
NULL 浦东新区 1289
Time taken: 83.518 seconds, Fetched: 8 row(s)等价于
select
shengfen,
null,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen
union all
select
null,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by dishi
distribute by sum_xinzeng sort by sum_xinzeng desc;
OK
_u2.shengfen _u2._c1 _u2.sum_xinzeng
广东省 NULL 4345
上海市 NULL 4092
NULL 深圳市 1518
NULL 广州市 1438
NULL 嘉定区 1434
NULL 珠海市 1389
NULL 普陀区 1369
NULL 浦东新区 1289
Time taken: 124.22 seconds, Fetched: 8 row(s)上面的例子实际上就是先以省份为分组字段，然后以市级作为分组字段，然后将结果合并。可以看做事依次遍历完成分局聚合操作。
上面的方法按地市分组时省份那列为null，可以优化为：
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, (shengfen, dishi))
-- cluster by sum_xinzeng
distribute by sum_xinzeng sort by sum_xinzeng desc;
OK
shengfen dishi sum_xinzeng
广东省 NULL 4345
上海市 NULL 4092
广东省 深圳市 1518
广东省 广州市 1438
上海市 嘉定区 1434
广东省 珠海市 1389
上海市 普陀区 1369
上海市 浦东新区 1289
Time taken: 77.62 seconds, Fetched: 8 row(s)等价于：
select
shengfen,
null,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen
union all
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng
from xinzeng_tb
group by shengfen, dishi
distribute by sum_xinzeng sort by sum_xinzeng desc;
OK
_u2.shengfen _u2._c1 _u2.sum_xinzeng
广东省 NULL 4345
上海市 NULL 4092
广东省 深圳市 1518
广东省 广州市 1438
上海市 嘉定区 1434
广东省 珠海市 1389
上海市 普陀区 1369
上海市 浦东新区 1289按grouping sets查询和group by查询再union的等价关系。
上面的例子优化了在以市级聚合时省份为NULL现象，市级作为省级下面更小的单位，也就是说省份肯定是有的，结果为NULL不太合理，所以通过更改grouping sets 以省市联合作为分组字段就可以解决上述问题。
grouping__id（两个下划线_）
这个函数返回一个位向量，该位向量对应于每一列是否存在。对于每一列，如果结果集中的某一行已经聚合了该列，则结果集中的某一行的值为“1”，否则该值为“0”。这可以用于在数据中有空值时进行区分。
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi
grouping sets (shengfen, (shengfen, dishi))
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;
OK
shengfen dishi sum_xinzeng grouping__id
广东省 深圳市 1518 0
广东省 广州市 1438 0
上海市 嘉定区 1434 0
广东省 珠海市 1389 0
上海市 普陀区 1369 0
上海市 浦东新区 1289 0
广东省 NULL 4345 1
上海市 NULL 4092 1
Time taken: 74.346 seconds, Fetched: 8 row(s)上面最后倒数两行，分别只有一个字段是有效的聚合字段，所以 grouping__id 为1 ，有一个不存在 就记为 1，按照二级制编码的方式得到 grouping__id。
等价于
select
shengfen,
null,
sum(xinzeng) as sum_xinzeng,
1 as grouping__id
from xinzeng_tb
group by shengfen
union all
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
0 as grouping__id
from xinzeng_tb
group by shengfen, dishi
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;
OK
_u2.shengfen _u2._c1 _u2.sum_xinzeng _u2.grouping__id
广东省 深圳市 1518 0
广东省 广州市 1438 0
上海市 嘉定区 1434 0
广东省 珠海市 1389 0
上海市 普陀区 1369 0
上海市 浦东新区 1289 0
广东省 NULL 4345 1
上海市 NULL 4092 1
Time taken: 117.174 seconds, Fetched: 8 row(s)select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi
with rollup
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;
OK
shengfen dishi sum_xinzeng grouping__id
广东省 深圳市 1518 0
广东省 广州市 1438 0
上海市 嘉定区 1434 0
广东省 珠海市 1389 0
上海市 普陀区 1369 0
上海市 浦东新区 1289 0
广东省 NULL 4345 1
上海市 NULL 4092 1
NULL NULL 8437 3
Time taken: 69.779 seconds, Fetched: 9 row(s)group_id是为了区分每条输出结果是属于哪一个group by的数据。它是根据group by后面声明的顺序字段是否存在于当前group by中的一个二进制位组合数据。
grouping__id为0的是group by中所有列都被选中了，二进制00，所以标识为0
grouping__id为1的是group by中只有一列被选中了，二进制01，所以标识为1
grouping__id为3的是group by中没有一列被选中，二进制11，所以标识为3
Grouping
分组函数指示GROUP BY子句中的表达式是否对给定行进行聚合。值0表示属于分组集的列，而值1表示不属于分组集的列。
select
shengfen,
dishi,
sum(xinzeng) as sum_xinzeng,
grouping(shengfen, dishi) grouping_two,
grouping(shengfen) grouping_s,
grouping(dishi) grouping_d,
grouping__id
from xinzeng_tb
group by shengfen, dishi
with rollup
-- cluster by grouping__id
distribute by grouping__id sort by grouping__id, sum_xinzeng desc;
OK
shengfen dishi sum_xinzeng grouping_two grouping_s grouping_d grouping__id
广东省 深圳市 1518 0 0 0 0
广东省 广州市 1438 0 0 0 0
上海市 嘉定区 1434 0 0 0 0
广东省 珠海市 1389 0 0 0 0
上海市 普陀区 1369 0 0 0 0
上海市 浦东新区 1289 0 0 0 0
广东省 NULL 4345 1 0 1 1
上海市 NULL 4092 1 0 1 1
NULL NULL 8437 3 1 1 3
Time taken: 73.224 seconds, Fetched: 9 row(s)cube and rollup
CUBE/ROLLUP必须与GROUP BY一起使用。
cube可以得到group by这些维度上所有可能的聚合问题的答案。
例如：
GROUP BY a, b, c WITH CUBE 等价于
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), ©, ( )).
rollup
ROLLUP子句用于与GROUP BY一起计算维度层次结构级别上的聚合。
使用ROLLUP将a、b、c分组，假设层次结构是“a”向下钻取到“b”，钻取到“c”。
GROUP BY a, b, c, WITH ROLLUP 等价于 GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
select
shengfen,
dishi,
riqi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi, riqi
--grouping sets (shengfen, dishi, riqi)
with rollup
--cluster by grouping__id
distribute by grouping__id sort by grouping__id desc, sum_xinzeng desc;
OK
shengfen dishi riqi sum_xinzeng grouping__id
NULL NULL NULL 8437 7
广东省 NULL NULL 4345 3
上海市 NULL NULL 4092 3
广东省 深圳市 NULL 1518 1
广东省 广州市 NULL 1438 1
上海市 嘉定区 NULL 1434 1
广东省 珠海市 NULL 1389 1
上海市 普陀区 NULL 1369 1
上海市 浦东新区 NULL 1289 1
广东省 深圳市 2019-01-02 770 0
广东省 深圳市 2019-01-03 748 0
广东省 广州市 2019-01-02 726 0
上海市 嘉定区 2019-01-03 721 0
上海市 嘉定区 2019-01-02 713 0
广东省 广州市 2019-01-03 712 0
广东省 珠海市 2019-01-02 708 0
上海市 普陀区 2019-01-03 691 0
广东省 珠海市 2019-01-03 681 0
上海市 普陀区 2019-01-02 678 0
上海市 浦东新区 2019-01-03 664 0
上海市 浦东新区 2019-01-02 625 0cube
elect
shengfen,
dishi,
riqi,
sum(xinzeng) as sum_xinzeng,
grouping__id
from xinzeng_tb
group by shengfen, dishi, riqi
--grouping sets (shengfen, dishi, riqi)
with cube
--cluster by grouping__id
distribute by grouping__id sort by grouping__id desc, sum_xinzeng desc;
OK
shengfen dishi riqi sum_xinzeng grouping__id
NULL NULL NULL 8437 7
NULL NULL 2019-01-02 4220 6
NULL NULL 2019-01-03 4217 6
NULL 深圳市 NULL 1518 5
NULL 广州市 NULL 1438 5
NULL 嘉定区 NULL 1434 5
NULL 珠海市 NULL 1389 5
NULL 普陀区 NULL 1369 5
NULL 浦东新区 NULL 1289 5
NULL 深圳市 2019-01-02 770 4
NULL 深圳市 2019-01-03 748 4
NULL 广州市 2019-01-02 726 4
NULL 嘉定区 2019-01-03 721 4
NULL 嘉定区 2019-01-02 713 4
NULL 广州市 2019-01-03 712 4
NULL 珠海市 2019-01-02 708 4
NULL 普陀区 2019-01-03 691 4
NULL 珠海市 2019-01-03 681 4
NULL 普陀区 2019-01-02 678 4
NULL 浦东新区 2019-01-03 664 4
NULL 浦东新区 2019-01-02 625 4
广东省 NULL NULL 4345 3
上海市 NULL NULL 4092 3
广东省 NULL 2019-01-02 2204 2
广东省 NULL 2019-01-03 2141 2
上海市 NULL 2019-01-03 2076 2
上海市 NULL 2019-01-02 2016 2
广东省 深圳市 NULL 1518 1
广东省 广州市 NULL 1438 1
上海市 嘉定区 NULL 1434 1
广东省 珠海市 NULL 1389 1
上海市 普陀区 NULL 1369 1
上海市 浦东新区 NULL 1289 1
广东省 深圳市 2019-01-02 770 0
广东省 深圳市 2019-01-03 748 0
广东省 广州市 2019-01-02 726 0
上海市 嘉定区 2019-01-03 721 0
上海市 嘉定区 2019-01-02 713 0
广东省 广州市 2019-01-03 712 0
广东省 珠海市 2019-01-02 708 0
上海市 普陀区 2019-01-03 691 0
广东省 珠海市 2019-01-03 681 0
上海市 普陀区 2019-01-02 678 0
上海市 浦东新区 2019-01-03 664 0
上海市 浦东新区 2019-01-02 625 0上述语句需要的数据文件
正文完
请博主喝杯咖啡吧！
