如何在SQL Server中按客户分区获取首条无订单记录？

我在SQL Server数据库中有以下数据，想要为每个customer_id返回该客户首次无订单（order_count=0）日期对应的row_id；如果客户没有无订单的记录，返回null。

原始数据 #

期望结果 #

我考虑用按customer_id分区的窗口函数，但不确定具体逻辑，请问对应的SQL语句该怎么写？

可以通过窗口函数筛选每个客户最早的无订单记录，再结合LEFT JOIN确保所有客户都被返回，具体SQL如下：

WITH customer_first_zero AS (
    SELECT 
        customer_id,
        row_id,
        -- 按客户分区，给order_count=0的记录按日期升序排序，最早的标记为1
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY date ASC
        ) AS rn
    FROM your_table_name
    WHERE order_count = 0
)
SELECT 
    c.customer_id,
    cfz.row_id
FROM (
    -- 获取所有唯一客户ID，确保无无订单记录的客户也能被返回
    SELECT DISTINCT customer_id FROM your_table_name
) c
LEFT JOIN customer_first_zero cfz 
    ON c.customer_id = cfz.customer_id 
    AND cfz.rn = 1; -- 只保留每个客户最早的那条无订单记录

逻辑说明 #

1. CTE部分：先筛选出所有order_count=0的记录，用ROW_NUMBER()按客户分区、日期升序排序，这样每个客户的第一条无订单记录会被标记为rn=1。
2. 主查询：先提取所有唯一的客户ID，再通过LEFT JOIN关联CTE中rn=1的记录，确保没有无订单记录的客户返回null。

如果你的数据库中有独立的客户表（比如customers表），可以直接用该表代替主查询中的SELECT DISTINCT customer_id FROM your_table_name，查询效率会更高：

WITH customer_first_zero AS (
    SELECT 
        customer_id,
        row_id,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY date ASC
        ) AS rn
    FROM your_table_name
    WHERE order_count = 0
)
SELECT 
    c.customer_id,
    cfz.row_id
FROM customers c
LEFT JOIN customer_first_zero cfz 
    ON c.customer_id = cfz.customer_id 
    AND cfz.rn = 1;

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