如何编写SQL按月统计满足90天无交易的inactive用户数？

问题描述 #

需要编写SQL查询按月统计inactive用户数，规则为：用户在统计月份前至少3个月无任何交易记录。例如统计2020年1月时，统计最后交易在2019年9月的用户；统计2020年2月时，统计最后交易在2019年10月的用户，以此类推。

示例数据 #

预期结果 #

用户尝试的SQL（存在问题） #

WITH date AS
(SELECT '2020-01-01' as s UNION ALL SELECT '2020-02-01' UNION ALL SELECT '2020-03-01' UNION ALL 
SELECT '2020-04-01' UNION ALL SELECT '2020-05-01' UNION ALL SELECT '2020-06-01' UNION ALL 
SELECT '2020-07-01' UNION ALL SELECT '2020-08-01' UNION ALL SELECT '2020-09-01' UNION ALL 
SELECT '2020-10-01' UNION ALL SELECT '2020-11-01' UNION ALL SELECT '2020-12-01')
SELECT date.s, COUNT(t.customer_id)
FROM date LEFT JOIN trips t ON toDate(formatDateTime(t.booking_time, '%Y-%m-%d')) = date_sub(month, 3, toDate(date.s))
GROUP BY date.s
ORDER BY date.s

问题分析 #

1. 表名/字段名不匹配：交易表实际字段是user_id和date，代码中误用了customer_id和booking_time；表名写成了trips而非实际的用户交易表。
2. 逻辑错误：原SQL试图匹配交易日期等于统计月份往前推3个月的日期，但实际需要判断的是用户最后一次交易的月份等于统计月份往前推3个月，且之后无任何交易。

修复后的原方案 #

WITH date_range AS (
    SELECT '2020-01-01' AS month_start UNION ALL
    SELECT '2020-02-01' UNION ALL
    SELECT '2020-03-01' UNION ALL
    SELECT '2020-04-01' UNION ALL
    SELECT '2020-05-01' UNION ALL
    SELECT '2020-06-01' UNION ALL
    SELECT '2020-07-01' UNION ALL
    SELECT '2020-08-01' UNION ALL
    SELECT '2020-09-01' UNION ALL
    SELECT '2020-10-01' UNION ALL
    SELECT '2020-11-01' UNION ALL
    SELECT '2020-12-01'
),
user_last_transaction AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MAX(date)) AS last_trans_month
    FROM 交易表
    GROUP BY user_id
)
SELECT 
    DATE_FORMAT(d.month_start, '%Y-%m') AS Month,
    COUNT(ul.user_id) AS Count
FROM date_range d
LEFT JOIN user_last_transaction ul 
    ON ul.last_trans_month = DATE_SUB(d.month_start, INTERVAL 3 MONTH)
GROUP BY d.month_start
ORDER BY d.month_start;

修复说明 #

• 修正了表名、字段名的错误匹配。
• 新增user_last_transactionCTE计算每个用户的最后交易月份，确保只统计最后交易时间符合条件的用户。
• 使用月份级别的日期函数（DATE_TRUNC/DATE_SUB）保证逻辑匹配的准确性。

其他实现方式 #

方式1：用户-月份笛卡尔积筛选 #

WITH date_range AS (
    SELECT '2020-01-01' AS month_start UNION ALL
    SELECT '2020-02-01' UNION ALL
    SELECT '2020-03-01' UNION ALL
    SELECT '2020-04-01' UNION ALL
    SELECT '2020-05-01' UNION ALL
    SELECT '2020-06-01' UNION ALL
    SELECT '2020-07-01' UNION ALL
    SELECT '2020-08-01' UNION ALL
    SELECT '2020-09-01' UNION ALL
    SELECT '2020-10-01' UNION ALL
    SELECT '2020-11-01' UNION ALL
    SELECT '2020-12-01'
),
all_users AS (
    SELECT DISTINCT user_id FROM 交易表
),
user_month_combinations AS (
    SELECT 
        a.user_id,
        d.month_start
    FROM all_users a
    CROSS JOIN date_range d
),
user_inactive_check AS (
    SELECT 
        um.user_id,
        um.month_start,
        CASE 
            WHEN MAX(t.date) <= DATE_SUB(um.month_start, INTERVAL 3 MONTH)
            THEN 1
            ELSE 0
        END AS is_inactive
    FROM user_month_combinations um
    LEFT JOIN 交易表 t ON um.user_id = t.user_id
    GROUP BY um.user_id, um.month_start
)
SELECT 
    DATE_FORMAT(month_start, '%Y-%m') AS Month,
    SUM(is_inactive) AS Count
FROM user_inactive_check
GROUP BY month_start
ORDER BY month_start;

逻辑说明 #

生成所有用户与统计月份的笛卡尔积，对每个组合判断用户最后交易时间是否早于统计月份前3个月，最后按月统计符合条件的用户数，逻辑直观易懂。

方式2：窗口函数标记活跃区间 #

WITH date_range AS (
    SELECT '2020-01-01' AS month_start UNION ALL
    SELECT '2020-02-01' UNION ALL
    SELECT '2020-03-01' UNION ALL
    SELECT '2020-04-01' UNION ALL
    SELECT '2020-05-01' UNION ALL
    SELECT '2020-06-01' UNION ALL
    SELECT '2020-07-01' UNION ALL
    SELECT '2020-08-01' UNION ALL
    SELECT '2020-09-01' UNION ALL
    SELECT '2020-10-01' UNION ALL
    SELECT '2020-11-01' UNION ALL
    SELECT '2020-12-01'
),
user_transactions AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', date) AS trans_month,
        LEAD(DATE_TRUNC('month', date), 1, '9999-12-01') OVER (PARTITION BY user_id ORDER BY date) AS next_trans_month
    FROM 交易表
),
user_inactive_months AS (
    SELECT 
        user_id,
        GENERATE_SERIES(
            DATE_ADD(trans_month, INTERVAL 1 MONTH),
            DATE_SUB(next_trans_month, INTERVAL 3 MONTH),
            INTERVAL 1 MONTH
        ) AS inactive_month
    FROM user_transactions
    WHERE DATE_SUB(next_trans_month, INTERVAL 3 MONTH) >= DATE_ADD(trans_month, INTERVAL 1 MONTH)
)
SELECT 
    DATE_FORMAT(d.month_start, '%Y-%m') AS Month,
    COUNT(DISTINCT uim.user_id) AS Count
FROM date_range d
LEFT JOIN user_inactive_months uim 
    ON uim.inactive_month = d.month_start
GROUP BY d.month_start
ORDER BY d.month_start;

逻辑说明 #

• 使用LEAD窗口函数获取用户下一次交易的月份，确定用户从最后交易月份+1个月到下一次交易月份前3个月的区间为inactive期。
• 通过GENERATE_SERIES生成该区间内的所有月份，最后关联日期范围表统计数量。
• 注意：GENERATE_SERIES语法在不同数据库中略有差异（如MySQL 8.0+、PostgreSQL支持）。

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