T-SQL实现按Wallet和月份分组的累计客户数与唯一客户数查询

嘿，我来帮你搞定这个累计统计的优化方案！你之前用多次插入的方法确实太繁琐了，而且数据量大的时候效率很低，这里给你两种更高效的通用方案，一次查询就能得到所有月份的累计结果。

方案一：生成月份截止日期表关联统计（通用所有主流数据库） #

这个思路是先生成本年度每个月份对应的截止日期（比如1月对应<2019-02-01，2月对应<2019-03-01），然后关联原表一次性统计每个截止日期前的累计数据，完美替代你之前的多次插入操作。

示例代码（以SQL Server为例） #

WITH monthly_cutoffs AS (
    -- 生成本年度1-12月的月份编号和对应的截止日期
    SELECT
        month_num,
        DATEADD(month, month_num, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)) AS cutoff_date
    FROM (
        SELECT 1 AS month_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
    ) AS months
)
SELECT
    t.[Wallet],
    COUNT(DISTINCT t.[Client]) AS [Unique],
    COUNT(t.[Client]) AS [Count],
    mc.month_num AS [Month]
FROM monthly_cutoffs mc
-- 左关联原表，筛选出截止日期前的本年度数据
LEFT JOIN tmp_tbl t
    ON t.[Date] < mc.cutoff_date
    AND YEAR(t.[Date]) = YEAR(GETDATE())
GROUP BY mc.month_num, t.[Wallet]
ORDER BY t.[Wallet], mc.month_num;

不同数据库的适配调整 #

• PostgreSQL：可以用generate_series简化月份生成

  WITH monthly_cutoffs AS (
      SELECT
          generate_series(1,12) AS month_num,
          (DATE_TRUNC('year', CURRENT_DATE) + (generate_series(1,12) || ' months')::INTERVAL) AS cutoff_date
  )
  -- 后续查询逻辑同上
  

• MySQL 8.0+：用递归CTE生成月份

  WITH RECURSIVE months AS (
      SELECT 1 AS month_num
      UNION ALL
      SELECT month_num + 1 FROM months WHERE month_num < 12
  ),
  monthly_cutoffs AS (
      SELECT
          month_num,
          DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-01-01'), INTERVAL month_num MONTH) AS cutoff_date
      FROM months
  )
  -- 后续查询逻辑同上
  

这个方案的优势很明显：只需要扫描原表一次（数据库优化器会自动处理），代码简洁易维护，修改年份或月份范围只需要调整CTE部分即可。

方案二：窗口函数统计（适用于支持窗口COUNT(DISTINCT)的数据库） #

如果你的数据库支持窗口函数中的COUNT(DISTINCT ...)（比如PostgreSQL 9.4+、Oracle 12c+），可以用窗口函数直接计算累计值，代码更紧凑：

WITH wallet_month_data AS (
    SELECT
        [Wallet],
        [Client],
        EXTRACT(MONTH FROM [Date]) AS month_num,
        1 AS record_count -- 每条记录计数为1，方便累计求和
    FROM tmp_tbl
    WHERE EXTRACT(YEAR FROM [Date]) = EXTRACT(YEAR FROM CURRENT_DATE)
)
SELECT
    [Wallet],
    -- 累计唯一客户数：从年初到当前月的所有唯一客户
    COUNT(DISTINCT [Client]) OVER (
        PARTITION BY [Wallet] 
        ORDER BY month_num 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Unique],
    -- 累计客户总数：从年初到当前月的所有记录数之和
    SUM(record_count) OVER (
        PARTITION BY [Wallet] 
        ORDER BY month_num 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Count],
    month_num AS [Month]
FROM wallet_month_data
GROUP BY [Wallet], month_num
ORDER BY [Wallet], month_num;

注意：SQL Server目前不支持窗口函数里的COUNT(DISTINCT)，所以这个方案只适合特定数据库。

对比你之前的方法 #

• 避免了多次手动编写INSERT语句，减少重复劳动
• 减少了对临时表的多次写入操作，提升性能
• 逻辑更清晰，便于后续修改和扩展

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