LAMBDA表达式及相关函数_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres V3.2版本起，支持LAMBDA表达式和若干高阶数组函数。本文为您介绍Hologres中LAMBDA表达式和高阶数组函数的使用。

LAMBDA表达式

语法

LAMBDA [ (lambda_arg1, ..., lambda_argn) => expr ]

参数说明
- LAMBDA[]：LAMBDA表达式关键字。
- (lambda_arg1, ..., lambda_argn) => expr：LAMBDA函数定义，其中：
  - (lambda_arg1, ..., lambda_argn)：表达式输入参数，支持定义任意数量入参，无需定义数据类型，多入参时需要使用半角圆括号。
  - expr：表达式定义，仅支持标量（Scalar）表达式，支持使用输入参数、引用表的列名，支持嵌套LAMBDA表达式，也即标量（scalar）表达式内可以使用含有LAMBDA表达式的ARRAY高阶函数。不支持聚合函数、窗口函数、子查询。
    关于PostgreSQL的表达式介绍详情，请参见表达式。
  - =>：输入参数和表达式间的操作符。

示例

LAMBDA [ (x1, x2, x3) => x2 - x1 * x3 + array_min(arr_col) ]

高阶数组函数列表

HG_ARRAY_MAP

描述：通过LAMBDA表达式对输入数组的对应元素执行表达式运算。
```
HG_ARRAY_MAP(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
使用说明
不支持全部常量入参。
参数说明
- LAMBDA[func(x1 [, ..., xN])]：LAMBDA表达式定义。
- source_arr：输入数组，有多个数组输入时，数组长度需要一致。
返回值说明
返回一个数组，数组类型与表达式返回值类型一致，数组长度与第一个输入数组长度一致。如果输入数组为NULL，则返回NULL。

示例

示例数据

DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl1 VALUES(1, ARRAY[1,2,3], ARRAY[11,12,13],1,2);
INSERT INTO tbl1 VALUES(2, ARRAY[21,22,23], ARRAY[31,32,33],10,20);

示例1：将arr_col1的每个元素都与col3相加。

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[x => x + col3], arr_col1)
FROM
    tbl1
ORDER BY
    id;

返回结果如下。

id | hg_array_map 
----+--------------
  1 | {2,3,4}
  2 | {31,32,33}
(2 rows)

示例2：让arr_col1和常量数组的每个元素对应相加，并加上arr_col2数组的最小值。

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[(x, y) => y + x + array_min (arr_col2)], arr_col1, ARRAY[5,6,7])
FROM
    tbl1
ORDER BY
    id;

返回结果如下。

 id | hg_array_map 
----+--------------
  1 | {17,19,21}
  2 | {57,59,61}
(2 rows)

示例3：使用嵌套LAMBDA表达式，将arr_col2的每个元素都与col3相加，并将所得结果数组中的最小值与arr_col1中的每个元素进行对应相加。

SELECT
    id,
    HG_ARRAY_MAP (LAMBDA[x => x + array_min (HG_ARRAY_MAP (LAMBDA[a => a + col3], arr_col2))], arr_col1)
FROM
    tbl1
ORDER BY
    id;

返回结果如下。

id | hg_array_map 
----+--------------
  1 | {13,14,15}
  2 | {62,63,64}
(2 rows)

HG_ARRAY_FILL

描述：通过LAMBDA表达式对输入数组的元素进行运算，生成一个BOOLEAN类型数组。利用该BOOLEAN数组对第一个输入数组进行筛选填充：从最小下标开始，当BOOLEAN数组对应元素为TRUE时，填充对应输入数组的元素，后续元素保持与前一个填充元素相同，直到遇到下一个TRUE元素。最终返回填充后的结果数组。
```
HG_ARRAY_FILL(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
使用说明
不支持全部常量入参。
参数说明
- LAMBDA[func(x1 [, ..., xN])]：LAMBDA表达式定义。
- source_arr：输入数组。有多个数组输入时，数组长度需要一致。
返回值说明
返回一个数组，数组类型和长度均与第一个输入数组一致。如果输入数组为NULL，则返回NULL。

示例

示例数据

DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl2 VALUES(1, ARRAY[1,2,3,4,5,6,7,8,9],ARRAY[1,0,0,1,0,0,0,1,0],1,2);
INSERT INTO tbl2 VALUES(2, ARRAY[10,12,13,14,15,16,17,18,19],ARRAY[1,0,0,1,0,0,0,1,0],1,2);

示例1：数组arr_col2大于0的元素，对应arr_col1的元素直接填充，后续元素保持相等。

SELECT
    id,
    HG_ARRAY_FILL (LAMBDA[(x, y) => y > 0], arr_col1, arr_col2)
FROM
    tbl2
ORDER BY
    id;

返回结果如下。

 id |        hg_array_fill         
----+------------------------------
  1 | {1,1,1,4,4,4,4,8,8}
  2 | {10,10,10,14,14,14,14,18,18}
(2 rows)

示例2：数组arr_col2小于等于0的元素，对应arr_col1的元素直接填充，后续元素保持相等。arr_col2的第一个元素大于0，对应arr_col1的元素也直接填充。

SELECT
    id,
    HG_ARRAY_FILL (LAMBDA[(x, y) => y <= 0], arr_col1, arr_col2)
FROM
    tbl2
ORDER BY
    id;

返回结果如下。

 id |        hg_array_fill         
----+------------------------------
  1 | {1,2,3,3,5,6,7,7,9}
  2 | {10,12,13,13,15,16,17,17,19}
(2 rows)

HG_ARRAY_FILTER

描述：通过Lambda函数对输入数组的对应元素执行表达式运算，生成一个BOOLEAN类型数组。将然后根据该BOOLEAN数组，过滤原始输入数组中对应位置为TRUE的元素，并返回过滤后的结果数组。
```
HG_ARRAY_FILTER(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
使用说明
不支持全部常量入参。
参数说明
- LAMBDA[func(x1 [, ..., xN])]：LAMBDA表达式定义。
- source_arr：输入数组。有多个数组输入时，数组长度需要一致。
返回值说明
返回一个数组，数组类型和长度均与第一个输入数组一致。如果输入数组为NULL，则返回NULL。

示例

示例数据

DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3(id INT, arr_col1 INT[], arr_col2 INT[], col3 INT, col4 INT);
INSERT INTO tbl3 VALUES(1, ARRAY[0,2,3,4,5,6,7,0,9], ARRAY[1,0,0,1,0,0,0,1,18],1,2);
INSERT INTO tbl3 VALUES(2, NULL, ARRAY[31,32,33,34,35,36,37,38,39],10,20);
INSERT INTO tbl3 VALUES(3, ARRAY[0,2,3,4,5,6,7,0,9], ARRAY[11,12,13,14,15,16,17,18,19],NULL,2);

示例1：按数组arr_col2的大于0的元素，对arr_col1进行过滤。

SELECT
    id,
    HG_ARRAY_FILTER (LAMBDA[(x, y) => y > 0], arr_col1, arr_col2)
FROM
    tbl3
ORDER BY
    id;

返回结果如下。

 id |   hg_array_filter   
----+---------------------
  1 | {0,4,0,9}
  2 | 
  3 | {0,2,3,4,5,6,7,0,9}
(3 rows)

示例2：按如下条件生成BOOLEAN数组：arr_col1大于0的元素，或col3为NULL，或arr_col1的元素加arr_col2的元素加col3加col4大于20。按BOOLEAN数组对arr_col1进行过滤。

SELECT
    id,
    HG_ARRAY_FILTER (
        LAMBDA[(x, y) => 
            (x = 0) 
            OR (col3 IS NULL) 
            OR (x + y + col3 + col4 > 20)]
        , arr_col1, arr_col2)
FROM
    tbl1
ORDER BY
    id;

返回结果如下。

id |   hg_array_filter   
----+---------------------
  1 | {}
  2 | {21,22,23}
(2 rows)

HG_ARRAY_SORT

描述：通过LAMBDA表达式对输入数组的每个元素进行运算，生成一个用于排序的值数组。然后根据该值数组的排序结果（从小到大），对第一个输入数组进行重排序，并返回排序后的结果数组。
```
HG_ARRAY_SORT(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
使用说明
不支持全部常量入参。
参数说明
- LAMBDA[func(x1 [, ..., xN])]：LAMBDA表达式定义。
- source_arr：输入数组。有多个数组输入时，数组长度需要一致。
返回值说明
返回一个数组，数组类型和长度均与第一个输入数组一致。如果输入数组为NULL，则返回NULL。

示例

根据arr_col1数组，对常量数组ARRAY[4,5,6]进行排序。

DROP TABLE IF EXISTS tbl4;
CREATE TABLE tbl4(id INT, arr_col1 INT[]);
INSERT INTO tbl4 VALUES(1, ARRAY[3,1,2]);
INSERT INTO tbl4 VALUES(2, ARRAY[2,3,1]);
INSERT INTO tbl4 VALUES(3, ARRAY[1,2,3]);
INSERT INTO tbl4 VALUES(4, NULL);

SELECT
    id,
    HG_ARRAY_SORT (LAMBDA[(x,y) => y], ARRAY[4,5,6], arr_col1)
FROM
    tbl4
ORDER BY
    id;

返回结果如下。

 id | hg_array_sort 
----+---------------
  1 | {5,6,4}
  2 | {6,4,5}
  3 | {4,5,6}
  4 | 
(4 rows)

根据arr_col2数组，对arr_col1数组进行排序。

DROP TABLE IF EXISTS tbl5;
CREATE TABLE tbl5(id INT, arr_col1 TEXT[], arr_col2 INT[]);
INSERT INTO tbl5 VALUES(1, ARRAY['1','2','3','4','5'], ARRAY[1,2,3,4,5]);
INSERT INTO tbl5 VALUES(2, ARRAY['1','2','3','4','5'], NULL);
INSERT INTO tbl5 VALUES(3, ARRAY['1','2','3','4','5'], ARRAY[21, 22, 20, 24, 25]);
INSERT INTO tbl5 VALUES(4, ARRAY['1','2','3','4','5'], ARRAY[21, 24, 22, 25, 23]);
INSERT INTO tbl5 VALUES(5, ARRAY['1','2','3','4','5'], ARRAY[21, 22, NULL, 24, 25]);

SELECT
    id,
    HG_ARRAY_SORT (LAMBDA[(x, y) => y], arr_col1, arr_col2)
FROM
    tbl5
ORDER BY
    id;

返回结果如下。

id | hg_array_sort 
----+---------------
  1 | {1,2,3,4,5}
  2 | 
  3 | {3,1,2,4,5}
  4 | {1,3,5,2,4}
  5 | {3,1,2,4,5}
(5 rows)

HG_ARRAY_FIRST_INDEX

描述：对输入数组的每个元素应用LAMBDA表达式进行计算，得到一个BOOLEAN数组，返回第一个值为TRUE的元素的下标。若无TRUE元素，则返回0。
```
HG_ARRAY_FIRST_INDEX(LAMBDA[func(x1 [, ..., xN])], source_arr1 [, ..., source_arrN]);
```
使用说明
不支持全部常量入参。
参数说明
- LAMBDA[func(x1 [, ..., xN])]：LAMBDA表达式定义。
- source_arr：输入数组。有多个数组输入时，数组长度需要一致。

示例

计算arr_col1数组中第一个大于等于3的元素的下标。

DROP TABLE IF EXISTS tbl6;
CREATE TABLE tbl6(id INT, arr_col1 INT[]);
INSERT INTO tbl6 VALUES(1, ARRAY[1,2,3,4,5]);
INSERT INTO tbl6 VALUES(2, NULL);

SELECT
    id,
    HG_ARRAY_FIRST_INDEX (LAMBDA[x => x >= 3], arr_col1)
FROM
    tbl6
ORDER BY
    id;

返回结果如下。

 id | hg_array_first_index 
----+----------------------
  1 |          3
  2 | 
(2 rows)