人大金仓数据库KingbaseES listagg函数使用

关键字：

listagg、人大金仓、KingbaseES

语法

LISTAGG (meaure_expr [,’delimiter’]) [WITHIN GROUP order_by_clause] [OVER query_partition_clause]

对于指定的度量列，LISTAGG函数在ORDER BY子句指定的每个组中对数据进行排序，然后连接度量值列的值。可作为单个集合聚集函数、分组聚集函数、分析函数在不同应用场景中使用。

（1）对于单个集合聚集函数，LISTAGG对所有的行进行操作并返回单个的输出行。

（2）对于组集聚合函数，LISTAGG对order by子句中定义的每个组进行排序并返回单个的输出行。

（3）对于分析函数，LISTAGG根据query_partition_clause中一个或多个表达式将查询结果集划分为多个组。

参数规则

（1）measure_expr可以是列名或表达式，度量列的空值会被忽略。如果measure_expr是RAW类型，表达式也必须是RAW类型。

（2）分隔符用于分隔度量列值的字符串，此字段是可选的，默认为NULL。分隔符可以隐式转化为RAW的字符串，或者使用UTL_RAW.CAST_TO_RAW函数显示转换分隔符为RAW。

（3）order_by_clause确定返回的连接值的顺序，仅仅当ORDER BY列是唯一排序时，该函数才是确定的。如果指明了order_by_clause，则必须指明WITHIN GROUP，反之亦然。这两个子句要么一起指定，要么都不指定。

数据准备

（1）创建数据表

test=# CREATE TABLE account(user_name varchar(20), phone_num varchar(11), amount int);

CREATE TABLE

（2）插入数据

test=# CREATE TABLE account(user_name varchar(20), phone_num varchar(11), amount int);

CREATE TABLE

test=# insert into account values('Lucy', '13642160022',3000);

INSERT 0 1

test=# insert into account values('Lily', '13642160022',5000);

INSERT 0 1

test=# insert into account values(NULL, '13642160022',5050);

INSERT 0 1

test=# insert into account values('Tom', '18622685434',10000);

INSERT 0 1

test=# insert into account values('Toney', '18622685434',9000);

INSERT 0 1

test=# insert into account values('Alice', '15950558535',12000);

INSERT 0 1

test=# insert into account values('Aline', '15950558535',8500);

INSERT 0 1

test=# insert into account values('Addy', '15950558535',6800);

INSERT 0 1

（3）查看数据表

test=# select * from account;

user_name | phone_num | amount

-----------+-------------+--------

Lucy | 13642160022 | 3000

Lily | 13642160022 | 5000

| 13642160022 | 5050

Tom | 18622685434 | 10000

Toney | 18622685434 | 9000

Alice | 15950558535 | 12000

Aline | 15950558535 | 8500

Addy | 15950558535 | 6800

测试用例

（1）LISTAGG用作单个集合聚集函数，不加within group字段，对user_name列所有的行进行连接操作，以逗号分隔，并返回单个的输出行。

test=# select listagg(user_name,',') from account;

listagg

--------------------------------------

Lucy,Lily,Tom,Toney,Alice,Aline,Addy

（2）LISTAGG用作单个集合聚集函数，加within group字段，按照phone_num列进行排序，对user_name列所有的行进行连接操作，以逗号分隔，并返回单个的输出行。

test=# select listagg(user_name,',') within group (order by phone_num) from account;

listagg

--------------------------------------

Lucy,Lily,Addy,Alice,Aline,Toney,Tom

（3）LISTAGG用作组集聚合函数，按照phone_num列分组并进行排序，对user_name列进行连接操作，以逗号分隔，并返回每组的输出行。

test=# select listagg(user_name,',') within group (order by phone_num) from account group by phone_num;

listagg

------------------

Lucy,Lily

Addy,Alice,Aline

Toney,Tom

（4）LISTAGG用作组集聚合函数，within group对多个字段进行排序，以 phone_num列分组，按照phone_num和user_name进行排序，对user_name列进行连接操作，以逗号分隔，返回每组的输出行。

test=# select listagg(user_name,',') within group (order by phone_num,user_name) from account group by phone_num;

listagg

------------------

Lily,Lucy

Addy,Alice,Aline

Tom,Toney

（5）LISTAGG用作分析函数，LISTAGG根据phone_num字段将查询结果集划分为多个组，组内按照user_name排序，将组内的参数user_name通过分隔符拼接起来，返回的结果集行数为组数。

test=# select user_name,phone_num,listagg(user_name,',')

test-# within group (order by user_name)

test-# over (partition by phone_num) as listagg from account;

user_name | phone_num | listagg

-----------+-------------+------------------

Lily | 13642160022 | Lily,Lucy

Lucy | 13642160022 | Lily,Lucy

| 13642160022 | Lily,Lucy

Addy | 15950558535 | Addy,Alice,Aline

Alice | 15950558535 | Addy,Alice,Aline

Aline | 15950558535 | Addy,Alice,Aline

Tom | 18622685434 | Tom,Toney

Toney | 18622685434 | Tom,Toney

小结

（1）聚合函数和分析函数都是对数据进行分组，但是，聚合函数对每组只返回一条数据，分析函数针对每条记录都会返回。

（2）对于meaure_expr中的空值会被忽略，排序时默认放在最后。