如何在TimescaleDB中计算指标随时间的增长量与增长率

你之前写的窗口函数存在两个语法/逻辑错误：一是OVER()子句中做分组必须用PARTITION BY而非GROUP BY；二是分区字段不需要加时间维度，只保留name即可实现同指标的跨周期取值。
在TimescaleDB中计算同指标环比增长，LAG()窗口函数是性能最优的方案，不需要表自关联，可直接利用时序表的时间、标签索引完成计算。

基础信息说明 #

现有测试表结构与样例数据：

one_day        | name | metric_value 
------------------------+---------------------
2022-05-30 00:00:00+00 | foo  |    400
2022-05-30 00:00:00+00 | bar  |    200
2022-06-01 00:00:00+00 | foo  |    800
2022-06-01 00:00:00+00 | bar  |    1000

期望输出为各指标对比上一周期的绝对增长量与百分比增长率：

name | % growth | growth 
-------------------------
 foo  |  200%    | 400
 bar  |  500%    | 800

可直接运行的SQL代码 #

将代码中的your_metric_table替换为你实际的表名即可：

WITH metric_prev_calc AS (
    SELECT
        name,
        metric_value,
        -- 核心逻辑：按name分区，同分区内按时间升序排列，取上一个时间点的指标值
        LAG(metric_value) OVER (
            PARTITION BY name
            ORDER BY one_day ASC
        ) AS last_period_value
    FROM your_metric_table
)
SELECT
    name,
    CONCAT(
        ROUND(((metric_value - last_period_value)::NUMERIC / last_period_value) * 100, 0),
        '%'
    ) AS "% growth",
    metric_value - last_period_value AS growth
FROM metric_prev_calc
-- 过滤掉没有上一周期数据的最早时间点记录
WHERE last_period_value IS NOT NULL;

逻辑说明 #

• PARTITION BY name保证窗口计算只会在相同name的指标组内进行，不会出现跨指标混算的问题
• ORDER BY one_day ASC保证同组内数据按时间从早到晚排序，LAG()取到的就是紧邻的上一个周期的数值
• 该写法在TimescaleDB的时序分片、索引加持下，百万级以上数据量计算也能保持很高的查询效率
• 如果需要计算指定时间范围内的增长率，可以在CTE内部加WHERE one_day BETWEEN 起始时间 AND 结束时间的过滤条件，提前裁剪数据进一步提升性能

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