使用分析函数时出错.我找不到原因

Question

此查询中的错误是什么？

select department_id, first_name,
lag(first_name) over (partition by department_id order by salary rows between 1 preceding and 2 following), 
lead(first_name) over (partition by department_id order by salary rows between 1 preceding and 2 following) 
from employees order by department_id;

错误是：

ORA-00907: missing right parenthesis

但是括号是可以的。这里有什么问题？

Answer 1

lag()和lead()不接受窗口子句(rows between部分(。他们所做的是查看"上一行"和"下一行"，所以，如果你考虑一下，从功能的角度来看，窗框是没有意义的。

从文档 - 强调我的：

某些分析函数允许windowing_clause。在本节末尾的分析函数列表中，允许windowing_clause的函数后跟一个星号 (*(。

在文档的下方，列出lag()和lead()时不带星号(不像，例如avg()或count()。

大概，你只需要：

select 
department_id, 
first_name,
lag(first_name)  over (partition by department_id order by salary) lag_first_name, 
lead(first_name) over (partition by department_id order by salary) lead_first_name
from employees 
order by department_id;

---

请注意，您可以通过将第二个参数传递给函数(未指定时默认为1(来偏移lead()和lag()，例如：

lead(first_name, 3)  -- get the "third" following first name
over (partition by department_id order by salary) lag_first_name