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
查找从日期列开始的连续空值:
您可以使用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;
输出:
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;