如何在QuestDB中准确计算小时用电量？

你原来的SQL仅计算了每个小时窗口内第一个和最后一个读数的差值，但电表读数是累计值，跨小时边界的消耗（比如20:56到21:12之间的读数变化）并没有被纳入对应小时的计算，导致结果遗漏了这部分电量。

正确的思路是：小时用电量=当前小时的最终读数 - 上一个小时的最终读数。累计值的变化，不管数据点是否落在小时窗口内，只要是从上一小时结束到当前小时结束的差值，就是该小时的实际消耗。

基础版（仅包含有数据的小时） #

该方案针对有数据记录的小时计算用电量，忽略无数据的小时：

WITH hourly_end_readings AS (
  -- 按日历小时分组，获取每个小时的最后读数
  SELECT 
    timestamp_trunc('hour', timestamp) AS hour_start,
    last(reading) AS hour_end_reading
  FROM energy_value
  SAMPLE BY 1h ALIGN TO CALENDAR
)
SELECT 
  hour_start,
  -- 当前小时读数减去上一小时读数，得到小时用电量
  hour_end_reading - LAG(hour_end_reading) OVER (ORDER BY hour_start) AS hourly_consumption
FROM hourly_end_readings

进阶版（包含无数据的小时，自动填充最近读数） #

如果需要覆盖所有日历小时，即使该小时没有数据，用最近的有效读数填充后计算：

WITH hourly_end_readings AS (
  -- 生成所有日历小时，无数据时返回NULL
  SELECT 
    timestamp_trunc('hour', timestamp) AS hour_start,
    last(reading) AS hour_end_reading
  FROM energy_value
  SAMPLE BY 1h ALIGN TO CALENDAR FILL(NULL)
),
filled_readings AS (
  -- 用最近的非NULL读数填充空值
  SELECT 
    hour_start,
    LAST_VALUE(hour_end_reading IGNORE NULLS) OVER (ORDER BY hour_start) AS filled_reading
  FROM hourly_end_readings
)
SELECT 
  hour_start,
  filled_reading - LAG(filled_reading) OVER (ORDER BY hour_start) AS hourly_consumption
FROM filled_readings

测试结果说明 #

用你提供的数据测试，基础版会返回：

• 20:00:00: 5365343 - 5365299 = 44
• 21:00:00: 5365420 - 5365343 = 77（包含了20:56到21:12的10度电）
• 22:00:00: 5365551 - 5365420 = 131

这就完整覆盖了所有时间段的用电量，不会漏掉跨小时的差值。

内容的提问来源于stack exchange，提问作者Nick The Greek