downsample postgres中毫秒范围内的时间戳

v64noz0r 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(432)

我有一张table如下

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;

请告诉我如何解决这个问题。谢谢您

sql postgresql time-series psql downsampling

来源：https://stackoverflow.com/questions/61859556/downsample-timestamp-in-milisecond-range-in-postgres

1条答案

按热度按时间

zpqajqem1#

考虑铸造 timestamp 列到 timestamp(1) ：这实际上会将其四舍五入到最接近的小数点后一秒。

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;

请注意，这将舍入时间戳而不是截断它。一般来说， 08:10:20.306 会变成 08:10:20.3 ，而 08:10:20.350 会变成 08:10:20.4 . 这可能是，也可能不是你真正想要的。
如果你真的想把时间缩短到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;

另一个截短的选项，如gordonlinoff所评论的，是先偏移然后舍入。这比上述解决方案简单，而且可能更有效：

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

赞(0）回复(0）举报 2021-08-09

我来回答

downsample postgres中毫秒范围内的时间戳

1条答案

相关问题

热门标签

最新问答