按月重置的每日滚动distinct cid计数SQL查询实现

现有测试表结构及数据如下：

CREATE TABLE tbl (
  id      int NOT NULL
, date    date NOT NULL
, cid     int NOT NULL
);
 
INSERT INTO tbl VALUES
  (1 , '2022-01-01', 1)
, (2 , '2022-01-01', 1)
, (3 , '2022-01-01', 2)
, (4 , '2022-01-01', 3)
, (5 , '2022-01-02', 1)
, (6 , '2022-01-02', 4)
, (7 , '2022-01-03', 5)
, (8 , '2022-01-03', 6)
, (9 , '2022-02-01', 1)
, (10, '2022-02-01', 5)
, (11, '2022-02-02', 5)
, (12, '2022-02-02', 3)
;

需求为统计按月重置的每日滚动去重cid数量，规则：

• 每月1日仅统计当日去重cid数
• 每月后续日期统计当月1日至当日的累计去重cid数
• 计数在每个自然月月初重置，跨月数据不参与上月累计

期望输出结果：

date        distinct_cids
2022-01-01           3
2022-01-02           4
2022-01-03           6
2022-02-01           2
2022-02-02           3

核心逻辑：先计算每个cid在每个自然月的首次出现日期，只要首次出现日期早于/等于统计日，就计入当日累计数，避免重复计数。该写法兼容绝大多数支持CTE的SQL引擎（MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH cid_monthly_first AS (
    -- 先取每个cid在每个月第一次出现的日期
    SELECT
        DATE_FORMAT(date, '%Y-%m') AS year_month,
        MIN(date) AS first_show_date,
        cid
    FROM tbl
    GROUP BY DATE_FORMAT(date, '%Y-%m'), cid
)
SELECT
    d.date,
    COUNT(c.cid) AS distinct_cids
FROM (SELECT DISTINCT date FROM tbl) d -- 取出所有需要统计的日期
LEFT JOIN cid_monthly_first c
    ON DATE_FORMAT(d.date, '%Y-%m') = c.year_month -- 只关联同月份数据
    AND c.first_show_date <= d.date -- 首次出现时间在统计日之前/当日
GROUP BY d.date
ORDER BY d.date;

说明：如果使用PostgreSQL等其他SQL方言，将DATE_FORMAT(date, '%Y-%m')替换为对应方言的年月格式化函数即可（如PostgreSQL用TO_CHAR(date, 'YYYY-MM')），核心逻辑无需调整。

执行后返回结果和期望完全一致。

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