sql - 如果我们在有序集中有重复值，如何解决 LEAD 或 LAG 分析函数中的关系？

Question

我有一个表，记录如下:

VERSION_ID    START_DATE
Vrandom1      2018-03-22
Vrandom2      2018-03-22

当我运行 SQL 时:

SELECT T.VERSION_ID VERSION_ID, 
START_DATE,
LEAD  (START_DATE) OVER (ORDER BY START_DATE) LEAD_START_DATE
FROM TABLE T

我得到:

VERSION_ID    START_DATE                   LEAD_START_DATE
Vrandom1      2018-03-22                   2018-03-22
Vrandom2      2018-03-22                   NULL  

获得正确主值的正确 SQL 是什么？

我希望两条记录的 LEAD_START_DATE 都为空，或者两条记录的 LEAD_START_DATE 值相同。谢谢

Answer 1

你的期望是错误的。 SQL 中的 ORDER BY 不稳定 这意味着当键具有相同的值时，排序是任意的。即使在同一个查询中两次调用 ORDER BY 也会产生不同的结果——这就是您所看到的。

“正确”的版本是期望不同的结果，并通过引入新 key 使排序稳定:

SELECT T.VERSION_ID VERSION_ID, START_DATE,
       LEAD(START_DATE) OVER (ORDER BY START_DATE, VERSION_ID) as LEAD_START_DATE
FROM TABLE T;

但这并不能解决您的问题。

顺便说一句，想到的是尝试不同的线索，但这会让人感到困惑，因为 NULL 通常表示最后一个值。相反，我会改变您的期望并引入稳定的排序。