我有下表:
CREATE TABLE IF NOT EXISTS test (
date DATE,
value INT
);
INSERT INTO test VALUES
( '2022-01-11', NULL), -- Tues
( '2022-01-10', 5), -- Mon
( '2022-01-09', NULL), -- Sun
( '2022-01-08', 6), -- Sat
( '2022-01-07', NULL), -- Fri
( '2022-01-06', NULL), -- Thur
( '2022-01-05', 5), -- Wed
( '2022-01-04', NULL), -- Tues
( '2022-01-03', NULL), --Mon
( '2022-01-02', NULL), -- Sun
( '2022-01-01', NULL); -- Sat
转发和回填日历周(星期一到星期日)的值列的查询是什么
对于日历周,应将填充空值转发到下一个非空值,并回填空值直到上一个非空值.如果日历周的值都为空,则周应为空
结果应该如下所示:
date | VALUES |
---|---|
'2021-01-11' | 5 -- Tues, filled from Monday |
'2022-01-10' | 5 -- Mon |
'2022-01-09' | 6 -- Sun Filled from saturday |
'2022-01-08' | 6 -- Sat |
'2022-01-07' | 5 -- Fri Filled from Wed |
'2022-01-06' | 5 -- Thur Filled from Wed |
'2022-01-05' | 5 -- Wed |
'2022-01-04' | 5 -- Tues Filled from Wed |
'2022-01-03' | 5 --Mon Filled from Wed |
'2022-01-02' | Null -- Sun |
'2022-01-01' | Null -- Sat No values in week, therefor null |