postgresql - 窗口分区上的数学运算符

Question

我正在尝试获取时差

lead(time) - time

这行不通:

SELECT 
the_geom,
id,
lat,
lng,
mapid,
time,
lead(time) - time  // THIS DOESNT WORK
OVER (PARTITION BY id ORDER BY time ASC)
as duration

FROM history

但这行得通:

SELECT 
the_geom,
id,
lat,
lng,
mapid,
time,
-time + lead(time)   // THIS WORKS
OVER (PARTITION BY id ORDER BY time ASC)
as duration

FROM history

第一个报错:

Syntax error near OVER

我不知道为什么会这样，谁能解释一下？

Answer 1

窗口函数有两部分

第一部分是类似于聚合函数的函数表达式，例如

Sum(revenue)
row_number()
rank()

这部分后面必须跟一个 OVER 子句。在你的情况下

OVER (PARTITION BY id ORDER BY time ASC)

窗函数就是把两者放在一起做的，例如

SUM(revenue) OVER (PARTITION BY department 
                   ORDER BY created_month 
                   ROWS BETWEEN UNBUNDED PRECEDING AND CURRENT ROW)
-- this calculates the running total for fictitious `departments` 
-- over months

您的第一个查询不会这样做，第二个查询会这样做，因为 SQL 在计算表达式时会忽略新行