如何用SQL的lag函数按日期组获取上一日期（无需子查询/连接）

原始数据 #

期望结果 #

尝试的SQL及错误结果 #

尝试使用以下SQL：

select ID, Date, lag(Date) over (partition by Date order by Date)
from Table;

得到错误结果，仅显示前一行的Date值，而非上一日期组的日期：

补充异常场景 #

当2023-10-05的ID为1-4，2023-10-10的ID为1-6时，按ID分区的方式仅对前后日期都存在的ID有效，新增的ID（5、6）无法正确获取上一日期，得到如下结果：

解决方案 #

可以通过嵌套窗口函数实现，无需子查询或连接：

SELECT
  ID,
  Date,
  MIN(LAG(Date) OVER (ORDER BY Date)) OVER (PARTITION BY Date) AS 上一日期
FROM your_table
ORDER BY Date, ID;

原理说明 #

1. LAG(Date) OVER (ORDER BY Date)：按日期排序后，取每一行的前一行日期值。同一日期组的第一行的前一行属于上一日期组，因此得到上一日期；同一日期组的后续行前一行是当前组的行，因此得到当前日期。
2. MIN(...) OVER (PARTITION BY Date)：在同一日期组内取最小的LAG值（即更早的上一日期），将该值填充到当前日期组的所有行中，包括新增的ID。

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