南大通用GBase8s数据库：LISTAGG函数的解析

用户627994718262
129 阅读5分钟

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在数据库管理中，我们经常需要将多个行中的数据合并成一个单独的字符串列表，以便于数据的展示和分析。南大通用GBase8s数据库提供了一个的聚合函数——LISTAGG，它不仅能够实现这一功能，还能对合并的字符串进行排序，并允许我们自定义分隔符。本文将对LISTAGG函数进行深入介绍，包括其语法、功能、用例分析，以及在实际应用中的注意事项。

LISTAGG函数概述

LISTAGG聚合函数的主要作用是将多行数据合并为一个字符串列表。这一过程不仅涉及数据的合并，还包括对数据的排序，确保数据的逻辑顺序。此外，通过指定分隔符，我们可以定义列表中各项的分隔方式，从而满足不同的数据展示需求。

LISTAGG语法说明及用例

语法说明

LISTAGG函数语法及说明如下：

语法：

LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (ORDER BY order_by_clause)

  • measure_expr 表达式

    需要合并多行记录的表达式， 支持列名、常量、列表达式。不支持省略。

  • delimiter 字符串

    分隔多行记录的分隔符 ，支持一个或多个字符。支持省略， 省略时无分隔符。 

  • order_by_clause 表达式

    WITHIN GROUP 子句内的 order_by_clause 表达式确定了聚合前数据的排序方式。这一步是必要的，因为 LISTAGG 需要按照这个顺序来合并字符串。支持多个列名，多列名时，按照从前到后 的顺序依次排序。不支持省略。

--其他说明：

  • 函数返回的值为字符型，如果聚合后的字符串长度超过了GBase8s数据库允许的最大长度（32765），GBase8s数据库将报错。
  • LISTAGG 函数最后返回的字符串不包含 NULL 值。

用例分析

为了更好地理解LISTAGG函数的用法，我们通过几个具体的用例来进行分析。

注意：以下所用示例使用的数据库版本为：GBase8sV8.8_TL_3.5.1_x86_64

下面的例子都是对 listagg语法的简单的演示，使用 employees 表，先看一下基础数据：

CREATE TABLE employees (
   employee_id int,
   department_id VARCHAR2(20),
   employee_name VARCHAR2(20)
);
-- 插入6条数据：
insert into employees  values (101,'depart1', 'carry');
insert into employees  values (102,'depart1', 'sam');
insert into employees  values (201,'depart2', 'joice');
insert into employees  values (202,'depart2', 'tim');
insert into employees  values (203,'depart2', 'Amy');
insert into employees  values (301,'depart3', 'rose');
> select * from employees order by employee_id;
employee_id department_id        employee_name
       101 depart1              carry
       102 depart1              sam
       201 depart2              joice
       202 depart2              tim
       203 depart2              Amy
       301 depart3              rose
6 row(s) retrieved.
注：用户表employees 中字段department_id记录了用户所属部门id。

用例1： 无GROUP BY 子句（即无聚合组），则数据行被视为一个大组，按照employee_id（默认升序）来合并字符串，函数返回值为单行。

> select listagg( employee_name, ',') within group(order by employee_id ) fname  from employees;

fname carry,sam,joice,tim,Amy,rose

1 row(s) retrieved.

用例2： 有GROUP BY 子句，通过LISTAGG函数得到各个部门下所有用户，各部门中的用户按照employee_id来合并字符串。

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees group by department_id order by department_id;

department_id  depart1
fname          carry,sam

department_id  depart2
fname          joice,tim,Amy

department_id  depart3
fname          rose

3 row(s) retrieved.

注：与其他聚合函数一样，投影列必须包含在group by子句中，否则报错。下面例子中department_id未包含在group by子句中，将报错。

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees order by department_id;

294: The column (department_id) must be in the GROUP BY list.
Error in line 1
Near character position 109

用例3： 数据中有null，它们会被忽略，不会出现在最终的字符串列表中。
插入1条null数据：

insert into employees(employee_id,department_id) values (302,'depart3');

>  select * from employees where department_id='depart3';

employee_id department_id        employee_name
       301 depart3                rose
       302 depart3
2 row(s) retrieved.

> select department_id, listagg( employee_name, ',') within group(order by employee_id) fname  from employees group by department_id order by department_id;

department_id  depart1
fname          carry,sam
department_id  depart2
fname          joice,tim,Amy
department_id  depart3
fname          rose            -----null被忽略

3 row(s) retrieved.

用例4： measure_expr 为列表达式

> select listagg(case when employee_id <200 then 100 when employee_id>200 and employee_id <300 then 200 else 300 end, '|') within group(order by employee_id) fname  from employees group by department_id;

FNAME  100|100
FNAME  300|300
FNAME  200|200|200
3 row(s) retrieved.

用例5： order_by_clause 表达式支持多个列名

> select department_id, listagg( employee_name, ',') within group(order by department_id,employee_name) fname  from employees group by department_id ;

DEPARTMENT_ID  depart1
FNAME          carry,sa
DEPARTMENT_ID  depart3
FNAME          rose
DEPARTMENT_ID  depart2
FNAME          Amy,joice,tim
3 row(s) retrieved.

依据department_id分组后，再按照department_id,employee_name排序来合并字符串。

通过本文的介绍，我们可以看到南大通用GBase8s数据库的LISTAGG函数能够帮助我们有效地合并多行数据为一个字符串列表，同时提供排序和自定义分隔符的功能。无论是在数据报告、数据分析还是数据展示中，LISTAGG函数都能发挥重要的作用。希望本文能够帮助您更好地理解和使用LISTAGG函数，提升您的数据库操作效率。

更多关于LISTAGG函数的详细信息，可以参考《GBase 8s V8.8 SQL 指南：语法.pdf》。如果您在使用过程中遇到任何问题，欢迎联系我们的技术支持团队，我们将竭诚为您提供帮助。

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

avatar
文章
阅读
粉丝