sql - Big Query SQL - 对所有可能的聚合级别进行分组

Question

假设我有一个表，其中包含 value, dim1, ..., dim 10 列，我想获取列 dim1 上所有可能分组的中位数>, ..., dim10。即在所有子组上，跨任意 9 个维度的所有子组，任意 8...

我可以

SELECT * FROM
( 
    SELECT 
        APPROX_QUANTILES(value, 100)[SAFE_ORDINAL(50)] as median,
        dim1, ..., dim10
    FROM table
    GROUP BY dim1, ..., dim10
)
UNION ALL
( 
    SELECT 
        APPROX_QUANTILES(value, 100)[SAFE_ORDINAL(50)] as median,
        dim1, ..., dim9
        NULL as dim10
    FROM table
    GROUP BY dim1, ..., dim9
)
UNION ALL
... --2^10 subtables

但这相当长，尤其是如果 table 是即时计算的。有没有更好的办法？

我正在处理大查询，但答案可能不是特定于大查询的

Answer 1

在 BigQuery 支持 GROUP BY CUBE 之前，我发现这是对每个组组合进行所需聚合的最紧凑/可读的方式:

SELECT 
    APPROX_QUANTILES(value, 100)[SAFE_ORDINAL(50)] as median,
    dim1, ..., dim10
FROM table,
UNNEST ([dim1, 'ALL']) AS dim1,
...
UNNEST ([dim10, 'ALL']) AS dim10
GROUP BY dim1, ..., dim10