LAMBDA運算式及相關函數 - 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)