如何关联两个聚合查询结果？用户与支付表年月分组统计需求

嘿，这个需求我之前刚好处理过，其实有几种实用的方法能把这两个独立的统计结果合并成一张按年月分组的报表，我给你拆解下每种方案的逻辑和代码示例：

方案一：子查询 + 全外连接/联合年月维度 #

这种方法适合需要保留所有有数据的年月（不管是只有注册还是只有支付），并且把缺失的统计值补为0的场景。

步骤1：分别生成两个表的聚合统计 #

先单独写出用户注册数和支付笔数的按年月统计SQL：

-- 用户注册数统计
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS year_month,
  COUNT(*) AS users
FROM users
GROUP BY year_month;

-- 支付笔数统计
SELECT
  DATE_FORMAT(payment_time, '%Y-%m') AS year_month,
  COUNT(*) AS payments
FROM payments
GROUP BY year_month;

步骤2：关联两个统计结果 #

如果你的数据库支持FULL OUTER JOIN（比如PostgreSQL、SQL Server），可以直接用全外连接关联：

SELECT
  COALESCE(u.year_month, p.year_month) AS year_month,
  COALESCE(u.users, 0) AS users, -- 把NULL转为0，避免空值
  COALESCE(p.payments, 0) AS payments
FROM
  (SELECT DATE_FORMAT(created_at, '%Y-%m') AS year_month, COUNT(*) AS users FROM users GROUP BY year_month) u
FULL OUTER JOIN
  (SELECT DATE_FORMAT(payment_time, '%Y-%m') AS year_month, COUNT(*) AS payments FROM payments GROUP BY year_month) p
ON u.year_month = p.year_month
ORDER BY year_month;

如果是MySQL这类不支持FULL OUTER JOIN的数据库，可以先通过UNION获取所有存在记录的年月，再分别左连接两个统计：

WITH all_months AS (
  -- 提取所有有注册或支付记录的年月
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS year_month FROM users
  UNION
  SELECT DATE_FORMAT(payment_time, '%Y-%m') AS year_month FROM payments
)
SELECT
  am.year_month,
  COALESCE(u.users, 0) AS users,
  COALESCE(p.payments, 0) AS payments
FROM all_months am
LEFT JOIN (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS year_month, COUNT(*) AS users FROM users GROUP BY year_month
) u ON am.year_month = u.year_month
LEFT JOIN (
  SELECT DATE_FORMAT(payment_time, '%Y-%m') AS year_month, COUNT(*) AS payments FROM payments GROUP BY year_month
) p ON am.year_month = p.year_month
ORDER BY am.year_month;

方案二：UNION ALL合并后再聚合 #

这种方法更简洁，适合数据量不是特别大的场景，核心是把两个表的记录按类型标记后合并，再一次性统计：

SELECT
  year_month,
  SUM(CASE WHEN record_type = 'user' THEN 1 ELSE 0 END) AS users,
  SUM(CASE WHEN record_type = 'payment' THEN 1 ELSE 0 END) AS payments
FROM (
  -- 把用户注册记录标记为user类型
  SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS year_month,
    'user' AS record_type
  FROM users
  UNION ALL
  -- 把支付记录标记为payment类型
  SELECT
    DATE_FORMAT(payment_time, '%Y-%m') AS year_month,
    'payment' AS record_type
  FROM payments
) combined_records
GROUP BY year_month
ORDER BY year_month;

额外优化建议 #

如果你的系统有现成的日期维度表（很多数据仓库会维护包含所有连续年月的维度表），直接用维度表左连接两个统计结果会更高效，还能自动补充那些既没有注册也没有支付的年月（显示0值），示例如下：

SELECT
  dt.year_month,
  COALESCE(u.users, 0) AS users,
  COALESCE(p.payments, 0) AS payments
FROM date_dimension dt
LEFT JOIN (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS year_month, COUNT(*) AS users FROM users GROUP BY year_month
) u ON dt.year_month = u.year_month
LEFT JOIN (
  SELECT DATE_FORMAT(payment_time, '%Y-%m') AS year_month, COUNT(*) AS payments FROM payments GROUP BY year_month
) p ON dt.year_month = p.year_month
-- 可以添加时间范围过滤
WHERE dt.year_month BETWEEN '2023-01' AND '2024-06'
ORDER BY dt.year_month;

注意事项 #

• 日期格式化函数因数据库而异：比如PostgreSQL用TO_CHAR(created_at, 'YYYY-MM')，SQL Server用FORMAT(created_at, 'yyyy-MM')或CONVERT(varchar(7), created_at, 120)；
• COALESCE函数用于将NULL值替换为0，确保报表数据的完整性；
• 一定要按year_month排序，保证报表的时间顺序正确。

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