统计自指定月份起未被计费的活跃用户数量的SQL查询问题

原SQL问题定位 #

当前编写的SQL逻辑存在核心偏差：WHERE s.BILLING_DATE <= DATEADD(month, -1, GETDATE()) 只会筛选出存在至少1条历史计费记录早于N个月前的活跃用户，无法判断用户是否「连续N个月没有新的计费记录」，哪怕用户上周刚产生过计费，只要他历史有更早的计费记录也会被命中，因此统计结果远大于预期。

正确实现方案 #

方案1：统计各未计费时长区间的活跃用户分布 #

可直接得到未计费1个月、3个月、5个月及以上的各区间用户数：

WITH user_last_billing AS (
    -- 关联活跃用户，计算每个用户的最近一次计费日期
    SELECT 
        u.USER_ID,
        MAX(s.BILLING_DATE) AS last_billing_date
    FROM USERS u
    LEFT JOIN SUBSCRIPTION s ON u.USER_ID = s.USER_ID
    WHERE u.STATUS = 'ACTIVE'
    GROUP BY u.USER_ID
)
SELECT 
    CASE 
        WHEN last_billing_date IS NULL THEN '从未计费'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=5 THEN '未计费5个月及以上'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=3 THEN '未计费3-4个月'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=1 THEN '未计费1-2个月'
        ELSE '近1个月已计费'
    END AS billing_interval,
    COUNT(DISTINCT USER_ID) AS user_count
FROM user_last_billing
GROUP BY 
    CASE 
        WHEN last_billing_date IS NULL THEN '从未计费'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=5 THEN '未计费5个月及以上'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=3 THEN '未计费3-4个月'
        WHEN DATEDIFF(month, last_billing_date, GETDATE()) >=1 THEN '未计费1-2个月'
        ELSE '近1个月已计费'
    END

方案2：统计满足指定未计费时长的总用户数 #

如果只需要统计「连续未计费≥N个月」的总用户数，可直接修改WHERE条件中的时间间隔阈值，比如统计未计费≥1个月的总用户：

WITH user_last_billing AS (
    SELECT 
        u.USER_ID,
        MAX(s.BILLING_DATE) AS last_billing_date
    FROM USERS u
    LEFT JOIN SUBSCRIPTION s ON u.USER_ID = s.USER_ID
    WHERE u.STATUS = 'ACTIVE'
    GROUP BY u.USER_ID
)
SELECT COUNT(DISTINCT USER_ID) AS unpaid_user_count
FROM user_last_billing
WHERE 
    -- 从未计费的用户也纳入统计，不需要的话可以去掉该条件
    last_billing_date IS NULL 
    OR DATEDIFF(month, last_billing_date, GETDATE()) >= 1

注意事项 #

• 不同数据库的时间函数存在差异：如果是MySQL，将GETDATE()替换为CURDATE()，DATEDIFF(month, 日期1, 日期2)替换为PERIOD_DIFF(DATE_FORMAT(日期2, '%Y%m'), DATE_FORMAT(日期1, '%Y%m'))；如果是PostgreSQL，将GETDATE()替换为CURRENT_DATE，时间差计算使用EXTRACT(YEAR FROM AGE(CURRENT_DATE, last_billing_date))*12 + EXTRACT(MONTH FROM AGE(CURRENT_DATE, last_billing_date))
• 如果业务规则里不把「从未产生过计费记录的活跃用户」纳入未计费统计，删除对应WHERE条件即可。

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