PostgreSQL TimescaleDB按时间分桶并补全多src-dest组缺失数据

数据库表结构 #

现有一张大型TimescaleDB表，结构及示例数据如下：

查询要求 #

需要实现以下查询逻辑：

• 对指定的src-dest对的traffic字段求和，支持查询单个对（如a->b）或多个对（如a->b和a->c），每个src-dest对视为唯一（a->b与b->a是不同的对）
• 将数据按指定时间范围（例如2022-12-11 00:25:00.000至2022-12-11 19:35:00.000）分成24个等宽时间桶（例如50分钟/桶），结果必须满足：
  1. 每个src-dest对应的所有24个时间桶都要出现在结果中（Timescale原生的time_bucket函数无法满足此要求）
  2. 第一个时间桶的起始时间必须是指定的查询起始时间（Timescale原生的time_bucket_gapfill函数无法满足此要求）
  3. 查询语句需要支持多src-dest对的筛选，例如使用WHERE ((src = 'a' and dest = 'b') or (src = 'a' and dest = 'c'))

示例输出（与上述示例输入无关） #

以a->b对为例，查询结果需包含24个起始于00:25:00的时间桶，若某段时间无流量数据，对应桶的traffic值为NULL：

内容的提问来源于stack exchange，提问作者zerohedge