| Function | Return Type | Description |
|---|---|---|
| lag(value any [,offset integer [,default any ]]) | same type as value | returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row,instead return default. Both offset and default are evaluated with respect to the current row. If omitted,offset defaults to 1 and default to null |
| lead(value any [,default any ]]) | same type as value | returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row,offset defaults to 1 and default to null |
1. 测试数据：
postgres=# select * from tb1;
id | name ----+------
1 | aa
2 | aa
3 | aa
4 | aa
5 | aa
| bb
| cc
(7 rows)
2. lag(value any [,default any ]])：获取往前偏移offset的那行的某一字段的数据
| 参数值 | 说明 |
|---|---|
| value any | 指定某一字段 |
| offset integer | 向上的偏移量 |
| default any | 如果前后的行不存在，则填充的默认值 |
- 获取上一行的id值，不指定默认值
postgres=# select *,lag(id,1) over(order by id) from tb1;
id | name | lead
----+------+------
1 | aa | --第一行的上一行没有值，就用null填充
2 | aa | 1
3 | aa | 2
4 | aa | 3
5 | aa | 4
| bb | 5
| cc |
(7 rows)
- 获取上一行的id值，指定默认值
postgres=# select *,1,100) over(order by id) from tb1;
id | name | lag
----+------+-----
1 | aa | 100 --第一行的上一行没有值，就用指定的默认值100填充
2 | aa | 1
3 | aa | 2
4 | aa | 3
5 | aa | 4
| bb | 5
| cc |
(7 rows)
- 偏移两行
postgres=# select *,2,100) over(order by id) from tb1;
id | name | lag
----+------+-----
1 | aa | 100
2 | aa | 100
3 | aa | 1
4 | aa | 2
5 | aa | 3
| bb | 4
| cc | 5
(7 rows)
-偏移量为-1
postgres=# select *,-1,100) over(order by id) from tb1;
id | name | lag
----+------+-----
1 | aa | 2
2 | aa | 3
3 | aa | 4
4 | aa | 5
5 | aa |
| bb |
| cc | 100
(7 rows)
当偏移量为负数的时候，就是取下面行的指定字段的值了。
3. lead(value any [,default any ]])：获取往后偏移offset的那行的某一字段的数据
- 向下偏移一行
postgres=# select *,lead(id,100) over(order by id) from tb1;
id | name | lead
----+------+------
1 | aa | 2
2 | aa | 3
3 | aa | 4
4 | aa | 5
5 | aa |
| bb |
| cc | 100
(7 rows)
可以看到，lag(id,1) 和 lead(id,-1)是一样的。
