PostgreSQL lag，lead获取记录前后的数据

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 [, offset integer [, 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, 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

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 [, offset integer [, default any ]])：获取往前偏移offset的那行的某一字段的数据

参数值	说明
value any	指定某一字段
offset integer	向上的偏移量
default any	如果前后的行不存在，则填充的默认值

- 获取上一行的id值，不指定默认值

postgres=# select *,lag(id,1) over(order by id) from tb1;
 id | name | lead 
----+------+------
   | aa   |      --第一行的上一行没有值，就用null填充
   | aa   |    1
   | aa   |    2
   | aa   |    3
   | aa   |    4
    | bb   |    5
    | cc   |     
( rows)
           

• 获取上一行的id值，指定默认值

postgres=# select *,lag(id,1,100) over(order by id) from tb1;
 id | name | lag 
----+------+-----
   | aa   | 100  --第一行的上一行没有值，就用指定的默认值100填充
   | aa   |   1
   | aa   |   2
   | aa   |   3
   | aa   |   4
    | bb   |   5
    | cc   |    
( rows)
           

• 偏移两行

postgres=# select *,lag(id,2,100) over(order by id) from tb1;
 id | name | lag 
----+------+-----
   | aa   | 100
   | aa   | 100
   | aa   |   1
   | aa   |   2
   | aa   |   3
    | bb   |   4
    | cc   |   5
( rows)
           

-偏移量为-1

postgres=# select *,lag(id,-1,100) over(order by id) from tb1;
 id | name | lag 
----+------+-----
   | aa   |   2
   | aa   |   3
   | aa   |   4
   | aa   |   5
   | aa   |    
    | bb   |    
    | cc   | 100
( rows)
           

当偏移量为负数的时候，就是取下面行的指定字段的值了。

3. lead(value any [, offset integer [, default any ]])：获取往后偏移offset的那行的某一字段的数据

• 向下偏移一行

postgres=# select *,lead(id,1,100) over(order by id) from tb1;
 id | name | lead 
----+------+------
   | aa   |    2
   | aa   |    3
   | aa   |    4
   | aa   |    5
   | aa   |     
    | bb   |     
    | cc   |  100
( rows)
           

可以看到，lag(id, 1) 和 lead(id, -1)是一样的。