如何在BigQuery中高效计算所有维度组合分组的中位数？

手动写2^10=1024个UNION ALL确实太折磨人了，尤其是当数据源是动态生成的子查询时，维护成本极高。针对你的需求，这里提供几种更优雅的实现方案，重点适配BigQuery，同时也覆盖通用数据库场景：

方案1：BigQuery动态SQL自动生成（最推荐） #

BigQuery支持EXECUTE IMMEDIATE执行动态拼接的SQL语句，我们可以先自动生成所有维度子集对应的查询片段，再拼接成完整的UNION ALL语句执行。

步骤1：生成所有维度子集的SQL片段 #

这个查询会遍历所有可能的维度组合（从1个维度到10个维度的所有子集），自动生成每个组合对应的SELECT和GROUP BY语句：

WITH dims AS (
  -- 定义所有维度列名
  SELECT ['dim1', 'dim2', 'dim3', 'dim4', 'dim5', 'dim6', 'dim7', 'dim8', 'dim9', 'dim10'] AS dim_list
),
subsets AS (
  -- 生成0到2^10-1的数字，每个数字的二进制位代表是否包含对应维度
  SELECT 
    bit,
    ARRAY(
      SELECT dim 
      FROM UNNEST(dim_list) dim WITH OFFSET pos 
      WHERE BIT_COUNT(bit & (1 << pos)) > 0
    ) AS selected_dims
  FROM dims, UNNEST(GENERATE_ARRAY(0, POWER(2, ARRAY_LENGTH(dim_list)) - 1)) AS bit
)
SELECT 
  STRING_AGG(
    FORMAT("""
      SELECT 
        APPROX_QUANTILES(value, 100)[SAFE_ORDINAL(50)] AS median,
        %s
      FROM (
        -- 替换成你的动态生成表逻辑
        SELECT * FROM your_source_table
      ) AS dynamic_table
      GROUP BY %s
    """, 
    -- 生成SELECT列：选中的维度保留原列，未选中的用NULL填充并保留列名
    STRING_AGG(IF(dim IN selected_dims, dim, "NULL AS " || dim), ", " ORDER BY dim),
    -- 生成GROUP BY列：只包含选中的维度
    STRING_AGG(dim, ", " ORDER BY dim)
    ), "\nUNION ALL\n"
  ) AS full_sql
FROM subsets
WHERE ARRAY_LENGTH(selected_dims) > 0 -- 若需要全表中位数（无分组），可去掉此条件

步骤2：执行动态SQL #

把上面生成的full_sql用EXECUTE IMMEDIATE执行即可：

DECLARE full_sql STRING;

-- 赋值动态SQL（替换上面的查询内容）
SET full_sql = (
  WITH dims AS (...)
  ... -- 完整的子集生成逻辑
);

-- 执行拼接好的SQL
EXECUTE IMMEDIATE full_sql;

这种方法的优势是完全自动化，不管维度数量是10个还是更多，都能自动生成所有组合的查询，彻底避免手动编写重复代码。

方案2：通用递归CTE生成子集（适配支持递归的数据库） #

如果使用其他支持递归CTE的数据库（如PostgreSQL、SQL Server），可以先通过递归生成所有维度子集，再拼接SQL执行。核心思路和方案1一致，只是子集生成方式换成递归：

WITH RECURSIVE dim_subsets AS (
  -- 初始：单个维度的子集
  SELECT 
    ARRAY[dim] AS group_dims,
    dim AS last_dim
  FROM UNNEST(['dim1','dim2','dim3','dim4','dim5','dim6','dim7','dim8','dim9','dim10']) dim
  UNION ALL
  -- 递归：向现有子集添加新维度（避免重复组合）
  SELECT 
    ARRAY_CONCAT(s.group_dims, [d.dim]),
    d.dim
  FROM dim_subsets s
  CROSS JOIN UNNEST(['dim1','dim2','dim3','dim4','dim5','dim6','dim7','dim8','dim9','dim10']) d
  WHERE d.dim > s.last_dim
),
all_subsets AS (
  -- 合并所有子集，包括单个维度、多维度组合，可选添加全表无分组
  SELECT group_dims FROM dim_subsets
  UNION ALL
  SELECT [] AS group_dims -- 全表中位数，可选
)
-- 后续逻辑和方案1一致：拼接每个子集对应的SQL，用动态SQL执行

性能优化提示 #

• 若数据量极大，APPROX_QUANTILES的近似中位数性能远优于精确中位数，如果业务允许优先使用；若需要精确中位数，可替换为PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value)。
• 10个维度会生成1024个子查询，BigQuery的调度能力完全能应对，但如果维度数量继续增加（如20个=1048576个子查询），可能需要拆分查询分批执行。

内容的提问来源于stack exchange，提问作者Guig