测试数据
with aa as
(
SELECT 1 a,'2019-01-03' times FROM dual union
SELECT 2 a,'2019-01-02' times FROM dual union
SELECT 3 a,'2019-01-04' times FROM dual union
SELECT 4 a,'2019-01-05' times FROM dual union
SELECT 5 a,'2019-01-06' times FROM dual union
SELECT 6 a,'2019-01-07' times FROM dual union
SELECT 7 a,'2019-01-08' times FROM dual union
SELECT 8 a,'2019-01-09' times FROM dual union
SELECT 9 a,'2019-01-10' times FROM dual
)
函数说明
key1 表示要获得的记录,1为偏移量,0为没有记录时返回的值,key2为排序方式
(key1与key2 可以一样)
上一条记录
lag([key1],1,0)over(order by [key2])
select a,times,lag(a,1,0)over(order by a ) new_a from aa
A | TIMES | NEW_A |
---|---|---|
1 | 2019-01-03 | 0 |
2 | 2019-01-02 | 1 |
3 | 2019-01-04 | 2 |
4 | 2019-01-05 | 3 |
5 | 2019-01-06 | 4 |
6 | 2019-01-07 | 5 |
7 | 2019-01-08 | 6 |
8 | 2019-01-09 | 7 |
9 | 2019-01-10 | 8 |
下一条记录
lead([key1],1,0)over(order by [key2])
select a,times,lead(a,1,0) over(order by times ) new_a from aa
A | TIMES | NEW_A |
---|---|---|
2 | 2019-01-02 | 1 |
1 | 2019-01-03 | 3 |
3 | 2019-01-04 | 4 |
4 | 2019-01-05 | 5 |
5 | 2019-01-06 | 6 |
6 | 2019-01-07 | 7 |
7 | 2019-01-08 | 8 |
8 | 2019-01-09 | 9 |
9 | 2019-01-10 | 0 |