在postgresql中使用其他组的时间戳向前填充分组时间序列

Question

我有一个表，看起来像这样

<表类> 时间 组 sub_group 数 tbody><<tr>2022-01-01对32022-01-01假12022-01-01B对22022-01-01B假12022-01-02假22022-01-02对52022-01-02B假32022-01-03假32022-01-03B假42022-01-03B对3

好吧，看来我已经很接近了，橡皮鸭调试帮了大忙。

这似乎做了我想要的:

WITH time_range AS (
SELECT MIN(time) AS start_time, -- current_timestamp - interval '2 day'
MAX(time) AS end_time
FROM my_table-- current_timestamp
),
interested_events AS (
SELECT e.group, e.sub_group, e.time, e.count
FROM my_table e
),
classes_having_events AS (
SELECT DISTINCT
GROUP, sub_group
FROM interested_events
ORDER BY
GROUP, sub_group
),
periods AS (
SELECT ts AS period_start, ts + INTERVAL '1 day' AS period_end
FROM GENERATE_SERIES(
(
SELECT start_time
FROM time_range
),
(
SELECT end_time
FROM time_range
) - INTERVAL '1 second',
INTERVAL '1 day') ts
),
resampled AS (
SELECT period_start,
period_end,
classes_having_events.group,
classes_having_events.sub_group,
interested_events.count
FROM periods
CROSS JOIN classes_having_events
LEFT JOIN interested_events
ON time >= period_start AND time < period_end
AND interested_events.group = classes_having_events.group
AND interested_events.sub_group = classes_having_events.sub_group
ORDER BY period_start DESC
)
SELECT period_start                                                              AS time,
"group",
sub_group,
MAX(count) OVER (PARTITION BY "group", "sub_group" ORDER BY period_start) AS count
FROM resampled
ORDER BY period_start DESC, "group", sub_group;