如何按不同分组规则对指定列进行SUM分组统计？

问题分析 #

你的核心需求是：

• 活跃用户（termination_date为NULL）：按country、city分组统计数量
• 近12个月非活跃用户（termination_date不为NULL且在近12个月内）：按country、city、termination_reason分组统计数量

原查询的问题确实出在分组逻辑：当你按termination_reason分组时，活跃用户的termination_reason为NULL，会被单独分成一组，但你又加了HAVING termination_reason IS NOT NULL，直接过滤掉了活跃用户的统计结果；同时非活跃用户的分组会让活跃用户的统计在每个reason组里重复计算，导致结果失真。

正确的SQL实现 #

我们可以把两个统计逻辑分开查询，再用UNION ALL合并结果：

-- 统计活跃用户：仅按country、city分组
SELECT
  COUNT(*) AS user_count,
  'active' AS user_status,
  country,
  city,
  NULL AS termination_reason
FROM users
WHERE termination_date IS NULL
GROUP BY country, city

UNION ALL

-- 统计近12个月的非活跃用户：按country、city、termination_reason分组
SELECT
  COUNT(*) AS user_count,
  'inactive_recent' AS user_status,
  country,
  city,
  termination_reason
FROM users
WHERE termination_date IS NOT NULL
  AND termination_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY country, city, termination_reason;

结果说明 #

针对你的示例数据（假设当前日期为2023-10-05），执行后会得到以下结果：

• 瑞典斯德哥尔摩的admin终止用户（2020-03-20）因为超过12个月，不会出现在结果里
• 活跃用户的统计单独成行，按国家城市分组
• 近12个月的非活跃用户按国家、城市、终止原因分组统计

另一种实现（合并为同一行展示） #

如果你希望把活跃和非活跃的统计放在同一行（按国家、城市、终止维度），可以用子查询先获取活跃用户的统计，再关联非活跃用户的统计：

WITH active_stats AS (
  SELECT
    country,
    city,
    COUNT(*) AS active_count
  FROM users
  WHERE termination_date IS NULL
  GROUP BY country, city
)
SELECT
  a.active_count,
  COALESCE(i.inactive_count, 0) AS inactive_count,
  a.country,
  a.city,
  i.termination_reason
FROM active_stats a
LEFT JOIN (
  SELECT
    country,
    city,
    termination_reason,
    COUNT(*) AS inactive_count
  FROM users
  WHERE termination_date IS NOT NULL
    AND termination_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  GROUP BY country, city, termination_reason
) i ON a.country = i.country AND a.city = i.city

UNION ALL

-- 处理只有非活跃用户没有活跃用户的情况（如果存在）
SELECT
  0 AS active_count,
  COUNT(*) AS inactive_count,
  country,
  city,
  termination_reason
FROM users
WHERE termination_date IS NOT NULL
  AND termination_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  AND NOT EXISTS (
    SELECT 1 FROM users u2
    WHERE u2.country = users.country AND u2.city = users.city AND u2.termination_date IS NULL
  )
GROUP BY country, city, termination_reason;

这个方案会把同一国家城市的活跃统计和各终止原因的非活跃统计放在对应行，适合需要横向展示的场景。

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