TimescaleDB中time_bucket_gapfill与locf组合返回异常结果排查

背景与需求 #

平台存储了各门户的每日用户统计数据，示例数据如下：

id     date        value

7057    2022-01-26  1   
7062    2022-01-31  13  
7063    2022-02-01  17  
7064    2022-02-02  20  
7065    2022-02-03  21  
7067    2022-02-05  23  
7069    2022-02-07  24  
7073    2022-02-11  25  
7074    2022-02-12  26  
7078    2022-02-16  30  
7079    2022-02-17  32  
7080    2022-02-18  33  
7084    2022-02-22  34  
7093    2022-03-03  35  
7098    2022-03-08  36  
7101    2022-03-11  37  
7126    2022-04-05  38  
7133    2022-04-12  39  
7144    2022-04-23  40  
7146    2022-04-25  41  
7149    2022-04-28  42  
7176    2022-05-25  43  
7177    2022-05-26  45  
7193    2022-06-11  46  
7203    2022-06-21  47  
7227    2022-07-15  48  
7237    2022-07-25  49  
10715   2022-08-22  48  

需要生成补全缺失日期并沿用最近可用值的结果，预期效果（以最后三条数据为例）：

2022-07-15 48 
2022-07-16 48 
... (value 48 till the 2022-07-24)
2022-07-24 48 
2022-07-25 49 
... (value 49 till the 2022-08-21)
2022-08-21 49 
2022-08-22 48 
... (value 48 till the current date)
2022-08-27 48  (current date)

异常现象 #

使用以下TimescaleDB SQL查询后，结果不符合预期：

SELECT time_bucket_gapfill(
        '1 day',
        timestamp,
        TO_TIMESTAMP(1643155200),
        now()
    ) AS time,
    locf(sum(count)) as value
FROM portal_user_stats
WHERE portal = '6171a8601a00c437c4f219e0'
GROUP BY time
ORDER BY time;

实际返回异常结果：

2022-07-15 48
...
2022-07-23 48
2022-07-24 49 <- expected to be 48 and only on next line start 49
2022-07-25 49
...
2022-08-20 49
2022-08-21 49
2022-08-22 49 <- expected to be 48 starting from this value till the end
2022-08-23 49
2022-08-24 49
2022-08-25 49
2022-08-26 49
2022-08-27 49

错误原因排查 #

1. 字段不匹配：查询中使用timestamp作为时间字段，但示例数据里的日期字段是date；同时用了sum(count)，但实际存储统计值的字段是value，字段混淆导致聚合逻辑错误，影响locf的填充结果。
2. 函数顺序错误：当前写法是对sum(count)的结果应用locf，但sum(count)本身逻辑错误，且locf应该作用于原始的每日统计值，而非错误的聚合结果，导致填充提前或滞后。

解决方案 #

调整SQL逻辑，匹配正确字段并修正填充顺序：

方案1：直接调整查询逻辑 #

SELECT 
    time_bucket_gapfill('1 day', date, TO_TIMESTAMP(1643155200), now()) AS time,
    locf(last_value(value)) OVER (ORDER BY time_bucket_gapfill('1 day', date, TO_TIMESTAMP(1643155200), now())) AS value
FROM portal_user_stats
WHERE portal = '6171a8601a00c437c4f219e0'
GROUP BY time
ORDER BY time;

方案2：使用子查询拆分逻辑（更易读） #

WITH daily_stats AS (
    SELECT 
        time_bucket_gapfill('1 day', date, TO_TIMESTAMP(1643155200), now()) AS time,
        last_value(value) AS daily_value
    FROM portal_user_stats
    WHERE portal = '6171a8601a00c437c4f219e0'
    GROUP BY time
)
SELECT 
    time,
    locf(daily_value) AS value
FROM daily_stats
ORDER BY time;

关键调整点 #

• 将时间字段从timestamp改为实际的date，确保时间桶分组准确。
• 用last_value(value)获取每个时间桶的有效统计值，替代错误的sum(count)。
• 让locf作用于原始的每日统计值，保证空缺日期正确沿用最近的非空值。

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