在Postgres中以毫秒为单位的下采样时间戳

由 lsint发布于 2020-05-18 06:38:01

我有一张桌子，如下

timestamp                    v1    v2    v3
2020/01/01 08:10:20.300      10    20    30
2020/01/01 08:10:20.306      11    21    31
2020/01/01 08:10:20.310      12    22    32
2020/01/01 08:10:20.318      13    23    33
2020/01/01 08:10:20.325      14    24    34
.......................
2020/01/01 08:10:21.100      19    29    39

可以看出，时间戳记在毫秒内变化不均匀，并且具有大量的粒度数据。我有兴趣压缩表，以便时间戳和数据列每100毫秒平均一次，即100毫秒之间的所有数据均被平均。

我有一个似乎有效的解决方案，但平均值超过了每秒而不是每隔100毫秒。

SELECT date_trunc('second', timestamp) AS timestamp
      ,avg(v1) AS avg_v1
      ,avg(v2) AS avg_v2
      ,avg(v3) AS avg_v3
FROM   myTable
GROUP  BY 1;

请指导我如何解决此问题。谢谢

妞给爷站住

2020-05-18 06:38:01

Consider casting the timestamp column to timestamp(1): this will actually round it to the closest decimal second.

SELECT 
    "timestamp"::timestamp(1) AS timestamp,
    avg(v1) AS avg_v1,
    avg(v2) AS avg_v2,
    avg(v3) AS avg_v3
FROM myTable
GROUP BY 1;

Note that this rounds the timestamp instead of truncating it. Typically, 08:10:20.306 would become 08:10:20.3, while 08:10:20.350 would be turned to 08:10:20.4. This might, or might not be what you really want.

如果您确实想将时间截断到10秒钟，那么它会有点复杂。一种选择是将时间戳截断为分钟，然后提取第二部分（包括所有小数），将其转换为数字，将其截断为1小数，然后再添加回去：

SELECT 
    date_trunc('minute', "timestamp") 
        + interval '1 second' 
        * trunc((extract(seconds from  "timestamp"))::numeric, 1) AS timestamp,
    avg(v1) AS avg_v1,
    avg(v2) AS avg_v2,
    avg(v3) AS avg_v3
FROM myTable
GROUP BY 1;

lsint

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3200
注册排名
1609