SQL延迟函数避免第一行为空

Question

我有一个sql查询

SELECT VALUE, LAG(VALUE,1,null) over(order by LOAD_DATE asc) as PREV_VALUE 
FROM TABLE LOAD_DATE >some_date
LOAD_DATE | VALUE
20210625    25
20210626    27
20210627    null
20210628    29

如果我的过滤器日期是>20210625，我得到的值为27，前一个值为null，是否有可能得到prev_value 25。如果没有过滤器，我将得到它。但是需要过滤器来防止读取整个表。还有一个要求。如果前一个值为空，例如date - 20210628，我应该得到前一个非空值27。

Answer 1

一个选项是计算表中的最后一个已知值，并将其用作LAG中的默认值:

DECLARE @Test TABLE (LOAD_DATE DATE, [VALUE] INT);
INSERT INTO @Test (LOAD_DATE, [VALUE])
VALUES
('2021-06-25', 25)
,   ('2021-06-26', 27)
,   ('2021-06-27', null)
,   ('2021-06-28', 29)
;
DECLARE @LoadDate DATE = '2021-06-24';
WITH LastValue AS (
SELECT
MAX([VALUE]) AS [VALUE] /* This is to make sure, there is at least one record returned (e.g. the date is earlier than the first record in the table. */
FROM
(
SELECT TOP(1)
[VALUE]
FROM
@Test
WHERE
LOAD_DATE <= @LoadDate
ORDER BY
LOAD_DATE DESC
) X
)
SELECT
T.*
,   LAG(T.VALUE, 1, LV.[VALUE]) OVER (ORDER BY T.LOAD_DATE) AS PREV_VALUE
FROM
@Test T
CROSS JOIN LastValue LV
WHERE
T.LOAD_DATE > @LoadDate
;

Answer 2

您可以使用子查询:

SELECT t.*
FROM (SELECT t.*, LAG(VALUE) OVER (ORDER BY LOAD_DATE) as PREV_VALUE
FROM TABLE t
) t
WHERE t.LOAD_DATE > some_date;

注意:这修复了语法(您通过省略OVER子句而过度简化了语法)。并删除1, NULL，因为这些是这些参数的默认值。

Answer 3

你可以尝试用两种方式修改WHERE子句:

1. 为日期比较添加等效性，即'>='而不是'>'
2. 在条件
中添加WHERE[VALUE] IS NOT NULL