SQL:获取每行的下一个日期值

Question

我有一个包含日期列的表格。

ID  |      Date 
----|-----------
  1 | 2000-01-01
  2 | 2000-02-01
  3 | 2000-02-01
  4 | 2000-03-01

我需要一个为每一行、ID、日期和大于当前日期的最小日期(表中所有日期)返回的选择。

ID  |      Date  |  Next date
----+------------+------------
  1 | 2000-01-01 | 2000-02-01
  2 | 2000-02-01 | 2000-03-01
  3 | 2000-02-01 | 2000-03-01
  4 | 2000-03-01 |     (NULL)

我的第一个方法是

SELECT id, date, LEAD (date, 1) OVER (ORDER BY date NULLS LAST) AS next_date
  FROM t

但这仅适用于 DATE 列中的值是唯一的。

有任何想法吗？

Answer 1

您可以使用带有 windowing clause 的解析函数. lead()不支持窗口子句，因此您需要使用类似 min() 的子句。或 first_value() :

FIRST_VALUE ("Date")
  OVER (ORDER BY "Date" RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

默认的窗口子句是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，这将使您的所有行具有相同的值 2000-01-01并使用 ROWS window 会遇到与您相同的问题 lead()具有重复日期(ID 2 仍会得到 2000-02-01 ；如果您使用 2000-03-01 而不是 ROWS BETWEEN CURRENT ROW... ，ID 4 会得到 1 FOLLOWING 而不是 null )。

使用此范围的演示:

with t (ID, "Date") as (
  select 1, date '2000-01-01' from dual
  union all select 2, date '2000-02-01' from dual
  union all select 3, date '2000-02-01' from dual
  union all select 4, date '2000-03-01' from dual
)
select id, "Date", FIRST_VALUE ("Date") OVER (ORDER BY "Date"
  RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_date
FROM t;

        ID Date       NEXT_DATE 
---------- ---------- ----------
         1 2000-01-01 2000-02-01
         2 2000-02-01 2000-03-01
         3 2000-02-01 2000-03-01
         4 2000-03-01           

仅考虑日期值高于当前行的行。而这仍然只需要上 table 一次。

(我将 "Date" 放在双引号中，因为 date 是一个保留字；从您的示例数据来看，它看起来像一个带引号的标识符，但它没有在您的查询中被引用，所以它可能只是变得更明智了真名……)