SQL技术咨询：如何基于审计表跟踪会员资格随时间的变化

核心思路 #

要解决这个问题，核心是补全会员状态的时间区间，让每个会员的每段状态都对应到持续的所有月份，再结合完整的年月序列统计数量。具体分四步：

• 生成覆盖统计周期的所有年月序列，确保没有遗漏月份
• 清理审计记录：每个账户每月只保留最后一条变更（当月多次变更取最终状态）
• 标记每个状态的生效区间：用窗口函数获取每个状态的开始月份和下一次变更的开始月份（即当前状态的结束月份）
• 关联年月序列与状态区间，按年月和状态分组统计账户数

SQL示例（以PostgreSQL为例） #

WITH account_status_changes AS (
    -- 过滤每个账户每月的最后一条变更记录
    SELECT
        account_id,
        membership_level,
        membership_status,
        DATE_TRUNC('month', last_change_date) AS status_start_month,
        -- 用LEAD获取下一次变更的起始月份，无后续变更则用当前年月
        LEAD(DATE_TRUNC('month', last_change_date), 1, DATE_TRUNC('month', CURRENT_DATE)) OVER (
            PARTITION BY account_id ORDER BY last_change_date
        ) AS status_end_month
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY account_id, DATE_TRUNC('month', last_change_date)
                ORDER BY last_change_date DESC
            ) AS rn
        FROM membership_audit
    ) t
    WHERE rn = 1
),
-- 生成统计周期内的所有年月序列
date_series AS (
    SELECT
        generate_series(
            (SELECT MIN(status_start_month) FROM account_status_changes),
            (SELECT MAX(status_end_month) FROM account_status_changes),
            INTERVAL '1 month'
        ) AS year_month
)
-- 关联统计，生成最终报表
SELECT
    TO_CHAR(d.year_month, 'YYYY-MM') AS year_month,
    COUNT(CASE WHEN ac.membership_level = 'recurring_member' AND ac.membership_status = 'active' THEN 1 END) AS "recurring/active",
    COUNT(CASE WHEN ac.membership_level = 'recurring_member' AND ac.membership_status = 'lapsed' THEN 1 END) AS "recurring/lapsed",
    COUNT(CASE WHEN ac.membership_level = 'annual_member' AND ac.membership_status = 'active' THEN 1 END) AS "annual/active",
    COUNT(CASE WHEN ac.membership_level = 'annual_member' AND ac.membership_status = 'lapsed' THEN 1 END) AS "annual/lapsed",
    COUNT(CASE WHEN ac.membership_level = 'major_donor' AND ac.membership_status = 'active' THEN 1 END) AS "major_donor/active",
    COUNT(CASE WHEN ac.membership_level = 'major_donor' AND ac.membership_status = 'lapsed' THEN 1 END) AS "major_donor/lapsed"
FROM date_series d
LEFT JOIN account_status_changes ac
    ON d.year_month >= ac.status_start_month
    AND d.year_month < ac.status_end_month
GROUP BY d.year_month
ORDER BY d.year_month;

关键部分说明 #

1. account_status_changes CTE：

   • 先用ROW_NUMBER()过滤每个账户每月的最后一条变更，确保当月多次变更只取最终状态
   • 用LEAD()窗口函数获取下一次变更的月份，这样每个状态的生效区间是[status_start_month, status_end_month)，自动覆盖状态持续的所有月份

2. date_series CTE：

   • 生成从最早状态开始到最晚状态结束的所有年月，确保没有遗漏任何需要统计的月份

3. 关联统计：

   • 用LEFT JOIN把年月序列和状态区间关联，确保即使某个年月没有变更记录，也能统计到持续的会员状态
   • 用CASE语句按状态分组计数，直接生成透视后的报表格式

不同数据库适配调整 #

SQL Server #

• 替换DATE_TRUNC为DATEADD + DATEDIFF：DATEADD(MONTH, DATEDIFF(MONTH, 0, last_change_date), 0)
• 用递归CTE生成年月序列：

date_series AS (
    SELECT MIN(status_start_month) AS year_month FROM account_status_changes
    UNION ALL
    SELECT DATEADD(MONTH, 1, year_month) FROM date_series
    WHERE year_month < (SELECT MAX(status_end_month) FROM account_status_changes)
)

MySQL #

• 用数字表生成年月序列（示例最多覆盖12个月，可按需扩展数字表）：

date_series AS (
    SELECT DATE_FORMAT(DATE_ADD((SELECT MIN(status_start_month) FROM account_status_changes), INTERVAL n MONTH), '%Y-%m') AS year_month
    FROM (
        SELECT 0 n UNION ALL SELECT 1 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
    ) numbers
    WHERE DATE_ADD((SELECT MIN(status_start_month) FROM account_status_changes), INTERVAL n MONTH) <= (SELECT MAX(status_end_month) FROM account_status_changes)
)

内容的提问来源于stack exchange，提问作者Nonprofit Data Wrangler