In database management, it is often necessary to merge data from multiple rows into a single string list for better data display and analysis. GBase database (GBase数据库) offers an aggregate function,
LISTAGG, which not only combines data but also allows sorting and custom delimiters. This article provides an in-depth introduction to theLISTAGGfunction, including its syntax, features, use cases, and considerations in practical applications.
Overview of the LISTAGG Function
The primary purpose of the LISTAGG aggregate function is to combine multiple rows of data into a single string list. This process includes both data merging and sorting to ensure logical ordering. Additionally, with a specified delimiter, we can define the separation format of the items in the list, accommodating various data display requirements.
Syntax and Use Cases of LISTAGG
Syntax
The syntax for the LISTAGG function is as follows:
LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (ORDER BY order_by_clause)
- measure_expr: The expression for the rows to be combined. This supports column names, constants, and expressions but cannot be omitted.
- delimiter: The separator string for rows, supporting one or multiple characters. If omitted, no delimiter is used.
-
order_by_clause: The expression within the
WITHIN GROUPclause determines the sort order of the data before aggregation. This step is essential forLISTAGGto merge strings in the specified sequence. Multiple column names are supported, and the sorting follows the order of columns specified from left to right. This cannot be omitted.
Additional Notes:
- The function returns a character-type value. If the length of the aggregated string exceeds the maximum allowed length in GBase8s (32765), an error will be raised.
- The resulting string from
LISTAGGdoes not includeNULLvalues.
Use Case Analysis
To better understand how to use the LISTAGG function, let's look at some practical examples.
Note: The examples below use GBase8s version GBase8sV8.8_TL_3.5.1_x86_64.
For demonstration, we will use a simple employees table with some sample data.
CREATE TABLE employees (
employee_id int,
department_id VARCHAR2(20),
employee_name VARCHAR2(20)
);
-- Insert 6 rows of data:
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.
Use Case 1: Without a GROUP BY clause (single group aggregation), data rows are treated as one large group. Here, data is merged based on employee_id in ascending order by default.
> select listagg( employee_name, ',') within group(order by employee_id ) fname from employees;
fname carry,sam,joice,tim,Amy,rose
1 row(s) retrieved.
Use Case 2: With a GROUP BY clause, the LISTAGG function provides all users within each department, sorted by 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.
Note: Like other aggregate functions, all projected columns must be included in the GROUP BY clause; otherwise, an error will occur.
> 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
Use Case 3: When there is a NULL value in the data, it is ignored and does not appear in the final string list.
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 is ignored
3 row(s) retrieved.
Use Case 4: Using an expression for 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.
Use Case 5: The order_by_clause expression supports multiple columns.
> 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.
Here, the list is grouped by department_id and sorted by both department_id and employee_name.
Through this overview, we can see how the LISTAGG function in the GBase8s database can help efficiently merge multiple rows into a string list, supporting sorting and custom delimiters. Whether in reporting, analysis, or data display, the LISTAGG function is highly useful. We hope this article enhances your understanding and usage of LISTAGG to improve database operation efficiency.
If you encounter any issues with the GBase database (GBase数据库), please contact our technical support team. We are here to help!
Top comments (0)