查询history表中截至最后日期的连续NULL值起始日期

数据表结构与数据插入 #

create table history(response_date date, r_value bit);

insert into history values('2023-03-18',1),('2023-03-19',NULL),
('2023-03-20',NULL),('2023-03-21',1),('2023-03-22',NULL),
('2023-03-23',0),('2023-03-24',0),('2023-03-25',NULL),
('2023-03-26',NULL),('2023-03-27',NULL),('2023-03-28',NULL);

需求 #

从history表中找出截至表内最后日期的连续r_value为NULL的记录的起始日期，预期结果为2023-03-25。

解决方案 #

方法一：基于最后非NULL日期的查询 #

逻辑直接，先定位最后一条非NULL值的记录日期，之后的所有日期都是连续的NULL，取其中最小的日期即为目标起始日期：

SELECT MIN(response_date) AS start_date
FROM history
WHERE response_date > (
    SELECT MAX(response_date)
    FROM history
    WHERE r_value IS NOT NULL
);

方法二：窗口函数分组法 #

通过窗口函数将连续的NULL值归为同一分组，再找到包含最后日期的分组，取该分组的最小日期：

WITH ranked_dates AS (
    SELECT 
        response_date,
        r_value,
        SUM(CASE WHEN r_value IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY response_date) AS group_id
    FROM history
),
last_group_info AS (
    SELECT group_id
    FROM ranked_dates
    WHERE response_date = (SELECT MAX(response_date) FROM history)
)
SELECT MIN(response_date) AS start_date
FROM ranked_dates
WHERE group_id = (SELECT group_id FROM last_group_info);

执行结果 #

2023-03-25

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