月度注册与储户查询优化：历史数据冻结问题修复请求

问题根源 #

原SQL的核心问题在于：

1. TotalSignups和TotalSavers是累计到截止日的总人数，而非当月独立统计数据，导致进入2月后，若当月无新用户，1月与2月的累计数值完全一致；
2. 历史月份的统计依赖实时数据，若存在回溯修改（如用户注册时间被调整到1月），历史月数据会持续更新，无法实现冻结效果。

修复方案 #

方案1：调整SQL逻辑实现当月独立统计（无需额外表） #

如果仅需基于现有数据实现"历史月数据冻结、当月实时更新"，可将统计逻辑改为当月专属数据，同时限制历史月的统计截止到当月月末：

WITH FirstInflowData AS (
  SELECT 
    CreatedBy AS UserId,
    MIN(CreatedOn) AS FirstInflowDate
  FROM 
    [Database].wallettopuplog
  WHERE 
    Status = 1 
    AND IsCredited = 1 
    AND Amount > 0
  GROUP BY 
    CreatedBy
)
SELECT 
  DATE_FORMAT(
    MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.mn - 1) MONTH,
    '%M'
  ) AS MonthName,
  
  -- 当月注册人数（历史月截止到月末，当前月截止到当天）
  (SELECT COUNT(*) 
   FROM [Database].customer
   WHERE 
     YEAR(CreatedOn) = YEAR(CURDATE())
     AND MONTH(CreatedOn) = m.mn
     AND CreatedOn <= CASE 
           WHEN m.mn < MONTH(CURDATE()) THEN 
             LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.mn - 1) MONTH)
           ELSE CURDATE() 
         END
  ) AS MonthlySignups,
  
  -- 当月新增储户数（首次入金在当月，历史月固定）
  (SELECT COUNT(UserId)
   FROM FirstInflowData
   WHERE YEAR(FirstInflowDate) = YEAR(CURDATE())
     AND MONTH(FirstInflowDate) = m.mn
     AND FirstInflowDate <= CASE 
           WHEN m.mn < MONTH(CURDATE()) THEN 
             LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.mn - 1) MONTH)
           ELSE CURDATE() 
         END
  ) AS MonthlySavers,
  
  -- 截止到该月的累计注册人数（历史月固定为月末累计值）
  (SELECT COUNT(*) 
   FROM [Database].customer
   WHERE CreatedOn <= 
         CASE 
           WHEN m.mn < MONTH(CURDATE()) THEN 
             LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.mn - 1) MONTH)
           ELSE CURDATE() 
         END
  ) AS CumulativeSignups,
  
  -- 截止到该月的累计储户人数（历史月固定为月末累计值）
  (SELECT COUNT(DISTINCT w.CreatedBy) 
   FROM [Database].wallettopuplog w
   JOIN [Database].customer c ON c.Id = w.CreatedBy
   WHERE c.Status = 1       
     AND w.Status = 1       
     AND w.IsCredited = 1   
     AND w.Amount > 0       
     AND w.CreatedOn <= 
         CASE 
           WHEN m.mn < MONTH(CURDATE()) THEN 
             LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (m.mn - 1) MONTH)
           ELSE CURDATE() 
         END
  ) AS CumulativeSavers
