VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

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。

具体支持情况如下表所示(行表示第一个参数,列表示第三个参数):

参数 int int2 int4 int8 int16 number char nchar varchar2 nvarchar2 varchar nvarchar text clob
int
int2
int4
int8
number
int16
char
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;