编写SQL连续月度采购统计查询遇阻，求解决方案

核心思路 #

先将采购日期聚合到年月维度（忽略具体日期），再通过窗口函数识别连续的年月区间，最后统计每个连续区间的相关指标。

分步实现代码 #

假设表名为customer_purchases，以下是通用SQL实现（适配多数主流数据库如MySQL、PostgreSQL、SQL Server）：

WITH monthly_purchases AS (
    -- 第一步：按客户+年月去重，标记每个客户有采购的年月
    SELECT DISTINCT
        Cust_ID,
        DATE_FORMAT(Purchase_Date, '%Y-%m') AS purchase_yearmonth,
        MIN(Purchase_Date) OVER (PARTITION BY Cust_ID, DATE_FORMAT(Purchase_Date, '%Y-%m')) AS month_first_purchase,
        MAX(Purchase_Date) OVER (PARTITION BY Cust_ID, DATE_FORMAT(Purchase_Date, '%Y-%m')) AS month_last_purchase
    FROM customer_purchases
),
month_groups AS (
    -- 第二步：用窗口函数识别连续年月组
    SELECT
        Cust_ID,
        purchase_yearmonth,
        month_first_purchase,
        month_last_purchase,
        -- 计算当前年月与基准年月的差值，连续年月的差值会一致
        DATE_SUB(
            STR_TO_DATE(purchase_yearmonth, '%Y-%m'),
            INTERVAL ROW_NUMBER() OVER (PARTITION BY Cust_ID ORDER BY purchase_yearmonth) MONTH
        ) AS group_key
    FROM monthly_purchases
),
group_stats AS (
    -- 第三步：统计每个连续组的基础指标
    SELECT
        Cust_ID,
        group_key,
        MIN(month_first_purchase) AS period_start_date,
        MAX(month_last_purchase) AS period_end_date,
        COUNT(*) AS consecutive_months,
        -- 关联原表统计该时段的总采购次数
        (SELECT COUNT(*) 
         FROM customer_purchases p
         WHERE p.Cust_ID = mg.Cust_ID
           AND p.Purchase_Date BETWEEN MIN(mg.month_first_purchase) AND MAX(mg.month_last_purchase)) AS total_purchases
    FROM month_groups mg
    GROUP BY Cust_ID, group_key
)
-- 最终输出结果
SELECT
    Cust_ID,
    CONCAT(period_start_date, ' - ', period_end_date) AS purchase_date_range,
    consecutive_months,
    total_purchases
FROM group_stats
ORDER BY Cust_ID, period_start_date;

关键说明 #

• 年月聚合：用DATE_FORMAT（MySQL）或TO_CHAR（PostgreSQL/SQL Server）将日期转为年月字符串，确保同一个月内的多次采购只算一个有效采购月。
• 连续组识别：通过ROW_NUMBER()生成每个客户的年月排序序号，再用日期减去序号对应的月份，连续的年月会得到相同的group_key，以此分组。
• 指标统计：每个分组的最小/最大采购日期构成时间范围，分组内的年月数量就是连续采购月数，关联原表统计该时段的总采购次数。

针对不同数据库的适配 #

• PostgreSQL：将DATE_FORMAT替换为TO_CHAR(Purchase_Date, 'YYYY-MM')，STR_TO_DATE替换为TO_DATE(purchase_yearmonth, 'YYYY-MM')，DATE_SUB替换为(TO_DATE(purchase_yearmonth, 'YYYY-MM') - INTERVAL '1 month' * ROW_NUMBER() OVER (...))。
• SQL Server：将DATE_FORMAT替换为FORMAT(Purchase_Date, 'yyyy-MM')，STR_TO_DATE替换为CAST(purchase_yearmonth AS DATE)，DATE_SUB替换为DATEADD(MONTH, -ROW_NUMBER() OVER (...), CAST(purchase_yearmonth AS DATE))。

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