多粒度层级下两张SQL表的合并方案咨询

现有两张SQL表utm（主表）和report（数据记录表），需生成Result表所示的统计结果。核心需求是从utm表提取id及所有utm_前缀字段，结合report表的数据，按对应utm记录的有效字段粒度进行聚合统计。

举个例子：utm表某行数据为(24611609, 'myTarget', 'Media', 'Social', NULL, NULL)，report表中有两行匹配数据，此时需按id, utm_campaign, utm_source, utm_medium粒度做SUM聚合并GROUP BY。

此前尝试用不同JOIN组合加UNION的方式覆盖所有粒度组合，但需创建大量组合，效率极低，求高效解决方案。

可以通过动态匹配JOIN条件覆盖所有粒度场景，无需拆分多个UNION组合。核心思路是：JOIN时仅对utm表中不为NULL的字段做等值匹配，NULL字段不限制匹配条件；之后直接按utm表全量字段+日期分组聚合即可。

具体SQL如下：

SELECT
  utm.row_id AS id,
  utm.utm_campaign,
  utm.utm_source,
  utm.utm_medium,
  utm.utm_content,
  utm.utm_term,
  report.date_of_visit,
  SUM(report.sessions) AS sessions,
  SUM(report.pageviews) AS pageviews,
  SUM(report.bounces) AS bounces
FROM utm
INNER JOIN report
  ON utm.row_id = report.id
  AND (utm.utm_campaign IS NULL OR utm.utm_campaign = report.utm_campaign)
  AND (utm.utm_source IS NULL OR utm.utm_source = report.utm_source)
  AND (utm.utm_medium IS NULL OR utm.utm_medium = report.utm_medium)
  AND (utm.utm_content IS NULL OR utm.utm_content = report.utm_content)
  AND (utm.utm_term IS NULL OR utm.utm_term = report.utm_term)
GROUP BY
  utm.row_id,
  utm.utm_campaign,
  utm.utm_source,
  utm.utm_medium,
  utm.utm_content,
  utm.utm_term,
  report.date_of_visit
ORDER BY
  utm.row_id,
  report.date_of_visit;

逻辑说明 #

• JOIN条件优化：每个utm_字段通过(utm字段 IS NULL OR utm字段 = report字段)判断，自动适配该字段是否参与匹配——若utm记录的该字段为NULL，则不对report的对应字段做限制；若不为NULL，则必须严格相等。
• 分组聚合：按utm表所有字段（保证每条utm记录的粒度唯一性）加上日期分组，聚合统计数值字段，自然得到对应粒度的统计结果。

这种写法仅需一次关联和聚合，避免了大量UNION带来的性能损耗，同时完美覆盖所有粒度场景。

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