使用Window Functions标记用户重复访问同一目的地的SQL实现

需求说明 #

现有行程级数据表，每行对应唯一行程。需要为每条行程添加布尔标识Same_Destination：

• 同一用户首次前往某目的地的行程，标记为0
• 该用户后续（包括非紧邻的再次前往）同一目的地的行程，标记为1

初始方案的问题 #

初始尝试使用LAG()函数仅对比分区内的前一条记录，代码如下：

CASE WHEN Destination = LAG(Destination) OVER (PARTITION BY Person) THEN 1 ELSE 0 END

这种方式只能识别紧邻的重复目的地，无法处理非连续的返回情况（比如示例中Tina第5次行程回到Arizona，初始代码会错误标记为0）。

正确解决方案（窗口函数实现） #

使用ROW_NUMBER()窗口函数，按「用户+目的地」分区，再按行程顺序排序。分区内的第一条记录即为首次前往，标记0；其余记录标记1。

完整SQL代码 #

SELECT
    Person,
    Trip,
    Destination,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY Person, Destination ORDER BY Trip) = 1 
        THEN 0 
        ELSE 1 
    END AS Same_Destination
FROM trips
ORDER BY Person, Trip;

数据示例 #

起始数据

期望输出

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