PostgreSQL：如何提取客户连续每月消费记录？求简洁SQL实现

现有客户消费表azs，记录客户各月份的购买数据——客户某月份没消费的话，表里就没有对应记录。现在要只提取那些每个月都连续消费、没有中断的客户的所有消费记录。

我尝试用CTE创建标记字段flag，但不知道怎么剔除首行；虽说用GROUP BY加HAVING也能实现，但想找个更简洁的PostgreSQL查询语句。以下是我写的代码：

with cte as (
    select 
        client_id, 
        date_trunc('month', dt)::date as m_date, 
        sum(liters) as s_lit
    from azs 
    group by client_id, m_date
    order by 1, 2
), 
cte2 as (
    SELECT client_id, m_date, 
        CASE WHEN DATE_PART('month', m_date) - DATE_PART('month', lag(m_date) over (partition by client_id order by m_date)) IS NULL THEN 1       
             WHEN DATE_PART('month', m_date) - DATE_PART('month', lag(m_date) over (partition by client_id order by m_date)) > 1 THEN 1
             ELSE 0
        END AS flag
    FROM cte
)
    
select * from cte2

简洁实现方案 #

核心思路是通过对比「当前月份与客户首次消费月份的间隔月数」和「当前记录的序列编号」来判断连续性：如果消费完全连续，这两个数值应该完全匹配，差值始终为0。

WITH monthly_consumption AS (
    SELECT 
        client_id,
        date_trunc('month', dt)::date AS m_date,
        sum(liters) AS s_lit,
        -- 计算当前月份与客户首个消费月的总间隔月数
        EXTRACT(YEAR FROM age(m_date, MIN(m_date) OVER (PARTITION BY client_id))) * 12 +
        EXTRACT(MONTH FROM age(m_date, MIN(m_date) OVER (PARTITION BY client_id))) AS month_diff,
        -- 给客户的消费月份按顺序编序号（从0开始）
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY m_date) - 1 AS seq_num
    FROM azs
    GROUP BY client_id, m_date
)
SELECT client_id, m_date, s_lit
FROM monthly_consumption
WHERE client_id IN (
    SELECT client_id
    FROM monthly_consumption
    GROUP BY client_id
    HAVING MAX(month_diff - seq_num) = 0
);

逻辑说明 #

1. monthly_consumption CTE：
   • 先按客户+月份聚合，得到每个客户每月的总购买量
   • month_diff：算出当前月和客户第一次消费月之间隔了多少个月
   • seq_num：给每个客户的消费月份排号，第一条是0，第二条是1，以此类推
2. 筛选连续消费的客户：
   • 子查询里，把每个客户的month_diff - seq_num的最大值拿出来，如果最大值是0，说明这个客户的所有消费月份都是连续的——只要有一个月中断，这个差值就会大于0
   • 最后取出这些客户的所有消费记录

如果不想用子查询，也可以用窗口函数直接标记，但上面的写法更简洁高效，适合大数据量场景。

内容的提问来源于stack exchange，提问作者Богдан Каредин