针对日期的 SQL LAG 函数

Question

我有下表（示例）：

+----+-------+-------------+----------------+
| id | value | last_update | ingestion_date |
+----+-------+-------------+----------------+
| 1  | 30    | 2021-02-03  | 2021-02-07     |
+----+-------+-------------+----------------+
| 1  | 29    | 2021-02-03  | 2021-02-06     |
+----+-------+-------------+----------------+
| 1  | 28    | 2021-01-25  | 2021-02-02     |
+----+-------+-------------+----------------+
| 1  | 25    | 2021-01-25  | 2021-02-01     |
+----+-------+-------------+----------------+
| 1  | 23    | 2021-01-20  | 2021-01-31     |
+----+-------+-------------+----------------+
| 1  | 20    | 2021-01-20  | 2021-01-30     |
+----+-------+-------------+----------------+
| 2  | 55    | 2021-02-03  | 2021-02-06     |
+----+-------+-------------+----------------+
| 2  | 50    | 2021-01-25  | 2021-02-02     |
+----+-------+-------------+----------------+

我需要的结果： 它应该是列值中的最后更新值和值2中的倒数值（基于last_update和ingestion_date）。

+----+-------+-------------+----------------+--------+
| id | value | last_update | ingestion_date | value2 |
+----+-------+-------------+----------------+--------+
| 1  | 30    | 2021-02-03  | 2021-02-07     | 28     |
+----+-------+-------------+----------------+--------+
| 2  | 55    | 2021-02-03  | 2021-02-06     | 50     |
+----+-------+-------------+----------------+--------+

我现在的查询如下：

SELECT id, value, last_update, ingestion_date, value2
FROM
(SELECT *,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY last_update DESC, ingestion_date DESC) AS order,
        LAG(value) OVER(PARTITION BY id ORDER BY last_update, ingestion_date) AS value2
    FROM table)
WHERE ordem = 1

我得到的结果：

+----+-------+-------------+----------------+--------+
| ID | value | last_update | ingestion_date | value2 |
+----+-------+-------------+----------------+--------+
| 1  | 30    | 2021-02-03  | 2021-02-07     | 29     |
+----+-------+-------------+----------------+--------+
| 2  | 55    | 2021-02-03  | 2021-02-06     | 50     |
+----+-------+-------------+----------------+--------+

Obs: I am using Athena from AWS