在日期列和位数据类型列中查找从日期开始的连续空值

Question

create table history(response_date date, r_value bit);
insert into 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);

我只需要连续空值的开始记录日期，直到表中的日期。

预期结果记录日期:

2023-03-25

Answer 1

查找从日期列开始的连续空值:

您可以使用ROW_NUMBER() OVER PARTITION BYr_value，如本演示所示。

SELECT MIN(response_date) AS start_date
FROM (
SELECT response_date, r_value, ROW_NUMBER() OVER (ORDER BY response_date) AS row_num,
ROW_NUMBER() OVER (PARTITION BY r_value ORDER BY response_date) AS group_num
FROM history
) t
WHERE r_value IS NULL
GROUP BY (row_num - group_num)
HAVING COUNT(*) >= 2;

输出:

tbody> <<tr>

start_date
2022-03-25

with cte as (  
SELECT 
response_date, 
r_value, 
LEAD(r_value) OVER (ORDER BY response_date) AS lead,
LAG(r_value) OVER (ORDER BY response_date) AS lag
FROM history
) 
select * 
from cte
where r_value is null
and lead is null
and lag is not null;