如何获取后续行中首个不同的delivery_at值作为next_delivery_at

原始数据表如下：

需求为新增next_delivery_at字段，该字段是当前行delivery_at对应的后续行中首个与当前值不同的delivery_at值，期望结果表如下：

针对上述特定场景，有人给出了以下SQL语句：

CASE
      WHEN (LEAD(delivery_at) OVER (PARTITION BY NULL ORDER BY delivery_at DESC) = delivery_at)
      THEN (LEAD(delivery_at, 2) OVER (PARTITION BY NULL ORDER BY delivery_at DESC))
      ELSE LEAD(delivery_at) OVER (PARTITION BY NULL ORDER BY delivery_at DESC)
  END AS next_delivery_at

但该语句仅适用于连续两行相同delivery_at的情况，当存在连续多行相同值时会失效，现需要一种通用方法来获取后续行中首个不同的delivery_at值。

可以通过分组标记+窗口函数的方式实现，具体步骤如下：

1. 先对连续相同的delivery_at进行分组标记，使用LAG函数判断当前行与上一行的delivery_at是否相同，生成分组ID：

SELECT 
    *,
    SUM(CASE WHEN LAG(delivery_at) OVER (ORDER BY order_at) = delivery_at THEN 0 ELSE 1 END) 
        OVER (ORDER BY order_at) AS group_id
FROM your_table

这一步会把连续相同delivery_at的行归为同一组。

2. 基于分组ID，获取每个分组对应的下一个分组的delivery_at值：

WITH grouped_data AS (
    SELECT 
        *,
        SUM(CASE WHEN LAG(delivery_at) OVER (ORDER BY order_at) = delivery_at THEN 0 ELSE 1 END) 
            OVER (ORDER BY order_at) AS group_id
    FROM your_table
),
next_delivery AS (
    SELECT 
        group_id,
        LEAD(delivery_at) OVER (ORDER BY group_id) AS next_delivery_val
    FROM (
        SELECT DISTINCT group_id, delivery_at 
        FROM grouped_data 
        ORDER BY group_id
    ) AS distinct_groups
)
SELECT 
    gd.order_at,
    gd.delivery_at,
    nd.next_delivery_val AS next_delivery_at
FROM grouped_data gd
LEFT JOIN next_delivery nd ON gd.group_id = nd.group_id
ORDER BY gd.order_at;

方案说明 #

• 第一步通过SUM和LAG组合生成分组ID，确保连续相同的delivery_at属于同一个分组；
• 第二步先提取每个分组的唯一delivery_at值，再用LEAD获取每个分组的下一个分组的delivery_at；
• 最后将分组数据与下一个分组的delivery_at关联，得到最终结果，该方法无论连续相同值的行数多少都能生效。

内容的提问来源于stack exchange，提问作者Javier Lopez Tomas