Grafana+TimescaleDB最后时间桶数值失真问题求助

针对你遇到的最后一个时间桶数据不全、数值失真的问题，给你三个可行的解决思路：

1. 直接忽略最后一个时间桶 #

通过子查询先生成所有时间桶数据，再过滤掉最后一条记录，确保展示的都是完整桶的数据：

WITH bucketed_data AS (
  SELECT
    $__timeGroupAlias(observedat,$__interval) AS time_bucket,
    count(1) AS "count"
  FROM observations_timescale
  WHERE
    $__timeFilter(observedat)
  GROUP BY 1
  ORDER BY 1
)
SELECT time_bucket, "count"
FROM bucketed_data
WHERE time_bucket != (SELECT MAX(time_bucket) FROM bucketed_data);

2. 用前一个桶的数值补全最后样本 #

如果不想丢失最后一个桶的展示位置，可以用窗口函数获取前一个完整桶的计数，替换当前不完整桶的数值，保证曲线连续：

WITH bucketed_data AS (
  SELECT
    $__timeGroupAlias(observedat,$__interval) AS time_bucket,
    count(1) AS "count",
    ROW_NUMBER() OVER (ORDER BY $__timeGroupAlias(observedat,$__interval)) AS row_num,
    COUNT(*) OVER () AS total_rows
  FROM observations_timescale
  WHERE
    $__timeFilter(observedat)
  GROUP BY 1
)
SELECT 
  time_bucket,
  CASE 
    WHEN row_num = total_rows THEN LAG("count") OVER (ORDER BY time_bucket)
    ELSE "count"
  END AS "count"
FROM bucketed_data
ORDER BY time_bucket;

3. 避免生成不完整桶（调整时间范围） #

把查询的结束时间提前一个时间间隔，确保所有被统计的时间桶都已经完全结束，不会出现不完整的情况：

SELECT
  $__timeGroupAlias(observedat,$__interval),
  count(1) AS "count"
FROM observations_timescale
WHERE
  observedat >= $__timeFrom()
  AND observedat <= ($__timeTo() - INTERVAL '$__interval')
GROUP BY 1
ORDER BY 1;

你可以根据自己的业务需求选择：如果不需要实时最新数据，选第一种最直接；如果要保持曲线连续，选第二种；如果必须确保所有桶都是完整统计的，选第三种。

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