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

我有一张桌子,如下

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;

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

评论
妞给爷站住
妞给爷站住

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;
点赞
评论