时间戳

首页 > 解决方案 > 通过 BigQuery 与 Teradata 中的分组集进行分组

sql - 通过 BigQuery 与 Teradata 中的分组集进行分组

问题描述

BigQuery 是否具有与group by GROUPING SETSTeradata 中相同的功能？

例如在 Teradata 中，我们可以使用类似的东西

group   by GROUPING SETS ( (1,2,3,4,5,6 ), (1,2,4,5,6 ))

BigQuery 有可能吗？

标签： sqlgoogle-bigqueryteradata

解决方案

当您需要汇总统计信息（例如 sum、min、max 等）时，可以从上述示例之一中详细说明另一种选择

with table as (select 'au' dim1, 'samsung' dim2, 'browser1' dim3 , 100 clk, 10000 e
union all
select 'ind' c, 'iphone' d, 'browser2' b , 1000 clk, 10000 e
union all
select 'ind' c, 'samsung' d, 'browser2' b , 10 clk, 1 e)

select 
     dim1
    ,dim2
    ,dim3
    ,max(clk)clk
    ,max(e) e
    from
(SELECT 

     dim1
    ,dim2
    ,dim3
    ,sum(clk) over (partition by dim1,dim2,dim3) clk
    ,sum(e) over (partition by dim1,dim2,dim3) e
FROM table,
UNNEST ([dim1, 'ALL']) AS dim1,
UNNEST ([dim2, 'ALL']) AS dim2,
UNNEST ([dim3, 'ALL']) AS dim3
)
GROUP BY dim1,dim2,dim3

推荐阅读