如何筛选客户果类中检查日最接近过期日的记录并修复SQL标记问题

问题梳理 #

原SQL存在三个核心问题：

• WHERE子句语法错误：OR 'Banana'写法不合法，无法正确筛选香蕉类型的记录
• 同日记录未正确标记：部分LastCheckedDate与ExpirationDate为同一天的记录未被标记为1（原逻辑本身支持该场景，需确保日期计算无偏差）
• 重复统计：未筛选每个客户下苹果、香蕉中LastCheckedDate最接近ExpirationDate的记录，导致同一客户同一水果类型可能有多条重复记录

修正后的SQL代码 #

WITH RankedRecords AS (
    SELECT
        "CUSTOMERPROFILE"."ExpirationDate",
        "CUSTOMERPROFILE"."LastCheckedDate",
        "CUSTOMERPROFILE"."CUSTOMERID",
        "CUSTOMERPROFILE"."FruitType",
        CASE 
            WHEN "CUSTOMERPROFILE"."LastCheckedDate" BETWEEN DATEADD(d, -30, "CUSTOMERPROFILE"."ExpirationDate") 
                AND DATEADD(d, 5, "CUSTOMERPROFILE"."ExpirationDate") 
            THEN 1 
            ELSE 0 -- 可选：不符合条件时标记为0，若不需要可省略ELSE，返回NULL
        END AS BetweenTheRange,
        -- 计算日期差绝对值，用于排序取最接近过期日的记录
        ROW_NUMBER() OVER (
            PARTITION BY "CUSTOMERPROFILE"."CUSTOMERID", "CUSTOMERPROFILE"."FruitType"
            ORDER BY ABS(DATEDIFF(d, "CUSTOMERPROFILE"."LastCheckedDate", "CUSTOMERPROFILE"."ExpirationDate")) ASC
        ) AS RecordRank
    FROM "dbo"."CUSTOMERPROFILE" AS "CUSTOMERPROFILE"
    -- 修正WHERE子句，用IN简化筛选逻辑
    WHERE "CUSTOMERPROFILE"."FruitType" IN ('Apple', 'Banana')
)
SELECT
    "ExpirationDate",
    "LastCheckedDate",
    "CUSTOMERID",
    "BetweenTheRange"
FROM RankedRecords
-- 保留每个客户每种水果下最接近过期日的唯一记录
WHERE RecordRank = 1

关键修正点说明 #

1. WHERE子句修正：将错误的OR 'Banana'替换为IN ('Apple', 'Banana')，既解决语法错误，也让筛选逻辑更简洁清晰。
2. 同日记录标记修正：BETWEEN运算符本身包含区间两端日期，只要LastCheckedDate与ExpirationDate为同一天，必然满足区间条件，CASE逻辑会正确标记为1。若原数据存在日期类型不一致问题，需确保两个字段均为标准日期类型。
3. 去重与筛选最接近记录：使用CTE结合窗口函数ROW_NUMBER()，按客户ID和水果类型分组，通过日期差绝对值排序，取每组排名第一的记录，确保每个客户下的苹果、香蕉各只保留一条最接近过期日的记录，避免重复统计。

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