SQL如何按CustomerID分区删除FirstOrder标记前的数据行

需求为按CUSTOMERID分区，删除每个客户首单（即FIRSTORDER=1对应的ASOFDATE日期）之前的所有数据行。
测试样例表结构与数据如下：

CREATE TABLE #DATA (ASOFDATE DATE, CUSTOMERID INT, ACTIVE BIT, FIRSTORDER BIT)
INSERT INTO #DATA VALUES
('2018-01-31',  206424, NULL,   NULL),
('2018-02-28',  206424, NULL,   NULL),
('2018-03-31',  206424, 1,      1),
('2022-06-30',  206424, NULL,   NULL),
('2022-07-31',  206424, NULL,   NULL),
('2018-06-30',  247034, NULL,   NULL),
('2018-07-31',  247034, NULL,   NULL),
('2018-08-31',  247034, 1,  1),
('2022-05-31',  247034, NULL,   NULL)

不需要额外处理NULL值填充，先聚合出每个客户对应的首单日期作为基准，再关联原表删除早于基准日期的行即可，逻辑简单且执行效率高。

删除语句 #

WITH CustomerFirstOrder AS (
    SELECT
        CUSTOMERID,
        MIN(ASOFDATE) AS FirstOrderDate
    FROM #DATA
    WHERE FIRSTORDER = 1
    GROUP BY CUSTOMERID
)
DELETE d
FROM #DATA d
JOIN CustomerFirstOrder fo
    ON d.CUSTOMERID = fo.CUSTOMERID
WHERE d.ASOFDATE < fo.FirstOrderDate

结果验证 #

执行完删除操作后，查询临时表可得到符合预期的结果：

SELECT * FROM #DATA ORDER BY CUSTOMERID, ASOFDATE

返回结果规则：

• 客户ID 206424：保留2018-03-31、2022-06-30、2022-07-31共3条记录，删除首单前2018年1月、2月的2条无效记录
• 客户ID 247034：保留2018-08-31、2022-05-31共2条记录，删除首单前2018年6月、7月的2条无效记录

之前尝试的计算列方案卡在NULL值处理，核心原因是把基准值计算和行筛选逻辑耦合在同一步，抬高了逻辑复杂度。把首单日期单独聚合为独立基准集之后，不需要给间隔位置的行填充任何值，只要按客户ID关联后做日期大小比较，所有早于首单日期的行不管字段是否为NULL，都会匹配到删除条件，逻辑更简洁也不容易出错。

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