如何基于TimeDate在动态范围中获取下一个最大ValueDate值？

我需要在截至当前行TimeDate的所有历史行中，找到比当前行ValueDate大的最小ValueDate（即下一个最大的ValueDate），无符合条件值时返回NULL。查询范围对应UNBOUNDED PRECEDING AND CURRENT ROW，按TimeDate排序，但不清楚如何用LAG/LEAD或FIRST/LAST VALUE实现。

数据样例与期望结果 #

关键说明 #

尽管ValueDate = '23.12.2021'是ValueDate = '19.12.2021'的下一个最大值，但由于它的TimeDate晚于当前行，此时尚未“存在”，不能纳入计算范围。

测试数据生成SQL #

CREATE VOLATILE TABLE TempTable (
    SomeIndex INTEGER,
    ValueDate DATE FORMAT 'YYYY-MM-DD',
    TimeDate DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX (SomeIndex)
ON COMMIT PRESERVE ROWS;

INSERT INTO TempTable VALUES(1,'2022-02-13','2023-09-10');
INSERT INTO TempTable VALUES(1,'2022-03-21','2023-09-11');
INSERT INTO TempTable VALUES(1,'2022-01-17','2023-09-12');
INSERT INTO TempTable VALUES(1,'2022-02-28','2023-09-13');
INSERT INTO TempTable VALUES(1,'2021-12-19','2023-09-14');
INSERT INTO TempTable VALUES(1,'2021-12-23','2023-09-15');

解决方案 #

常规窗口函数无法直接实现这种动态范围的“下一个最大值”查询，以下是两种可行方案：

方案一：关联子查询（通用SQL，兼容多数数据库） #

直接通过子查询筛选截至当前TimeDate的历史行，找到符合条件的最小ValueDate：

SELECT 
    t1.SomeIndex,
    t1.ValueDate,
    t1.TimeDate,
    (
        SELECT MIN(t2.ValueDate)
        FROM TempTable t2
        WHERE t2.SomeIndex = t1.SomeIndex
          AND t2.TimeDate <= t1.TimeDate
          AND t2.ValueDate > t1.ValueDate
    ) AS DesiredResult
FROM TempTable t1
ORDER BY t1.TimeDate;

方案二：Teradata专用LATERAL子查询（更高效） #

利用Teradata支持的LATERAL子查询，逻辑更直观，在有合适索引时性能更优：

SELECT 
    t.SomeIndex,
    t.ValueDate,
    t.TimeDate,
    lr.DesiredResult
FROM TempTable t
LEFT JOIN LATERAL (
    SELECT MIN(ValueDate) AS DesiredResult
    FROM TempTable
    WHERE SomeIndex = t.SomeIndex
      AND TimeDate <= t.TimeDate
      AND ValueDate > t.ValueDate
) lr ON 1=1
ORDER BY t.TimeDate;

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