时间戳

首页 > 解决方案 > Postgres SQL 填补多个组的时间序列空白

sql - Postgres SQL 填补多个组的时间序列空白

问题描述

我有一个这样的表，它跨越 2 天的数据捕获：

   class   timestamp              count

   2       2020-05-20 03:10:00    4
   1       2020-05-21 07:45:00    2
   6       2020-05-20 09:20:00    1
   2       2020-05-21 11:30:00    1
   5       2020-05-21 21:50:00    3
   1       2020-05-21 07:45:00    5
   5       2020-05-20 14:55:00    2

我想创建类似于该模式的输出，但如果没有匹配的时间戳，则两天内每5 分钟间隔一次，计数归零。一个类的所有重复时间戳都需要对该时间戳条目进行总计。所有唯一的类都需要分组，并且顺序应该是时间戳升序。

每个独特的班级都需要以这种方式对待 - 逐个班级，按顺序填写缺失的 5 分钟时间，将该组中具有完全相同时间戳的所有计数相加，按时间戳排序。整体输出应按类升序排列。

感谢您的任何帮助。

标签： sqlpostgresql

解决方案

Postgres 使用left join. 以下每个时间戳有一行：

select gs.ts, coalesce(sum(t.count), 0)
from generate_series('2020-05-20 00:00:00'::timestamp, '2020-05-21 23:55:00'::timestamp, interval '5 minute') gs(ts) left join
     t
     on t.timestamp = gs.ts
group by gs.ts;

如果您希望按类对此进行分类（我真的无法根据问题的措辞说出您的意图），然后使用cross join生成行left join和聚合：

select c.class, gs.ts, coalesce(sum(t.count), 0)
from generate_series('2020-05-20 00:00:00'::timestamp, '2020-05-21 23:55:00'::timestamp, interval '5 minute') gs(ts) cross join
     (select distinct class from t) c left join
     t
     on t.timestamp = gs.ts
group by c.class, gs.ts;

我突然想到，您的数据中的时间戳可能不准确。如果是这种情况，请对 使用不等式join：

     on t.timestamp >= gs.ts and
        t.timestamp < gs.ts + interval '5 minute'

推荐阅读