SQL条件过滤：排除10月转new后11月回processing的首条记录

需求分析 #

需保留每个客户**首次状态为new**的记录，但需排除以下情况：客户首次new状态出现在10月，且11月状态变回processing（或无11月记录）。

解决方案思路 #

1. 定位每个客户首次出现new状态的记录；
2. 关联每个客户11月的状态信息；
3. 通过条件判断排除符合“10月首次new且11月状态非new”的记录。

实现SQL #

以下是基于你提供的示例表的完整查询语句：

WITH first_new_records AS (
    -- 获取每个客户首次new状态的记录
    SELECT 
        id,
        MIN(month) AS first_new_month
    FROM customer
    WHERE status = 'new'
    GROUP BY id
),
november_status AS (
    -- 获取每个客户2022年11月的状态
    SELECT 
        id,
        status AS nov_status
    FROM customer
    WHERE EXTRACT(MONTH FROM month) = 11 
      AND EXTRACT(YEAR FROM month) = 2022
)
-- 筛选符合要求的记录
SELECT 
    fn.id,
    fn.first_new_month AS month,
    'new' AS status
FROM first_new_records fn
LEFT JOIN november_status ns ON fn.id = ns.id
WHERE 
    -- 排除条件：首次new在10月，且11月状态不是new（或无11月记录）
    NOT (
        EXTRACT(MONTH FROM fn.first_new_month) = 10 
        AND EXTRACT(YEAR FROM fn.first_new_month) = 2022
        AND (ns.nov_status != 'new' OR ns.nov_status IS NULL)
    )

结果验证 #

基于你的示例数据，执行上述SQL后会得到以下结果：

客户3的首次new记录（2022-10-31）因11月状态为processing被排除，完全符合需求。

补充说明 #

• 若需适配不同年份，可调整EXTRACT(YEAR FROM month) = 2022的条件；
• 如果业务允许“无11月记录的10月首次new客户”保留，只需去掉OR ns.nov_status IS NULL即可。

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