postgresql SQL：如何将结果划分为时段，并跨列组填充缺失数据？

我有一个非常大的PostgreSQL时间尺度数据库，它看起来大致如下：
| 源代码|目的地|交通|时间戳（类型：时间戳）|
| - ------|- ------|- ------|- ------|
| 项目a| B|二百|2022年12月11日00时23分51秒|
| 项目a| B|二百|2022年12月11日00时32分01秒|
| B|项目a|二百|2022年12月11日00时49分01秒|
| 项目a|（c）秘书长的报告|二百|2022年12月11日11时39分01秒|
| 项目a| B|二百|2022年12月11日11时57分01秒|
| 项目a| B|二十个|2022年12月11日21时32分01秒|
用户需要求src-dest对之间的traffic之和。例如，用户可能要求求a和b之间的traffic之和。或者，求几对a-〉b和a-〉c之间的traffic之和--这将使结果集更大。这是可以的：要聚集的对的数量是有限的、小的和受限的。2用户不会要求多于10对。
每个源-目的对都应该被认为是“唯一的”，这意味着a-b与b-a不同。
数据应分时段到两个时间戳中宽度相等的时段中。例如，从“2022-12-11 00：25：00.000”到“2022-12-11 19：35：00.000”的24个50 minute时段。
桶的数量由我决定，我取开始时间戳和结束时间戳，并将它们除以X个等宽的桶（在这个问题中，你可以假设它总是24）。
这就是我遇到问题的地方--几个必须达到的结果：
1.范围内的所有时段都必须出现在结果中。因此，对于上面的示例，a-〉b对的结果应该始终有24行，每行代表1个时段。（这就是Timescale的time_bucket功能失败的地方）
1.在我们的示例中，最早的时段行应该始终是起始时间戳：2022-12-11 00:25:00.000（这是time_bucket_gapfill功能失败的地方）。
查询的必备条件：
1.查询应支持多个对。例如：

WHERE ((src = 'a' and dest = 'b') or (src = 'a' and dest = 'c'))

输出示例（与输入示例无关）。
对于a和b对，从00:25:00开始的24个时间桶，其中通信在11:15和11:55之间的某个时间停止：
| 时段|源代码|目的地|交通|
| - ------|- ------|- ------|- ------|
| 2022年12月11日00时25分00秒000秒+0200秒|项目a| B|小行星48614|
| 2022年12月11日01时15分00秒00分+0200秒|项目a| B|小行星49|
| 2022年12月11日02时05分00秒000秒+0200秒|项目a| B|小行星49|
| 2022年12月11日02时55分00千+0200秒|项目a| B|小行星48614|
| 2022年12月11日03时45分00千+0200秒|项目a| B|小行星49|
| 2022年12月11日04时35分00千+0200秒|项目a| B|小行星49119|
| 2022年12月11日05时25分00秒00分+0200秒|项目a| B|小行星27288|
| 2022年12月11日06时15分00秒00分+0200秒|项目a| B|小行星26054|
| 2022年12月11日07时05分00秒00秒+0200秒|项目a| B|小行星25735|
| 2022年12月11日07时55分00千+0200秒|项目a| B|小行星2536|
| 2022年12月11日08时45分00千+0200秒|项目a| B|小行星26748|
| 2022年12月11日09时35分00秒00分+0200秒|项目a| B|小行星24787|
| 2022年12月11日10时25分00秒00秒+0200秒|项目a| B|小行星23065|
| 2022年12月11日11时15分00秒000秒+0200秒|项目a| B|小行星20629|
| 2022年12月11日11时55分00秒000秒+0200秒|项目a| B|零|
| 2022年12月11日12时45分00万+0200秒|项目a| B|零|
| - -|项目a| B|零|
| 2022年12月12日19时35分00秒|项目a| B|零|

鉴于以下情况：

时间段为50分钟
第一个时格开始于一天的00：25：00; 24号晚上20点25分
（src，dest）参数的列表

一种使用标准PostgreSQL而不扩展timescaledb的方法

cte_ts_params表示（start_ts，end_ts）参数
用于（src，dest）参数的cte_src_desc_params
cte_time_buckets，用于基于cte_ts_params的24个50分钟时段
cte_src_dest_time_buckets适用于所有组合（src、dest）x time bucket
将它们结合在一起以获得结果

with recursive cte_ts_params as (
select to_timestamp('2022-12-12 00:25:00','YYYY-MM-DD HH24:MI:SS') as start_ts,
       to_timestamp('2022-12-12 20:25:00','YYYY-MM-DD HH24:MI:SS') as end_ts),
cte_src_dest_params as (
select 'a' as src, 'b' as dest union all
select 'a', 'c'),
cte_time_buckets(bucket_ts, n) as (
select start_ts as bucket_ts, 1 as n
  from cte_ts_params
union all
select bucket_ts + interval '50 minutes', n+1
  from cte_time_buckets
 where n < 24),
cte_src_dest_time_buckets as (
select bucket_ts,
       src,
       dest
  from cte_time_buckets, cte_src_dest_params),
cte_src_dest_traffic as (
select date_bin('50 minutes', t.ts, (select start_ts from cte_ts_params)) as bucket_ts,
       t.src,
       t.dest,
       sum(t.traffic) as traffic
  from ts t
  join cte_src_dest_params s
    on t.src = s.src
   and t.dest = s.dest
 where t.ts >= (select start_ts from cte_ts_params) -- start of 1st time bucket
   and t.ts <  (select end_ts   from cte_ts_params) -- end of 24th time bucket
 group by bucket_ts, t.src, t.dest)
