如何优化该SQL查询？简化多channelgrouping场景下的重复逻辑

核心思路：避免重复CASE逻辑的两种方案 #

方案1：使用UNPIVOT + PIVOT组合（适用于渠道数量较少的场景） #

如果你的渠道数量在10种以内，用PIVOT可以替代重复的CASE语句，让代码更简洁。假设你的原始查询是通过多次CASE分别计算每个渠道的留存用户数，可重构为：

首先预处理用户的首次渠道和留存状态，再通过PIVOT自动生成各渠道的留存列：

WITH user_base AS (
  -- 这里假设你已经有用户首次访问渠道、访问月份、是否留存的标记
  SELECT
    user_id,
    DATE_TRUNC(first_access_date, MONTH) AS access_month,
    channelgrouping AS first_channel,
    is_retained -- 假设这是一个布尔值：用户后续是否回访
  FROM your_dataset.your_table
)
SELECT
  access_month,
  COUNT(DISTINCT user_id) AS total_users,
  -- PIVOT会自动生成对应渠道的列，无需手动写CASE
  `Organic Search`,
  Direct,
  `Paid Search`,
  Social,
  Email,
  Referral,
  `Display Ads`,
  Other
FROM user_base
PIVOT (
  -- 聚合逻辑：统计该渠道下留存的用户数
  COUNT(DISTINCT CASE WHEN is_retained THEN user_id END)
  -- 指定要转成列的渠道值
  FOR first_channel IN (
    'Organic Search', 
    'Direct', 
    'Paid Search', 
    'Social', 
    'Email', 
    'Referral', 
    'Display Ads', 
    'Other'
  )
)
GROUP BY access_month
ORDER BY access_month;

方案2：动态SQL生成（适用于渠道数量达100种的场景） #

当渠道数量极多或频繁变化时，手动维护PIVOT的渠道列表不现实，可通过EXECUTE IMMEDIATE动态生成查询逻辑：

-- 第一步：获取所有唯一的channelgrouping值，拼接成SQL可用的字符串格式
DECLARE channel_list STRING;
SET channel_list = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", channelgrouping, "'"), ', ')
  FROM your_dataset.your_table
);

-- 第二步：动态生成并执行PIVOT查询
EXECUTE IMMEDIATE FORMAT("""
WITH user_base AS (
  SELECT
    user_id,
    DATE_TRUNC(first_access_date, MONTH) AS access_month,
    channelgrouping AS first_channel,
    is_retained
  FROM your_dataset.your_table
)
SELECT
  access_month,
  COUNT(DISTINCT user_id) AS total_users,
  -- 替换渠道名为带反引号的列名，避免特殊字符报错
  %s
FROM user_base
PIVOT (
  COUNT(DISTINCT CASE WHEN is_retained THEN user_id END)
  FOR first_channel IN (%s)
)
GROUP BY access_month
ORDER BY access_month
""", REPLACE(channel_list, "'", "`"), channel_list);

关键说明 #

• PIVOT方案的优势是代码可读性高，执行性能稳定，适合渠道固定的场景；
• 动态SQL方案完全自动化，无需手动更新渠道列表，适合渠道数量多或经常新增的场景；
• 若不需要将渠道转为列，而是以行的形式展示留存数据，可直接在user_base基础上按access_month和first_channel分组聚合，完全避免重复逻辑：

  SELECT
    access_month,
    first_channel,
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCT CASE WHEN is_retained THEN user_id END) AS retained_users,
    ROUND(COUNT(DISTINCT CASE WHEN is_retained THEN user_id END) / COUNT(DISTINCT user_id), 2) AS retention_rate
  FROM user_base
  GROUP BY access_month, first_channel
  ORDER BY access_month, first_channel;
  

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