sql - 需要对 oracle sql 中的 LEAD 函数进行一些更正
问题描述
这是我的桌子
ID NAME YEAR PRICE
--- ------ ------ -------
1 A 2015 3
1 A 2016 4
1 A 2017 7
2 B 2015 4
2 B 2016 2
2 B 2017 6
需要输出这个
ID NAME LEAD YEAR LEAD_SAL PRICE PRICE
---- ------ ------ ------ --------- ------- ------
1 A 2016 2015 4 3 1
1 A 2017 2016 7 4 3
1 A 2017 7
2 B 2016 2015 2 4 -2
2 B 2017 2016 6 2 4
2 B 2017 6
我的查询
SELECT id, name, LEAD(year,1,NULL) OVER (PARTITION BY id ORDER BY year) lead, year
, LEAD(price,1,NULL) OVER (PARTITION BY id ORDER BY price) lead_sal, price
, LEAD(price,1,NULL) OVER (PARTITION BY id ORDER BY price) - Price Price
FROM price_tb;
但是答案是这样的
ID NAME LEAD YEAR LEAD_SAL PRICE PRICE
---- ------ ------ ------ --------- ------- ------
1 A 2016 2015 4 3 1
1 A 2017 2016 7 4 3
1 A 2017 7
2 B 2016 2015 6 4 2
2 B 2017 2016 4 2 2
2 B 2017 6
这里 id 1 工作正常,但 id 2 给出的输出偏离预期。我怎样才能重写这个查询?
解决方案
怎么样
SQL> with test (id, name, year, price) as
2 (select 1, 'A', 2015, 3 from dual union all
3 select 1, 'A', 2016, 4 from dual union all
4 select 1, 'A', 2017, 7 from dual union all
5 --
6 select 2, 'B', 2015, 4 from dual union all
7 select 2, 'B', 2016, 2 from dual union all
8 select 2, 'B', 2017, 6 from dual
9 )
10 select
11 id,
12 name,
13 lead(year) over (partition by id order by year) lead_year,
14 year,
15 lead(price) over (partition by id order by year) lead_price,
16 price,
17 --
18 lead(price) over (partition by id order by year) - price diff
19 from test
20 order by id, year;
ID N LEAD_YEAR YEAR LEAD_PRICE PRICE DIFF
---------- - ---------- ---------- ---------- ---------- ----------
1 A 2016 2015 4 3 1
1 A 2017 2016 7 4 3
1 A 2017 7
2 B 2016 2015 2 4 -2
2 B 2017 2016 6 2 4
2 B 2017 6
6 rows selected.
SQL>
推荐阅读
- javascript - Momentjs 和流类型
- c++ - 无条件三元运算符
- progressive-web-apps - PWA 很快不会自动显示添加到主屏幕提示
- html - CSS
- 不延长
- c# - EF Core 可选列按要求处理
- floating-point - 没有 MTC1 的 MIPS 中浮点数的移位
- json - 分离处理非常大的压缩 JSON 文件的命令的保存输出
- javascript - 如何获得将 ID 号转换为 var(数学)
- css - 使用 background-size:cover 时如何获取图像的比例百分比?
- rasa-nlu - Rasa-core 是否在幕后训练实际对话数据?