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

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的那行的某一字段的数据

- 获取上一行的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)是一样的。