如何高效实现按campaign、source及组合分组并生成join_id？

问题背景 #

现有数据表结构如下：

期望输出结果：

需求规则 #

• 生成join_id：格式为counter_id: + 拼接utm_campaign=campaign值；若source为Not specified则不拼接额外内容，否则拼接&utm_source=source值。
• 需结合counter_id和date，覆盖所有可能的分组组合：单独按campaign分组、单独按source分组、按campaign+source分组；若新增列（如medium），需自动覆盖所有子集组合（如campaign+medium、source+medium等）。

当前实现方式 #

目前只能通过创建多个视图分组后用UNION ALL合并，示例代码：

SELECT 
  CONCAT(counter_id, ':utm_campaign=', campaign) AS join_id,
  date,
  SUM(sessions) AS sessions
FROM your_table
GROUP BY counter_id, date, campaign

UNION ALL

SELECT 
  CONCAT(counter_id, ':utm_campaign=', campaign, '&utm_source=', source) AS join_id,
  date,
  SUM(sessions) AS sessions
FROM your_table
WHERE source != 'Not specified'
GROUP BY counter_id, date, campaign, source

UNION ALL

SELECT 
  CONCAT(counter_id, ':utm_source=', source) AS join_id,
  date,
  SUM(sessions) AS sessions
FROM your_table
WHERE source != 'Not specified'
GROUP BY counter_id, date, source;

这种方式需要多次扫描表，且新增维度时要修改大量代码，扩展性差。

高效实现方案 #

可以通过生成分组维度的组合集合，再与原表做笛卡尔积关联，最后一次性聚合的方式实现，避免多次扫描表，且扩展性更强：

核心思路 #

1. 用VALUES子句生成所有需要的分组维度标识（1表示保留该维度参与分组，0表示忽略）。
2. 将维度组合与原表关联，根据标识判断当前分组需要保留哪些维度值。
3. 按counter_id、date以及选中的维度分组，计算聚合值并生成join_id。

示例SQL代码 #

WITH dimension_combinations AS (
  -- 生成所有需要的分组维度组合
  SELECT * FROM (VALUES
    (1, 0),  -- 仅按campaign分组
    (0, 1),  -- 仅按source分组
    (1, 1)   -- 按campaign+source分组
  ) AS dims(use_campaign, use_source)
),
grouped_data AS (
  SELECT
    t.counter_id,
    t.date,
    -- 根据维度组合确定分组键
    CASE WHEN dc.use_campaign = 1 THEN t.campaign ELSE NULL END AS campaign_group,
    CASE WHEN dc.use_source = 1 AND t.source != 'Not specified' THEN t.source ELSE NULL END AS source_group,
    SUM(t.sessions) AS sessions
  FROM your_table t
  CROSS JOIN dimension_combinations dc
  -- 过滤无效分组：仅按source分组时排除Not specified的行
  WHERE NOT (dc.use_source = 1 AND t.source = 'Not specified')
  GROUP BY
    t.counter_id,
    t.date,
    CASE WHEN dc.use_campaign = 1 THEN t.campaign ELSE NULL END,
    CASE WHEN dc.use_source = 1 AND t.source != 'Not specified' THEN t.source ELSE NULL END
)
SELECT
  -- 拼接符合要求的join_id
  CONCAT(
    counter_id, ':',
    STRING_AGG(
      CASE
        WHEN campaign_group IS NOT NULL THEN CONCAT('utm_campaign=', campaign_group)
        WHEN source_group IS NOT NULL THEN CONCAT('utm_source=', source_group)
      END, '&' ORDER BY 
        CASE WHEN campaign_group IS NOT NULL THEN 1 ELSE 2 END
    )
  ) AS join_id,
  date,
  sessions
FROM grouped_data
WHERE 
  -- 排除无有效维度的分组
  (campaign_group IS NOT NULL OR source_group IS NOT NULL)
ORDER BY date, join_id;

方案优势 #

• 减少表扫描次数：仅需扫描原表一次，相比多次UNION ALL大幅提升性能。
• 扩展性强：新增维度（如medium）时，只需在dimension_combinations中添加对应的维度组合（比如(1,0,1)表示campaign+medium），无需修改大量聚合逻辑。
• 逻辑统一：所有分组逻辑集中在维度组合定义中，便于维护和修改。

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