FROM 
  (
    SELECT 1 AS mn 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 m
WHERE m.mn <= MONTH(CURDATE())
ORDER BY m.mn;

方案2：使用快照表彻底冻结历史数据（解决回溯修改问题） #

如果存在用户数据被回溯修改（如注册时间被调整到历史月），仅靠SQL逻辑无法完全冻结数据，需创建月度快照表，每月初自动生成上月统计结果：

1. 创建快照表

CREATE TABLE monthly_stats_snapshot (
  year INT NOT NULL,
  month INT NOT NULL,
  monthly_signups INT NOT NULL,
  monthly_savers INT NOT NULL,
  cumulative_signups INT NOT NULL,
  cumulative_savers INT NOT NULL,
  snapshot_date DATE NOT NULL DEFAULT CURRENT_DATE(),
  PRIMARY KEY (year, month)
);

2. 每月1号执行定时任务生成上月快照

INSERT INTO monthly_stats_snapshot (year, month, monthly_signups, monthly_savers, cumulative_signups, cumulative_savers)
SELECT
  YEAR(CURDATE()) - IF(MONTH(CURDATE()) = 1, 1, 0) AS stats_year,
  MONTH(CURDATE()) - 1 AS stats_month,
  (SELECT COUNT(*) FROM [Database].customer WHERE YEAR(CreatedOn) = stats_year AND MONTH(CreatedOn) = stats_month),
  (SELECT COUNT(DISTINCT CreatedBy) FROM [Database].wallettopuplog WHERE YEAR(CreatedOn) = stats_year AND MONTH(CreatedOn) = stats_month AND Status=1 AND IsCredited=1 AND Amount>0),
  (SELECT COUNT(*) FROM [Database].customer WHERE CreatedOn <= LAST_DAY(MAKEDATE(stats_year, 1) + INTERVAL (stats_month-1) MONTH)),
  (SELECT COUNT(DISTINCT w.CreatedBy) FROM [Database].wallettopuplog w JOIN [Database].customer c ON c.Id=w.CreatedBy WHERE c.Status=1 AND w.Status=1 AND w.IsCredited=1 AND w.Amount>0 AND w.CreatedOn <= LAST_DAY(MAKEDATE(stats_year, 1) + INTERVAL (stats_month-1) MONTH))
ON DUPLICATE KEY UPDATE
  monthly_signups = VALUES(monthly_signups),
  monthly_savers = VALUES(monthly_savers),
  cumulative_signups = VALUES(cumulative_signups),
  cumulative_savers = VALUES(cumulative_savers),
  snapshot_date = CURRENT_DATE();

3. 查询时合并快照数据与当月实时数据

WITH CurrentMonthStats AS (
  SELECT
    YEAR(CURDATE()) AS stats_year,
    MONTH(CURDATE()) AS stats_month,
    (SELECT COUNT(*) FROM [Database].customer WHERE YEAR(CreatedOn) = YEAR(CURDATE()) AND MONTH(CreatedOn) = MONTH(CURDATE()) AND CreatedOn <= CURDATE()) AS monthly_signups,
    (SELECT COUNT(UserId) FROM (SELECT MIN(CreatedOn) AS FirstInflowDate, CreatedBy AS UserId FROM [Database].wallettopuplog WHERE Status=1 AND IsCredited=1 AND Amount>0 GROUP BY CreatedBy) t WHERE YEAR(FirstInflowDate) = YEAR(CURDATE()) AND MONTH(FirstInflowDate) = MONTH(CURDATE()) AND FirstInflowDate <= CURDATE()) AS monthly_savers,
    (SELECT COUNT(*) FROM [Database].customer WHERE CreatedOn <= CURDATE()) AS cumulative_signups,
    (SELECT COUNT(DISTINCT w.CreatedBy) FROM [Database].wallettopuplog w JOIN [Database].customer c ON c.Id=w.CreatedBy WHERE c.Status=1 AND w.Status=1 AND w.IsCredited=1 AND w.Amount>0 AND w.CreatedOn <= CURDATE()) AS cumulative_savers
)
SELECT
  DATE_FORMAT(MAKEDATE(year, 1) + INTERVAL (month-1) MONTH, '%M') AS MonthName,
  monthly_signups,
  monthly_savers,
  cumulative_signups,
  cumulative_savers
FROM monthly_stats_snapshot
WHERE year = YEAR(CURDATE()) AND month < MONTH(CURDATE())
UNION ALL
SELECT
  DATE_FORMAT(MAKEDATE(stats_year, 1) + INTERVAL (stats_month-1) MONTH, '%M') AS MonthName,
  monthly_signups,
  monthly_savers,
  cumulative_signups,
  cumulative_savers
FROM CurrentMonthStats
ORDER BY month;

内容的提问来源于stack exchange，提问作者The Igbo Wolf