LEAD

功能描述

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

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

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

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

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

注意事项

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

语法格式

LEAD
  { ( 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

  默认值。偏移量超出分区范围时返回的值，如果未指定默认值，则返回 NULL。

• {RESPECT | IGNORE} NULLS

  决定value_expr的null值是否包含在数据集中。

  • IGNORE NULLS：忽略数据集中的NULL值。

  • RESPECT NULLS：包含数据集中的NULL值。

  默认值：RESPECT NULLS，即value_expr中包含NULL值。

• query_partition_clause

  将FROM子句生成的结果集划分为要应用函数的分区。如果未指定，则此函数将查询结果集的所有行视为单个组。

• Order_by_clause

  排序语句，必选项。

示例

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、调用LEAD函数不指定offset偏移量。

SELECT id ,agent_id ,entity_type ,lead(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、调用LEAD函数指定第一个参数和第三个参数为不同数据类型。

SELECT id ,agent_id ,entity_type ,lead(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           | 3
  2 |      101 | B           | 5
  3 |      101 | C           | 5
  4 |      102 | A           | 5
  5 |      102 | D           | 5
  6 |      103 | B           | 5
  7 |      103 | C           | 5
  8 |      104 | B           | 10
  9 |      104 | C           | 5
 10 |      104 | D           | 5
(10 rows)

5、清理测试表。

DROP TABLE agent_scope;