Presto SQL-给group by添加汇总行

熟悉Excel数据透视表的同学都知道，Excel数据透视表可选择显示分类汇总，如下图所示：

一些固化的表格型报表可能需要有汇总行，以便直接使用而不需要再在Excel中加工调整。一些报告用的数据可能既需要分类数据也需要汇总数据，如果能一次性加工出来，效率自然更高。那SQL如何实现此类需求呢，最原始的方法是分别group by后用union all合并，这种方式代码会比较长且容易出错，好在Presto SQL直接为group by提供了3种支持复杂分组操作的语法：

• grouping sets：手动指定多个分组方式。

• cube：自动为给定的字段生成所有可能的分组集，无需按照grouping sets进行一一指定。指定n个字段，就会有2ⁿ个分组方式。

• rollup：对于1个指定的字段，自动为该字段生成所有可能的子分组集。

注意，以上3种语法在指定字段时，只支持表中已存在的字段，不支持表达式新生成的字段，如果需要新字段，要先建临时表并生成所要用的字段。

下面我们就来看看Presto SQL中复杂分组操作的具体实现，假设我们有如下数据表table1：

示例代码1：

--grouping sets语法
select
    product,
    sum(amount) as amount
from table1
group by grouping sets((product))
order by 1;


select
    category,
    product,
    sum(amount) as amount
from table1
group by grouping sets((category), (category, product), (product))
order by 1,2;

代码结果1：

示例代码2：

--cube语法
select
    category,
    product,
    sum(amount) as amount
from table1
group by cube(category, product)  --相当于grouping sets((category), (category, product), (product), ())
order by 1,2;

代码结果2：

示例代码3：

--rollup语法
select
    category,
    product,
    sum(amount) as amount
from table1
group by rollup(category, product)  --相当于grouping sets(category, (category, product), ())
order by 1,2;

代码结果3：

示例代码4：

--不能使用表达式,只能先新建字段
with
    tmp as (
        select
            category,
            product,
            to_char(time, 'yyyy-mm') as month,
            amount
        from table1
    )
select
    category,
    product,
    month,
    sum(amount) as amount
from tmp
group by rollup(category, product, month)  --month不能直接使用to_char(time, 'yyyy-mm')
order by 1,2,3;

代码结果4：

示例代码5：

--将汇总行的字段值填充为汇总
with
    tmp as (
        select
            category,
            product,
            sum(amount) as amount
        from table1
        group by rollup(category, product)
    )
select
    if(category is null, '汇总', category) as category,
    if(product is null, '汇总', product) as product,
    amount
from tmp
order by 1,2;

代码结果5：

以上就是Presto SQL为group by提供的3种复杂分组操作的实现，在需要使用union all实现不同分组操作时，比如需要给group by添加汇总行时，可以考虑选用这3种语法。