select t.bucket_ts,
       t.src,
       t.dest,
       coalesce(s.traffic,0) as traffic
  from cte_src_dest_time_buckets t
  left
  join cte_src_dest_traffic s
    on t.bucket_ts = s.bucket_ts
   and t.src = s.src
   and t.dest = s.dest
 order by t.src, t.dest, t.bucket_ts;

输出示例：

bucket_ts                    |src|dest|traffic|
-----------------------------+---+----+-------+
2022-12-12 00:25:00.000 +0200|a  |b   |   1545|
2022-12-12 01:15:00.000 +0200|a  |b   |   1102|
2022-12-12 02:05:00.000 +0200|a  |b   |   3637|
2022-12-12 02:55:00.000 +0200|a  |b   |   2060|
2022-12-12 03:45:00.000 +0200|a  |b   |   1610|
2022-12-12 04:35:00.000 +0200|a  |b   |   1150|
2022-12-12 05:25:00.000 +0200|a  |b   |   2312|
2022-12-12 06:15:00.000 +0200|a  |b   |   1382|
2022-12-12 07:05:00.000 +0200|a  |b   |   1165|
2022-12-12 07:55:00.000 +0200|a  |b   |     63|
2022-12-12 08:45:00.000 +0200|a  |b   |   2796|
2022-12-12 09:35:00.000 +0200|a  |b   |   2452|
2022-12-12 10:25:00.000 +0200|a  |b   |   1180|
2022-12-12 11:15:00.000 +0200|a  |b   |   1646|
2022-12-12 12:05:00.000 +0200|a  |b   |      0| *
2022-12-12 12:55:00.000 +0200|a  |b   |   2490|
2022-12-12 13:45:00.000 +0200|a  |b   |   1688|
2022-12-12 14:35:00.000 +0200|a  |b   |   1671|
2022-12-12 15:25:00.000 +0200|a  |b   |   3363|
2022-12-12 16:15:00.000 +0200|a  |b   |    772|
2022-12-12 17:05:00.000 +0200|a  |b   |   1019|
2022-12-12 17:55:00.000 +0200|a  |b   |   2183|
2022-12-12 18:45:00.000 +0200|a  |b   |    945|
2022-12-12 19:35:00.000 +0200|a  |b   |   2468|
2022-12-12 00:25:00.000 +0200|a  |c   |      0| *
2022-12-12 01:15:00.000 +0200|a  |c   |   4333|
2022-12-12 02:05:00.000 +0200|a  |c   |   4266|
2022-12-12 02:55:00.000 +0200|a  |c   |    375|
2022-12-12 03:45:00.000 +0200|a  |c   |    154|
2022-12-12 04:35:00.000 +0200|a  |c   |   1991|
2022-12-12 05:25:00.000 +0200|a  |c   |      0| *
2022-12-12 06:15:00.000 +0200|a  |c   |   1646|
2022-12-12 07:05:00.000 +0200|a  |c   |   6056|
2022-12-12 07:55:00.000 +0200|a  |c   |   2531|
2022-12-12 08:45:00.000 +0200|a  |c   |   2987|
2022-12-12 09:35:00.000 +0200|a  |c   |   2965|
2022-12-12 10:25:00.000 +0200|a  |c   |   4107|
2022-12-12 11:15:00.000 +0200|a  |c   |   1276|
2022-12-12 12:05:00.000 +0200|a  |c   |    362|
2022-12-12 12:55:00.000 +0200|a  |c   |    534|
2022-12-12 13:45:00.000 +0200|a  |c   |   1194|
2022-12-12 14:35:00.000 +0200|a  |c   |    579|
2022-12-12 15:25:00.000 +0200|a  |c   |    362|
2022-12-12 16:15:00.000 +0200|a  |c   |    833|
2022-12-12 17:05:00.000 +0200|a  |c   |    348|
2022-12-12 17:55:00.000 +0200|a  |c   |   2401|
2022-12-12 18:45:00.000 +0200|a  |c   |   1708|
2022-12-12 19:35:00.000 +0200|a  |c   |   3101|

更新根据@zerohedge评论中的后续问题

根据https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/，
对于不包括月或年间隔的时段，时段起始默认为2000年1月3日午夜;对于月、年和世纪时段，时段起始默认为2000年1月1日午夜。
我测试了timescales-latest-pg 14 docker图像，它具有

PostgreSQL语言14.5
扩展2.8.1

使用以下查询调用time_bucket()和time_bucket_gapfill()，并使用必需参数和可选参数：

select time_bucket('50 minutes', '2022-12-12 00:32:00'::timestamp) as bucket_ts_with_default_origin,
       time_bucket('50 minutes', '2022-12-12 00:32:00'::timestamp, '2022-12-12 00:25:00'::timestamp) as bucket_ts_with_custom_origin,
       time_bucket_gapfill('50 minutes', '2022-12-12 00:32:00'::timestamp) as bucket_gapfill_ts_with_default,
       time_bucket_gapfill('50 minutes', '2022-12-12 00:32:00'::timestamp, '2022-12-12 00:25:00'::timestamp, '2022-12-12 20:25:00'::timestamp) as bucket_gapfill_ts_with_custom_start_finish;

型
结果：

bucket_ts_with_default_origin|bucket_ts_with_custom_origin|bucket_gapfill_ts_with_default|bucket_gapfill_ts_with_custom_start_finish|
-----------------------------+----------------------------+------------------------------+------------------------------------------+
      2022-12-11 23:50:00.000|     2022-12-12 00:25:00.000|       2022-12-11 23:50:00.000|                   2022-12-11 23:50:00.000|

你可以看到

time_bucket()在给定自定义原点时有效
time_bucket_gapfill()不工作，即使给定自定义开始和结束

它是一个bug、一个特性还是遗漏了什么？

postgresql SQL：如何将结果划分为时段，并跨列组填充缺失数据？

1条答案

相关问题

热门标签

最新问答