在BigQuery SQL中如何将占比低于阈值的类别替换为指定值

原SQL的核心问题是计算占比时未按月份维度分组，导致使用全局失败总量计算占比，而非当月的失败占比；同时未处理successful状态下空failure_reason的关联问题。以下是修正后的逻辑：

WITH mytable AS (
  SELECT *
  FROM UNNEST([
    STRUCT("2022-08-01" AS month, "successful" AS status, "" AS failure_reason, 1000 AS qty),            
    ("2022-08-01","failed", "reason A", 550),
    ("2022-08-01","failed", "reason B", 300),
    ("2022-08-01","failed", "reason C", 100),
    ("2022-08-01","failed", "reason D", 50),
    ("2022-09-01","successful", "", 1500),
    ("2022-09-01","failed", "reason A", 800),
    ("2022-09-01","failed", "reason B", 110),
    ("2022-09-01","failed", "reason C", 80),
    ("2022-09-01","failed", "reason D", 10),
    ("2022-10-01","successful", "", 1100),
    ("2022-10-01","failed", "reason A", 600),
    ("2022-10-01","failed", "reason B", 210),
    ("2022-10-01","failed", "reason C", 120),
    ("2022-10-01","failed", "reason D", 50),
    ("2022-10-01","failed", "reason E", 20) 
  ])
),
-- 计算每个月、每个failure_reason的失败占比
failure_share AS (
  SELECT
    month,
    failure_reason,
    SUM(qty) AS reason_qty,
    SUM(qty) OVER (PARTITION BY month) AS monthly_failed_total,
    SUM(qty) / SUM(qty) OVER (PARTITION BY month) AS share
  FROM mytable
  WHERE status = "failed"
  GROUP BY month, failure_reason
)
SELECT
  t.month,
  t.status,
  -- 处理不同状态的情况：successful直接保留原failure_reason；failed根据占比替换
  CASE
    WHEN t.status = "successful" THEN t.failure_reason
    WHEN fs.share <= 0.1 THEN "other"
    ELSE t.failure_reason
  END AS failure_reason,
  t.qty
FROM mytable t
LEFT JOIN failure_share fs
  ON t.month = fs.month 
  AND t.failure_reason = fs.failure_reason
  AND t.status = "failed" -- 仅关联failed状态的记录
ORDER BY t.month, t.status, failure_reason;

关键修正点： #

• 按月份计算占比：用PARTITION BY month替代原SQL的PARTITION BY status，确保占比是当月失败总量的占比，而非全局失败总量。
• 精准关联：关联时增加AND t.status = "failed"，避免successful状态的空failure_reason误关联。
• 状态区分处理：通过CASE语句明确区分successful和failed状态，保留成功记录的原有字段，仅处理失败记录的低占比reason。

如果需要将other的qty按月份和状态合并，可以在最后一步再增加一个聚合层：

-- 接上面的CTE，增加聚合步骤
SELECT
  month,
  status,
  failure_reason,
  SUM(qty) AS total_qty
FROM (
  -- 上面的SELECT语句内容
)
GROUP BY month, status, failure_reason
ORDER BY month, status, failure_reason;

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