CASE WHEN函数异常排查及替换now()为指定日期的技术咨询

问题1：CASE WHEN逻辑异常修复 #

你的CASE逻辑存在两个核心问题：

1. 条件覆盖了所有可能的日期区间，导致ELSE 'l'永远不会触发。因为reference_more_than_31D（now()-31天）比reference_more_than_30D（now()-30天）早1天，任何日期要么满足>= now()-31天，要么满足<= now()-30天，没有中间区间留给ELSE。
2. 逻辑与你期望的状态定义不匹配，需要重新梳理区间划分：
   • 最近30天内的订单 → 'r'
   • 31-90天前的订单 → 'l'
   • 超过90天前的订单 → 'a'

问题2：替换当前日期并过滤订单年份 #

直接将now()替换为固定日期'2021-07-01'::DATE，同时在orders表的查询中添加年份过滤条件，仅保留2020和2021年的订单。

优化后的完整SQL #

WITH customer_latest_order AS (
    SELECT 
        customer_id,
        MAX(submitted_on) AS latest_order
    FROM orders
    -- 仅处理2020和2021年的订单
    WHERE submitted_on >= '2020-01-01' 
      AND submitted_on < '2022-01-01'
    GROUP BY customer_id
),
reference_dates AS (
    SELECT
        '2021-07-01'::DATE AS current_date,
        '2021-07-01'::DATE - INTERVAL '30 days' AS ref_30d_ago,
        '2021-07-01'::DATE - INTERVAL '91 days' AS ref_91d_ago
)
SELECT
    clo.customer_id,
    clo.latest_order,
    rd.ref_30d_ago,
    rd.ref_91d_ago,
    CASE
        -- 最近30天内（含）的订单
        WHEN clo.latest_order >= rd.ref_30d_ago THEN 'r'
        -- 31-90天前的订单（即晚于91天前，早于30天前）
        WHEN clo.latest_order > rd.ref_91d_ago THEN 'l'
        -- 超过90天前的订单（含91天前及更早）
        ELSE 'a'
    END AS status
FROM customer_latest_order clo
CROSS JOIN reference_dates rd;

优化说明 #

1. 去掉了冗余的DISTINCT和不必要的CTE（原AA），因为参考日期是固定值，无需关联orders表计算。
2. 用CROSS JOIN引入固定参考日期，逻辑更清晰。
3. 明确了每个状态对应的日期区间，避免条件重叠。
4. 添加了订单年份过滤，只处理2020和2021年的订单。

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