TimescaleDB连续聚合计数DISTINCT

Question

假设我有一个简单的超表，比如

device_id	value1	时间戳
1	123	2022-04-04 10:10
2	431	2022-04-04 10:53
1	321	2022-04-04 10:12

您可以按device_id进行分组，然后计算值的数量。

CREATE MATERIALIZED VIEW agg_hourly_devices 
WITH (timescaledb.continuous)
AS
SELECT SUM(value1) AS myValue,
time_bucket('1h', "timestamp") AS bucket_time,
COUNT(value1) AS device_count
FROM devices
GROUP BY bucket_time, device_id
ORDER BY bucket_time, device_id