PostgreSQL中窗口函数用作聚合函数时的报错问题排查

需求说明 #

现有表forecast_table，每行包含timestamp1、timestamp2（均为时间戳类型）和value字段。需要完成以下操作：

1. 计算每行的timediff列，值为timestamp2 - timestamp1的时间差；
2. 按timestamp1和timediff分组，每组中选取timestamp2最小的那条数据对应的value。

原SQL及报错 #

尝试执行以下SQL：

SELECT
  timestamp1,
  timediff,
  FIRST_VALUE(value) OVER (ORDER BY timestamp2) AS value
FROM (
  SELECT
    timestamp1,
    timestamp2,
    value,
    timestamp2 - timestamp1 AS timediff
  FROM forecast_table WHERE device = 'TEST'
) sq
GROUP BY timestamp1,timediff
ORDER BY timestamp1

出现报错：

错误：列"sq.value"必须出现在GROUP BY子句中或用于聚合函数里

问题原因 #

1. PostgreSQL的GROUP BY规则要求：SELECT子句中的非分组列必须用聚合函数处理，窗口函数不属于聚合函数，无法满足这个要求；
2. 原SQL中的FIRST_VALUE没有指定PARTITION BY，会将所有数据视为一个窗口，逻辑上也无法实现按timestamp1和timediff分组取最小timestamp2对应value的需求。

正确实现方式 #

方式一：使用PostgreSQL特有的DISTINCT ON（推荐） #

SELECT DISTINCT ON (timestamp1, timediff)
  timestamp1,
  timediff,
  value
FROM (
  SELECT
    timestamp1,
    timestamp2,
    value,
    timestamp2 - timestamp1 AS timediff
  FROM forecast_table WHERE device = 'TEST'
) sq
ORDER BY timestamp1, timediff, timestamp2 ASC;

DISTINCT ON (col1, col2)会保留每组（timestamp1,timediff）中，按ORDER BY指定顺序排列的第一行。这里按timestamp2升序，就能直接获取每组timestamp2最小的那条数据的value。

方式二：使用窗口函数ROW_NUMBER() #

SELECT
  timestamp1,
  timediff,
  value
FROM (
  SELECT
    timestamp1,
    timestamp2,
    value,
    timestamp2 - timestamp1 AS timediff,
    ROW_NUMBER() OVER (PARTITION BY timestamp1, timediff ORDER BY timestamp2 ASC) AS rn
  FROM forecast_table WHERE device = 'TEST'
) sq
WHERE rn = 1
ORDER BY timestamp1;

通过PARTITION BY timestamp1, timediff将数据按目标分组划分窗口，每个窗口内按timestamp2升序给行编号，取编号为1的行就是每组timestamp2最小的数据。

方式三：GROUP BY结合关联查询 #

SELECT
  f.timestamp1,
  f.timediff,
  ft.value
FROM (
  SELECT
    timestamp1,
    timestamp2 - timestamp1 AS timediff,
    MIN(timestamp2) AS min_timestamp2
  FROM forecast_table WHERE device = 'TEST'
  GROUP BY timestamp1, timestamp2 - timestamp1
) f
JOIN forecast_table ft
  ON ft.timestamp1 = f.timestamp1
  AND ft.timestamp2 = f.min_timestamp2
  AND ft.device = 'TEST'
ORDER BY f.timestamp1;

先分组计算每组的最小timestamp2，再通过关联原表，获取该timestamp2对应的value。

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