我正在尝试使用LAG来获取组中排名第二的行以及前一行（排名第一）的值。
但是，应用条件where place = 2会使结果集的第一行只有LAG结果。
包含LEAD列会使预期结果显示在LAG列中。
我正在使用Oracle 19c，并且正在使用的表如下所示：
UKNUM PARTY UKVOTES
----- ------ --------
1 con 6605
1 lab 23745
1 lib 8206
1 plaid 1859
每个uknum至少有三行，并且所有列都不为空。
这是我的查询，旨在通过第一行的ukvotes值来获取第二行（按ukvotes）：
SELECT
*
FROM
(
SELECT
uknum,rank() OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS place,lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag
FROM
ukresults
)
WHERE
place = 2
AND uknum BETWEEN 1 AND 5;
这将产生以下结果：
UKNUM PLACE LAG
---------- ---------- ----------
1 2 23745
2 2
3 2
4 2
5 2
我希望对于第2-5行，LAG列中会有一个非空值。
在查询中添加LEAD会使LAG列包含预期结果：
SELECT
*
FROM
(
SELECT
uknum,lag(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lag,lead(ukvotes) OVER (PARTITION BY uknum ORDER BY ukvotes DESC) AS lead
FROM
ukresults
)
WHERE
place = 2
AND uknum BETWEEN 1 AND 5;
UKNUM PLACE LAG LEAD
---------- ---------- ---------- ----------
1 2 23745 6605
2 2 19262 7426
3 2 15393 10372
4 2 31288 6070
5 2 24148 11599
对于第一个查询为什么没有为所有行提供LAG值的预期结果感到困惑。
Here’s a DBFiddle with the data and the queries loaded.
更新：这是Oracle 19c的查询计划，首先是仅使用LAG的查询计划，然后是LEAD本身或LAG和LEAD的查询计划（每个包含LEAD的查询的计划都是相同的。
LAG仅计划：
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 197 | 7683 | 5 (20)| 00:00:01 |
|* 1 | VIEW | | 197 | 7683 | 5 (20)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 197 | 1773 | 5 (20)| 00:00:01 |
| 3 | TABLE accESS CLUSTER | UKRESULTS | 197 | 1773 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | UKNUMX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PLACE"=2)
2 - filter(RANK() OVER ( PARTITION BY "UKNUM" ORDER BY
INTERNAL_FUNCTION("UKVOTES") DESC )<=2)
4 - access("UKNUM"<=50)
LEAD / LAG和LEAD计划：
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 197 | 7683 | 5 (20)| 00:00:01 |
|* 1 | VIEW | | 197 | 7683 | 5 (20)| 00:00:01 |
| 2 | WINDOW SORT | | 197 | 1773 | 5 (20)| 00:00:01 |
| 3 | TABLE accESS CLUSTER| UKRESULTS | 197 | 1773 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | UKNUMX | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PLACE"=2)
4 - access("UKNUM"<=50)