添加具有上一日期(不等于当前日期)的列[SQL，PrestoDB]

Question

我有一个这样的表：

current_date	user_id
2021-10-1	1
2021-10-02	1
2021-10-02	1
2021-10-09	1
2021-10-15	1
2021-10-01	2
2021-10-01	2
2021-10-04	2
2021-10-04	2
2021-10-04	2
2021-10-11	2
2021-10-11	2

使用连接和LAG函数，我们可以尝试：

WITH cte AS (
SELECT *, LAG(current_date) OVER
(PARTITION BY user_id ORDER BY current_date) previous_date
FROM (SELECT DISTINCT current_date, user_id FROM yourTable) t
)
SELECT t1.current_date, t1.user_id, t2.previous_date
FROM yourTable t1
INNER JOIN cte t2
ON t2.user_id = t1.user_id AND t2.current_date = t1.current_date
ORDER BY t1.user_id, t1.current_date;

这是SQL Server中的一个演示，不过同样的查询应该在Presto中运行，只需最少的修改。