能否利用TimescaleDB连续聚合计算累积和或移动平均？——以每小时生成3小时降水累积和为例

Absolutely! You can pull this off with TimescaleDB continuous aggregates—you just need to combine time bucketing with window functions, a common pattern that’s easy to miss if you’re new to the tool. Let’s break this down step by step for your scenario.

Step 1: Convert Your Table to a Hypertable #

First, continuous aggregates only work with hypertables (TimescaleDB’s partitioned time-series tables). If you created a regular table, convert it with this command:

SELECT create_hypertable('foo', 'ts');

This enables the time-based partitioning that continuous aggregates rely on to efficiently refresh data.

Step 2: Create the Continuous Aggregate with Window Function #

The key here is using time_bucket to align your calculations to hourly intervals (your desired refresh frequency) and a window function to compute the 3-hour cumulative sum. Here’s the exact SQL to create your materialized view:

CREATE MATERIALIZED VIEW foo_3h_cumulative
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', ts) AS ts,
    SUM(precipitation) OVER (
        ORDER BY time_bucket('1 hour', ts)
        RANGE BETWEEN INTERVAL '2 hours' PRECEDING AND CURRENT ROW
    ) AS cum_precipitation
FROM foo
WITH DATA;

Let’s unpack this:

• time_bucket('1 hour', ts) rounds each timestamp to the start of its hour, ensuring we calculate once per hour (matching your desired frequency).
• The window function SUM(...) OVER (...) calculates the sum of precipitation over the current hour plus the previous 2 hours (total 3 hours). This gives you the rolling cumulative sum you want.

Step 3: Verify the Results #

With your sample data, this query will produce exactly the output you expected:

Step 4: Set Up a Refresh Policy #

To make sure the continuous aggregate updates every hour automatically, add a refresh policy:

SELECT add_continuous_aggregate_policy('foo_3h_cumulative',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '0 hours',
    schedule_interval => INTERVAL '1 hour');

• start_offset ensures we only refresh data that’s at least 3 hours old (so we don’t recalculate if new data is still being written to the latest hour).
• schedule_interval tells TimescaleDB to run the refresh every hour.

Bonus: Handling Missing Hours #

If your data has gaps (e.g., no precipitation reading for an hour), the default query won’t generate a row for that hour. If you want to include those rows with a cumulative sum of 0 (or carry forward the last sum), you can join against a generated series of hourly timestamps. Feel free to ask for help with that if needed!

内容的提问来源于stack exchange，提问作者Chad Showalter