为什么LAG函数最后一行无返回值？SQL窗口函数问题咨询

我理解LAG()函数可用于获取上一行的数据，因此基于上一行的status字段创建了survived和disenrolled新字段。2022-11月的status为1（非NULL），我预期2022-12月行的survived值为1、disenrolled值为0，但结果中这两个字段为NA。请问原因是什么？

原始表信息 #

查询语句：

select * from tmp_enrollment_long_2;

原始表数据：

使用的SQL语句 #

SELECT * 
,CAST( lag( status IS NOT NULL, 1 ) OVER( partition BY person_id ORDER BY month DESC ) AS SMALLINT ) AS survived
,CAST( lag( status IS     NULL, 1 ) OVER( partition BY person_id ORDER BY month DESC ) AS SMALLINT ) AS disenrolled
FROM tmp_enrollment_long_2;

查询结果 #

问题出在窗口函数的排序方向上：你使用了ORDER BY month DESC，这会让数据按月份从新到旧排列，2022-12月是排序结果的第一行，没有“前一行”（在排序后的结果集中），因此LAG()函数无法获取到任何值，返回NULL（即结果中的空值）。

而你预期的是获取时间上更早的上一个月的状态，这时候需要让数据按月份升序（ORDER BY month ASC）排列，这样2022-12月的前一行就是2022-11月，LAG()就能正确取到对应状态值。

修改窗口函数中的排序方向为升序，SQL语句调整如下：

SELECT * 
,CAST( lag( status IS NOT NULL, 1 ) OVER( partition BY person_id ORDER BY month ASC ) AS SMALLINT ) AS survived
,CAST( lag( status IS     NULL, 1 ) OVER( partition BY person_id ORDER BY month ASC ) AS SMALLINT ) AS disenrolled
FROM tmp_enrollment_long_2;

如果需要最终结果按月份降序展示，可以在外层添加排序：

SELECT *
FROM (
    SELECT * 
    ,CAST( lag( status IS NOT NULL, 1 ) OVER( partition BY person_id ORDER BY month ASC ) AS SMALLINT ) AS survived
    ,CAST( lag( status IS     NULL, 1 ) OVER( partition BY person_id ORDER BY month ASC ) AS SMALLINT ) AS disenrolled
    FROM tmp_enrollment_long_2
) t
ORDER BY month DESC;

调整后，2022-12月的survived会是1，disenrolled会是0，符合你的预期。

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