SQL - LAG 等效使用 OVER 子句来检索以前的值并存储在当前行中？

Question

我有一个日期表，我需要在其中存储前一行的 val当前行中的列。但是，我不能使用 LAG子句，因为存在缺失的日期，并且只有在前一个直接日期存在时才应提取前一个值并将其存储在当前行中。因此我必须(我认为)使用 OVER()和 RANGE条款
例如，想象下表:

date      | val
-----------------
12/1/2020 | 1
12/2/2020 | 2
12/4/2020 | 3
12/5/2020 | 4

我希望能够使用前一天的值添加一个附加列，如果前一天不存在，则存储一个 0

date      | val | prev_val
--------------------------
12/1/2020 | 1.  | 0
12/2/2020 | 2.  | 1
12/4/2020 | 3.  | 0 <--- notice how 12/3/2020 doesn't exist? that's why we store a 0
12/5/2020 | 4.  | 3

填充 prev_val ，我想我可以在 SELECT 中执行以下操作陈述: SUM(val) OVER(ORDER BY UNIX_DATE(date) RANGE BETWEEN 1 PRECEDING AND NOT CURRENT ROW) as prev_val显然 NOT不存在，但这是我必须追求的概念，对吗？我不知道用于获取单个先前值的正确语法，我不能使用 ROW因为那不会考虑丢失的日期。我错过了什么？我正在使用 Google BigQuery。

Answer 1

您可以使用 LAG连同 CASE表达式有条件地呈现前一天的前一个值，或者在这一天不存在的情况下呈现零:

SELECT
    date,
    val,
    CASE WHEN DATE_SUB(date, INTERVAL 1 DAY) = LAG(date) OVER (ORDER BY date)
         THEN LAG(val) OVER (ORDER BY date)
         ELSE 0 END AS prev_val
FROM yourTable
ORDER BY
    date;