SQL - 查询下一个日期

Question

我有一个表格，其中包含许多与每个ID关联的日期，甚至有一些没有日期的ID。 对于每个ID和日期组合，我想选择该ID、日期以及与该ID关联的下一个最大日期（如果存在），或者如果不存在，则将下一个日期选择为null。

示例表格：

ID      Date
1       5/1/10
1       6/1/10
1       7/1/10
2       6/15/10
3       8/15/10
3       8/15/10
4       4/1/10
4       4/15/10
4       

期望的输出：

ID       Date       Next_Date
1        5/1/10     6/1/10
1        6/1/10     7/1/10
1        7/1/10     
2        6/15/10    
3        8/15/10    
3        8/15/10    
4        4/1/10     4/15/10
4        4/15/10    

- John

Answer 1

SELECT
    mytable.id,
    mytable.date,
    (
        SELECT
            MIN(mytablemin.date)
        FROM mytable AS mytablemin
        WHERE mytablemin.date > mytable.date
            AND mytable.id = mytablemin.id
    ) AS NextDate
FROM mytable

这在 SQL Server 2008 R2 上进行了测试（但应该也适用于其他 DBMS），并生成以下输出：

id          date                    NextDate
----------- ----------------------- -----------------------
1           2010-05-01 00:00:00.000 2010-06-01 00:00:00.000
1           2010-06-01 00:00:00.000 2010-06-15 00:00:00.000
1           2010-07-01 00:00:00.000 2010-08-15 00:00:00.000
2           2010-06-15 00:00:00.000 2010-07-01 00:00:00.000
3           2010-08-15 00:00:00.000 NULL
3           2010-08-15 00:00:00.000 NULL
4           2010-04-01 00:00:00.000 2010-04-15 00:00:00.000
4           2010-04-15 00:00:00.000 2010-05-01 00:00:00.000
4           NULL                    NULL

更新 1： 对于那些感兴趣的人，我已经比较了两个变体在 SQL Server 2008 R2 中的性能（一个使用 MIN 聚合，另一个使用带 ORDER BY 的 TOP 1）：

在日期列没有索引的情况下，MIN 版本的成本为 0.0187916，而 TOP/ORDER BY 版本的成本为 0.115073，因此 MIN 版本“更好”。

在日期列上有索引的情况下，它们表现相同。

请注意，这只是针对这 9 条记录进行测试，因此结果可能（非常）虚假...

更新 2： 这些结果适用于 10,000 条均匀分布的随机记录。当记录数达到 100,000 条时，TOP/ORDER BY 查询运行时间过长，我不得不取消并放弃。

- Daniel Renshaw

---

最好使用排序函数而不是聚合函数。特别是当你有一个大表的时候。 - Андрей Костенко

@Andrii：我不知道其他DB是怎样的，但在SQL Server上应该没有区别。如果有索引，它很聪明地知道可以只读取第一行；如果没有索引，则必须无论如何扫描整个表。实际上，使用ORDER BY可能会更慢，因为它必须执行O（n * lg（n））排序，而不是O（n）扫描。 - Daniel Renshaw

这是一个使用访问传递查询的mssql数据库，这意味着LIMIT查询无论如何都不会起作用。通过在WHERE语句中稍微添加mytable.id = mytablemin.id，上面的最小化查询已经完美地运行。查询有点缓慢，但我正在使用的日期字段上没有索引。感谢大家的帮助。 - John

Answer 2

如果您的数据库是Oracle，可以使用lead()和lag()函数。

SELECT id, date, 
LEAD(date, 1, 0) OVER (PARTITION BY ID ORDER BY Date DESC NULLS LAST) NEXT_DATE,
FROM Your_table
ORDER BY ID;

- Bharat

---

以上代码对我来说产生了一个错误 - 0 应该是 null，因为 Oracle SQL 抱怨它是不兼容的数据类型（数字而不是日期）。下面的代码可以正常工作：SELECT id, date, LEAD(date, 1, null) OVER (PARTITION BY ID ORDER BY Date DESC NULLS LAST) NEXT_DATE, FROM Your_table。 - bawpie

Answer 3

SELECT id, date, ( SELECT date FROM table t1 WHERE t1.date > t2.date ORDER BY t1.date LIMIT 1 ) FROM table t2

选择 id, 日期, ( SELECT date FROM table t1 WHERE t1.date > t2.date ORDER BY t1.date LIMIT 1 ) 从表格 t2 中

- Андрей Костенко

Answer 4

我认为自连接JOIN比子查询更快。

WITH dates AS (
    SELECT 1 AS ID, '2010-05-01' AS Date
    UNION ALL SELECT 1, '2010-06-01'
    UNION ALL SELECT 1, '2010-07-01'
    UNION ALL SELECT 2, '2010-06-15'
    UNION ALL SELECT 3, '2010-08-15'
    UNION ALL SELECT 3, '2010-08-15'
    UNION ALL SELECT 4, '2010-04-01'
    UNION ALL SELECT 4, '2010-04-15'
    UNION ALL SELECT 4, ''
 )
 
 SELECT
    dates.ID,
    dates.Date,
    nextDates.Date AS Next_Date
 FROM
    dates
 LEFT JOIN
    dates nextDates
    ON nextDates.ID = dates.ID
    AND nextDates.Date > dates.Date
 LEFT JOIN
    dates noLower
    ON noLower.ID = nextDates.ID
    AND noLower.Date < nextDates.Date
    AND noLower.Date > dates.Date
 WHERE
    dates.Date > 0
    AND noLower.ID IS NULL

https://www.db-fiddle.com/f/4sWRLt2hxjik5HqiJ21ez8/1

- wiktor