sql - 为什么我的 over 子句附近会出现语法错误？

Question

我有一个包含 birthdate::date 和 id::integer 列的 customers 表。

当我运行以下查询时出现语法错误(错误:语法错误在或接近“over”):

select lead(age, 1) - age over (order by age) as diff
  from customers
  join (select id,
               extract(year from age(birthdate))::integer as age
          from customers) as sub
        on customers.id = sub.id
order by age;

但当我将第一行调整为

select (-age) + lead(age, 1) over (order by age) as diff

我不明白这种行为。错误原因是什么？

我正在使用 PostgreSQL 9.6.6。

Answer 1

over (order by age) 不是完整表达式的修饰符。 lead(...) over(...) 是一个单元，您不能在它们之间放置随机的东西。如果你想把 - age 部分放在表达式的后面，你可以，你只需要将它移回比你尝试的更远的地方:

select lead(age, 1) over (order by age) - age as diff