LAG

功能描述

LAG函数是一个分析函数，该函数提供了在没有自连接的情况下同时访问表的多行。给定从查询返回的一系列行和游标的位置，LAG函数提供对该位置之前的给定物理偏移位置的行的访问。

• 如果未指定偏移量（参数2），则其默认值为1。
• 如果偏移量超出窗口范围，则返回可选默认值（参数3）。
• 如果未指定default，则其默认值为空。

在Oracle兼容模式下，LAG函数第一个参数类型与第三个参数类型可以不同。

支持以下任意2种变量类型的组合作为参数1和参数3：int、int2、int4、int8、number、numeric、char、nchar、varchar2、nvarchar2、varchar、nvarchar、text、clob。

具体支持情况如下表所示（行表示第一个参数，列表示第三个参数）：

注意事项

• 仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）LAG函数第一个参数类型与第三个参数类型不同。
• 如果value_expr或default被设置为 NULL，则返回 NULL。

语法格式

LAG
  { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] 
  | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
  }
  OVER ([ query_partition_clause ] order_by_clause)

参数说明

• value_expr

  值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。

• offset

  偏移量。如果未指定则默认值为1。

• default

  默认值。

• query_partition_clause

  分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。

• Order_by_clause

  排序语句，必选项。

示例

示例1： 在SELECT语句中调用LAG函数。

1、创建测试表。

CREATE TABLE agent_scope(id bigint primary key,agent_id int,entity_type varchar(1),entity_id bigint);

2、插入数据。

INSERT INTO agent_scope VALUES(1,101,'A',1011);
INSERT INTO agent_scope VALUES(2,101,'B',1012);
INSERT INTO agent_scope VALUES(3,101,'C',1013);
INSERT INTO agent_scope VALUES(4,102,'A',1021);
INSERT INTO agent_scope VALUES(5,102,'D',1024);
INSERT INTO agent_scope VALUES(6,103,'B',1032);
INSERT INTO agent_scope VALUES(7,103,'C',1033);
INSERT INTO agent_scope VALUES(8,104,'B',1032);
INSERT INTO agent_scope VALUES(9,104,'C',1033);
INSERT INTO agent_scope VALUES(10,104,'D',1034);

3、调用LAG函数不指定offset偏移量。

SELECT id ,agent_id ,entity_type ,lag(id,'0') over (partition by agent_id order by entity_id) as det FROM agent_scope;

返回结果为：

 id | agent_id | entity_type | det
----+----------+-------------+-----
  1 |      101 | A           |   1
  2 |      101 | B           |   2
  3 |      101 | C           |   3
  4 |      102 | A           |   4
  5 |      102 | D           |   5
  6 |      103 | B           |   6
  7 |      103 | C           |   7
  8 |      104 | B           |   8
  9 |      104 | C           |   9
 10 |      104 | D           |  10
(10 rows)

4、调用LAG函数指定第一个参数和第三个参数为不同数据类型。

SELECT id ,agent_id ,entity_type ,lag(id,2,5::int2) over (partition by agent_id order by entity_id) as det FROM agent_scope;

返回结果为：

 id | agent_id | entity_type | det
----+----------+-------------+-----
  1 |      101 | A           | 5
  2 |      101 | B           | 5
  3 |      101 | C           | 1
  4 |      102 | A           | 5
  5 |      102 | D           | 5
  6 |      103 | B           | 5
  7 |      103 | C           | 5
  8 |      104 | B           | 5
  9 |      104 | C           | 5
 10 |      104 | D           | 8
(10 rows)

5、清理测试表。

DROP TABLE agent_scope;