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

我有一个如下所示的表
| 计时器|组群|子组|计数器|
| - -|- -|- -|- -|
| 2022年1月1日|A级|正确|三个|
| 2022年1月1日|A级|错误|一个|
| 2022年1月1日|B|正确|2个|
| 2022年1月1日|B|错误|一个|
| 2022年1月2日|A级|错误|2个|
| 2022年1月2日|A级|正确|五个|
| 2022年1月2日|B|错误|三个|
| 2022年1月3日|A级|错误|三个|
| 2022年1月3日|B|错误|四个|
| 2022年1月3日|B|正确|三个|
因此，每天每组+子组的计数递增，除非某一天组+子组的计数没有变化，否则该行将丢失。
在上面的例子中，丢失的行将是：...
|2022年1月2日| B|真的|2|
...
|2022年1月3日|A|真的|五|
...
为了便于数据处理，我需要所有组+子组每天的连续时间戳。
| 计时器|组群|子组|计数器|
| - -|- -|- -|- -|
| 2022年1月1日|A级|正确|三个|
| 2022年1月1日|A级|错误|一个|
|2022年1月1日| B|真的|2|
| 2022年1月1日|B|错误|一个|
| 2022年1月2日|A级|错误|2个|
|2022年1月2日|A|真的|五|
| 2022年1月2日|B|错误|三个|
|2022年1月2日| B|真的|2|
| 2022年1月3日|A级|错误|三个|
|2022年1月3日|A|真的|五|
| 2022年1月3日|B|错误|四个|
| 2022年1月3日|B|正确|三个|
我该如何实现呢？可能是一些parition by... over选择结构，但在这种情况下，我无法理解如何根据其他组的时间戳进行分区，因为我没有NULL计数来作为中间值转发填充每个组。
更新：到目前为止，我似乎已经达到了中间状态，填补了缺失的时间戳（基本上只是每天的频率在这里是好的）之间的组，像这样：

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
    )

好吧，看起来我很接近了，橡皮鸭调试帮助了我。
这似乎做什么我想有：

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;

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

1条答案

相关问题

热门标签

最新问答