使用TimescaleDB计算大整数时间间隔的平均值

Question

我有一个包含以下字段的模式：

Name of row          |   Type    
--------------------------+--------
name                      | string
value1                    | numeric
timestamp                 | bigint

这些行包含一个名称、一个数值和一个bigint值的条目，以纳秒为单位存储unix时间戳。使用TimescaleDB，我想使用time_buckets_gapfill来检索数据。考虑到时间戳存储在bigint中，这相当麻烦。

我想获得这些时间间隔的汇总数据：5分钟、小时、天、周、月、季度、年。我已经设法使用普通的time_buckets使其工作，但现在我也想填补空白。我现在使用以下查询：

SELECT COALESCE(COUNT(*), 0), COALESCE(SUM(value1), 0), time_bucket_gapfill('5 min', date_trunc('quarter', to_timestamp(timestamp/1000000000)), to_timestamp(1599100000), to_timestamp(1599300000)) AS bucket
FROM playground
WHERE name = 'test' AND timestamp >= 1599100000000000000 AND timestamp <= 1599300000000000000
GROUP BY bucket
ORDER BY bucket ASC

这将正确返回值，但不会填充空白。如果我将查询修改为

time_bucket_gapfill('5 min', 
date_trunc('quarter', 
to_timestamp(timestamp/1000000000), 
to_timestamp(1599100000), 
to_timestamp(1599200000))

我会正确地得到第一个条目，然后每5分钟清空一行。我该怎么做？谢谢

这里有一个DB fiddle，但它不起作用，因为它不支持TimeScaleDB。上面的查询返回以下内容：

coalesce |       coalesce       |         avg_val
------------------------+-------------------------
3     |          300         |  2020-07-01 00:00:00+00
0     |           0          |  2020-09-03 02:25:00+00   
0     |           0          |  2020-09-03 02:30:00+00

Answer 1

您应该在time_bucket_gapfill中使用与表中的数据类型匹配的数据类型。下面的查询应该会让你找到你想要的东西：

SELECT
COALESCE(count(*), 0),
COALESCE(SUM(value1), 0),
time_bucket_gapfill(300E9::BIGINT, timestamp) AS bucket
FROM
t
WHERE
name = 'example'
AND timestamp >= 1599100000000000000
AND timestamp < 1599200000000000000
GROUP BY
bucket;

Answer 2

我在Sven的回答基础上解决了这个问题。它首先使用他的函数来填补空白，然后调用date_trunc来消除多余的行。

WITH gapfill AS (
SELECT 
COALESCE(count(*), 0) as count, 
COALESCE(SUM(value1), 0) as sum,
time_bucket_gapfill(300E9::BIGINT, timestamp) as bucket
FROM
playground
WHERE
name = 'test'
AND timestamp >= 1599100000000000000
AND timestamp < 1599300000000000000
GROUP BY
bucket
)
SELECT
SUM(count),
SUM(sum),
date_trunc('quarter', to_timestamp(bucket/1000000000)) as truncated
FROM 
gapfill
GROUP BY truncated
ORDER BY truncated ASC