如何提取指定维度键值对组合的基数及筛选唯一组合

需求1：获取x、y维度的唯一组合 #

要拿到同一行中x和y的对应组合，不能仅拆分维度行再过滤，得先提取每行的x、y值再组合去重，以下是两种可行方案：

方案1：条件聚合提取x、y值 #

通过CASE WHEN提取每行的x和y值，再用DISTINCT去重：

SELECT DISTINCT
  MAX(CASE WHEN dimension_name = 'x' THEN dimension_value END) AS x,
  MAX(CASE WHEN dimension_name = 'y' THEN dimension_value END) AS y
FROM "metrics"
CROSS JOIN UNNEST(dimensions) AS dimension_map(dimension_name, dimension_value)
WHERE metric = 'A' AND dimension_name IN ('x', 'y')
GROUP BY metrics.ctid -- 用表的行唯一标识分组，确保同一行的x、y被聚合到一起

注：ctid是PostgreSQL的行唯一标识，不同数据库对应标识不同：比如BigQuery用主键或GENERATE_UUID()；MySQL用主键或ROW_NUMBER()生成的行号。

方案2：使用PIVOT（支持的SQL方言适用） #

如果你的数据库支持PIVOT语法（如BigQuery、SQL Server），可直接将维度转成列后去重：

WITH unnested AS (
  SELECT 
    metrics.*,
    dimension_name,
    dimension_value
  FROM "metrics"
  CROSS JOIN UNNEST(dimensions) AS dimension_map(dimension_name, dimension_value)
  WHERE metric = 'A' AND dimension_name IN ('x', 'y')
)
SELECT DISTINCT x, y
FROM unnested
PIVOT (
  MAX(dimension_value) FOR dimension_name IN ('x' AS x, 'y' AS y)
) AS pivoted;

执行后即可得到预期的4种唯一(x,y)组合，统计行数就能获取基数。

需求2：获取z、p、q维度的唯一组合 #

思路是先过滤掉x、y维度，再将同一行的z、p、q值组合成整体去重：

方案1：条件聚合生成结构化组合 #

SELECT DISTINCT
  MAX(CASE WHEN dimension_name = 'z' THEN dimension_value END) AS z,
  MAX(CASE WHEN dimension_name = 'p' THEN dimension_value END) AS p,
  MAX(CASE WHEN dimension_name = 'q' THEN dimension_value END) AS q
FROM "metrics"
CROSS JOIN UNNEST(dimensions) AS dimension_map(dimension_name, dimension_value)
WHERE metric = 'A' AND dimension_name NOT IN ('x', 'y')
GROUP BY metrics.ctid;

若数据库支持结构体/对象类型（如BigQuery），可打包成对象去重：

SELECT DISTINCT
  STRUCT(
    MAX(CASE WHEN dimension_name = 'z' THEN dimension_value END) AS z,
    MAX(CASE WHEN dimension_name = 'p' THEN dimension_value END) AS p,
    MAX(CASE WHEN dimension_name = 'q' THEN dimension_value END) AS q
  ) AS z_p_q组合
FROM "metrics"
CROSS JOIN UNNEST(dimensions) AS dimension_map(dimension_name, dimension_value)
WHERE metric = 'A' AND dimension_name NOT IN ('x', 'y')
GROUP BY metrics.ctid;

方案2：拼接字符串去重（通用简单） #

若不需要单独列，可将z、p、q的键值对拼接成字符串后去重：

SELECT DISTINCT
  STRING_AGG(CONCAT(dimension_name, '=', dimension_value), ', ' ORDER BY dimension_name) AS z_p_q组合
FROM "metrics"
CROSS JOIN UNNEST(dimensions) AS dimension_map(dimension_name, dimension_value)
WHERE metric = 'A' AND dimension_name NOT IN ('x', 'y')
GROUP BY metrics.ctid;

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