数据仓库服务 GAUSSDB(DWS)-聚集函数:listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)
数据仓库服务 GAUSSDB(DWS)-聚集函数:listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)
listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list)
描述:将聚集列数据按WITHIN GROUP指定的排序方式排列,并用delimiter指定的分隔符拼接成一个字符串。
- expression:必选。指定聚集列名或基于列的有效表达式,不支持DISTINCT关键字和VARIADIC参数。
- delimiter:可选。指定分隔符,可以是字符串常数或基于分组列的确定性表达式,缺省时表示分隔符为空。
- order-list:必选。指定分组内的排序方式。
返回类型:text
listagg是兼容Oracle 11g2的列转行聚集函数,可以指定OVER子句用作窗口函数。为了避免与函数本身WITHIN GROUP子句的ORDER BY造成二义性,listagg用作窗口函数时,OVER子句不支持ORDER BY的窗口排序或窗口框架。
示例:
聚集列是文本字符集类型:
1 2 3 4 5 6 7 |
SELECT deptno, listagg(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees FROM emp GROUP BY deptno; deptno | employees --------+-------------------------------------- 10 | CLARK,KING,MILLER 20 | ADAMS,FORD,JONES,SCOTT,SMITH 30 | ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD (3 rows) |
聚集列是整型:
1 2 3 4 5 6 7 |
SELECT deptno, listagg(mgrno, ',') WITHIN GROUP(ORDER BY mgrno NULLS FIRST) AS mgrnos FROM emp GROUP BY deptno; deptno | mgrnos --------+------------------------------- 10 | 7782,7839 20 | 7566,7566,7788,7839,7902 30 | 7698,7698,7698,7698,7698,7839 (3 rows) |
聚集列是浮点类型:
1 2 3 4 5 6 7 8 9 |
SELECT job, listagg(bonus, '($); ') WITHIN GROUP(ORDER BY bonus DESC) || '($)' AS bonus FROM emp GROUP BY job; job | bonus ------------+------------------------------------------------- CLERK | 10234.21($); 2000.80($); 1100.00($); 1000.22($) PRESIDENT | 23011.88($) ANALYST | 2002.12($); 1001.01($) MANAGER | 10000.01($); 2399.50($); 999.10($) SALESMAN | 1000.01($); 899.00($); 99.99($); 9.00($) (5 rows) |
聚集列是时间类型:
1 2 3 4 5 6 7 |
SELECT deptno, listagg(hiredate, ', ') WITHIN GROUP(ORDER BY hiredate DESC) AS hiredates FROM emp GROUP BY deptno; deptno | hiredates --------+------------------------------------------------------------------------------------------------------------------------------ 10 | 1982-01-23 00:00:00, 1981-11-17 00:00:00, 1981-06-09 00:00:00 20 | 2001-04-02 00:00:00, 1999-12-17 00:00:00, 1987-05-23 00:00:00, 1987-04-19 00:00:00, 1981-12-03 00:00:00 30 | 2015-02-20 00:00:00, 2010-02-22 00:00:00, 1997-09-28 00:00:00, 1981-12-03 00:00:00, 1981-09-08 00:00:00, 1981-05-01 00:00:00 (3 rows) |
聚集列是时间间隔类型:
1 2 3 4 5 6 7 |
SELECT deptno, listagg(vacationTime, '; ') WITHIN GROUP(ORDER BY vacationTime DESC) AS vacationTime FROM emp GROUP BY deptno; deptno | vacationtime --------+------------------------------------------------------------------------------------ 10 | 1 year 30 days; 40 days; 10 days 20 | 70 days; 36 days; 9 days; 5 days 30 | 1 year 1 mon; 2 mons 10 days; 30 days; 12 days 12:00:00; 4 days 06:00:00; 24:00:00 (3 rows) |
分隔符缺省时,默认为空:
1 2 3 4 5 6 7 |
SELECT deptno, listagg(job) WITHIN GROUP(ORDER BY job) AS jobs FROM emp GROUP BY deptno; deptno | jobs --------+---------------------------------------------- 10 | CLERKMANAGERPRESIDENT 20 | ANALYSTANALYSTCLERKCLERKMANAGER 30 | CLERKMANAGERSALESMANSALESMANSALESMANSALESMAN (3 rows) |
listagg作为窗口函数时,OVER子句不支持ORDER BY的窗口排序,listagg列为对应分组的有序聚集:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT deptno, mgrno, bonus, listagg(ename,'; ') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees FROM emp; deptno | mgrno | bonus | employees --------+-------+----------+------------------------------------------- 10 | 7839 | 10000.01 | CLARK; KING; MILLER 10 | | 23011.88 | CLARK; KING; MILLER 10 | 7782 | 10234.21 | CLARK; KING; MILLER 20 | 7566 | 2002.12 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7566 | 1001.01 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7788 | 1100.00 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7902 | 2000.80 | FORD; SCOTT; ADAMS; SMITH; JONES 20 | 7839 | 999.10 | FORD; SCOTT; ADAMS; SMITH; JONES 30 | 7839 | 2399.50 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 9.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 1000.22 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 99.99 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 1000.01 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN 30 | 7698 | 899.00 | BLAKE; TURNER; JAMES; MARTIN; WARD; ALLEN (14 rows) |