TimescaleDB如何构建视图生成按月分组的周内每日聚合结果？

优化方案 #

1. 重构原始表，清理冗余字段

删除表中冗余的dow、month字段，避免数据写入时的额外计算和字段不一致风险，同时将表转为TimescaleDB超表开启时间分区，这是性能优化的基础：

-- 新建表
CREATE TABLE public.visitors (
    ts timestamp(0) without time zone NOT NULL,
    uuid uuid NOT NULL,
    count_in integer
);
-- 转为超表，按ts字段做时间分区
SELECT create_hypertable('visitors', 'ts');

-- 存量表改造直接删除冗余字段即可
ALTER TABLE visitors DROP COLUMN dow, DROP COLUMN month;

2. 优化连续聚合视图定义

直接在连续聚合视图中通过PostgreSQL内置时间函数计算dow、month，无需依赖底层表的冗余字段：

CREATE MATERIALIZED VIEW visitors_1d
WITH (timescaledb.continuous)
AS
SELECT
    time_bucket('1 day', ts) as time,
    uuid,
    -- EXTRACT(DOW)周日返回0，周一返回1，如需周一为起始值可替换为EXTRACT(ISODOW FROM ts)
    EXTRACT(DOW FROM ts)::integer as dow,
    EXTRACT(MONTH FROM ts)::integer as month,
    max(count_in) as visitors
from visitors
GROUP BY time, uuid, dow, month
WITH DATA;

如果追求极致查询性能，可以再创建二级连续聚合，直接预计算年度维度的月+周几平均访客量，查询时直接取预计算结果，响应时间可降至百毫秒级：

CREATE MATERIALIZED VIEW visitors_year_month_dow
WITH (timescaledb.continuous)
AS
SELECT
    time_bucket('1 year', time) as year,
    uuid,
    month,
    dow,
    avg(visitors) as visitors
from visitors_1d
GROUP BY year, uuid, month, dow
WITH DATA;

3. 补充索引缩小扫描范围

给连续聚合视图添加适配查询逻辑的复合索引，进一步提速：

-- 1天粒度视图适配按uuid+时间范围查询的索引
CREATE INDEX idx_visitors_1d_uuid_time ON visitors_1d (uuid, time DESC);
-- 二级聚合视图适配按uuid+年份查询的索引
CREATE INDEX idx_visitors_ymd_uuid_year ON visitors_year_month_dow (uuid, year DESC);

4. 设置自动刷新策略保证数据时效性

给连续聚合视图配置自动刷新策略，无需手动维护数据更新：

-- 1天粒度视图每小时刷新一次，覆盖最近7天的数据，可根据业务时效性要求调整参数
SELECT add_continuous_aggregate_policy('visitors_1d',
  start_offset => INTERVAL '7 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

5. 最终查询语句优化

如果使用1天粒度视图查询，语句如下：

select 
    dow, 
    month,
    avg(visitors) as visitors
from visitors_1d
where
    time BETWEEN '2020-01-01' AND '2020-12-31'
    AND uuid = 'some-uuid-here'
group by month, dow
order by month, dow;

如果使用二级聚合视图，查询可简化为：

select 
    dow, 
    month,
    visitors
from visitors_year_month_dow
where
    year = '2020-01-01'::timestamp
    AND uuid = 'some-uuid-here'
order by month, dow;

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