codecamp

OceanBase LISTAGG

LISTAGG 函数用于列转行,LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序,然后合并度量列的值。作为单个集合的聚合函数,LISTAGG 对所有行进行操作并返回单个输出行。作为组集聚合,LISTAGG 将对 GROUP BY 子句定义的每个组进行操作并返回输出行。作为分析函数,LISTAGG 基于 query_partition_clause 中的一个或多个表达式将查询结果集分为几组。

语法

LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
[OVER query_partition_clause]

作为分析函数使用时,您需要使用窗口函数的完整语法,它对一组行的集合进行计算并返回多个值。作为聚合函数使用时,该函数对一组行的集合进行聚合计算,结果只能返回一个值,此时不需要加 OVER 关键字。

参数

参数

说明

OVER

使用 OVER 子句定义窗口进行计算。

measure_expr

可以是任何表达式。度量列中的空值将被忽略。

delimiter

指定用于分隔度量值的字符串。此子句是可选的,默认为 NULL。

返回类型

如果度量列是 RAW 的,则返回数据类型为 RAW,否则返回值为 VARCHAR2 型。

示例

分析函数示例

建表 employees,并向里面插入数据,执行以下语句:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(70, 100, 'Weiss',  '2019-07-11',13500);     
INSERT INTO employees VALUES(90, 100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(90,100, 'Partners',  '2018-12-01',14000);

查询 2019 年 10 月 10 日之前雇用的雇员,以及该雇员的部门,雇用日期以及该部门中的其他雇员。执行以下语句:

SELECT department_id "Dept", hiredate "Date", last_name "Name",LISTAGG(last_name, '; ') WITHIN GROUP 
(ORDER BY hiredate, last_name) OVER (PARTITION BY department_id) as "Emp_list"
FROM employees WHERE hiredate < '2019-10-10' ORDER BY "Dept", "Date", "Name";

查询结果如下:

+------+------------+-----------+---------------------+
| Dept | Date       | Name      | Emp_list            |
+------+------------+-----------+---------------------+
|   30 | 2017-07-01 | Raphaely  | Raphaely; De Haan   |
|   30 | 2018-05-01 | De Haan   | Raphaely; De Haan   |
|   40 | 2017-07-21 | Errazuriz | Errazuriz           |
|   50 | 2017-07-22 | Raphaely  | Raphaely; Hartstein |
|   50 | 2019-05-01 | Hartstein | Raphaely; Hartstein |
|   70 | 2019-07-11 | Weiss     | Weiss               |
|   90 | 2018-12-01 | Partners  | Partners; Russell   |
|   90 | 2019-10-05 | Russell   | Partners; Russell   |
+------+------------+-----------+---------------------+

聚合函数示例

建表 employees,并向里面插入数据,执行以下语句:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(30, 100, 'Errazuriz', '2017-07-01', 1400);
INSERT INTO employees VALUES(30, 100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'Weiss',  '2019-07-01',13500);     
INSERT INTO employees VALUES(30, 100, 'Russell', '2019-07-01', 13000);
INSERT INTO employees VALUES(30,100, 'Partners',  '2018-12-01',14000);

查询第 30 部门的所有员工,并按雇用日期和姓氏排序。执行以下语句:

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hiredate, last_name)  as "Emp_list", 
MIN(hiredate) as "Earliest" FROM employees WHERE department_id = 30;

查询结果如下:

+-----------------------------------------------------------------------------+------------+
| Emp_list                                                                    | Earliest   |
+-----------------------------------------------------------------------------+------------+
| Errazuriz; Raphaely; Raphaely; De Haan; Partners; Hartstein; Russell; Weiss | 2017-07-01 |
+-----------------------------------------------------------------------------+------------+
OceanBase KEEP
OceanBase MAX
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

OceanBase 控制台指南

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 客户端版 ODC

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase SQL 参考(MySQL 模式)

OceanBase SQL 参考(Oracle 模式)

OceanBase 基本元素

OceanBase 数据库对象

OceanBase 函数

OceanBase 单行函数

OceanBase 返回数字的字符串函数

OceanBase 通用比较函数

OceanBase 编码解码函数

OceanBase SQL 调优指南

OceanBase 相关协议

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }