Oracle listunagg函数

在本文中，我们将介绍Oracle数据库中的listunagg函数。listunagg函数是Oracle 19c及以上版本中引入的新函数，用于对聚合数据进行反解析，将单个聚合值解析为原始行。

什么是listunagg函数？

listunagg函数是用于将聚合数据反解析的函数。在实际应用中，通常会使用聚合函数如SUM、COUNT等对数据进行统计和汇总。而通过listunagg函数，我们可以反向解析这些聚合值，将它们还原为原始行。

listunagg函数的语法

listunagg函数的语法如下：

LISTAGG(expr, delimiter) WITHIN GROUP (ORDER BY order_by_columns) listunagg_alias [ON OVERFLOW {ERROR|TRUNCATE|WORD_WRAP|ELLIPSIS}]

expr: 需要被解析的聚合值或列名
delimiter: 字符串，用于分隔结果集中的每个解析项
order_by_columns: 用于排序的列名，多个列名使用逗号分隔
listunagg_alias: 解析后的结果集的列名
ON OVERFLOW: 用于指定如果解析结果集过大时的处理方式，可选参数有ERROR、TRUNCATE、WORD_WRAP和ELLIPSIS

listunagg函数的示例

为了更好地理解listunagg函数的使用方法，我们来看几个示例。

示例1：将聚合值解析为原始行

假设有一个包含学生姓名的表格，我们进行了分组并使用listagg函数对姓名进行了聚合。现在，我们希望通过listunagg函数将聚合值解析为原始行。

首先，我们创建一个名为students的表格来存储学生信息：

CREATE TABLE students (
  student_id NUMBER,
  student_name VARCHAR2(50),
  class_id NUMBER
);

然后，我们向students表格中插入一些学生信息：

INSERT INTO students (student_id, student_name, class_id)
VALUES (1, 'Alice', 1);

INSERT INTO students (student_id, student_name, class_id)
VALUES (2, 'Bob', 1);

INSERT INTO students (student_id, student_name, class_id)
VALUES (3, 'Charlie', 2);

INSERT INTO students (student_id, student_name, class_id)
VALUES (4, 'David', 2);

INSERT INTO students (student_id, student_name, class_id)
VALUES (5, 'Emily', 3);

现在，我们使用listagg函数对学生姓名进行聚合，并将结果存储在名为students_list的新表格中：

CREATE TABLE students_list AS
SELECT class_id, LISTAGG(student_name, ',') WITHIN GROUP (ORDER BY student_id) AS students
FROM students
GROUP BY class_id;

接下来，我们使用listunagg函数将students_list表格中的聚合值解析为原始行：

SELECT class_id, student_name
FROM students_list, TABLE(listunagg(students, ',')) s
ORDER BY class_id, student_name;

运行以上SQL语句后，我们会得到以下结果：

CLASS_ID    STUDENT_NAME
1           Alice
1           Bob
2           Charlie
2           David
3           Emily

从以上结果中可以看出，listunagg函数将聚合值解析为原始行，使我们能够按照原始数据的形式获取经过聚合的数据。

示例2：设置解析项的分隔符和排序

在listunagg函数中，我们可以指定解析项的分隔符和排序方式。如果不指定排序方式，默认会按照原始行的顺序解析。

假设我们有一个存储订单信息的表格，我们想要将每个订单的商品名称解析为原始行，并按照价格进行排序。

首先，我们创建一个名为orders的表格来存储订单信息：

CREATE TABLE orders (
  order_id NUMBER,
  product_name VARCHAR2(50),
  price NUMBER
);

然后，我们向orders表格中插入一些订单信息：

INSERT INTO orders (order_id, product_name, price)
VALUES (1, 'Apple', 5);

INSERT INTO orders (order_id, product_name, price)
VALUES (1, 'Banana', 3);

INSERT INTO orders (order_id, product_name, price)
VALUES (2, 'Orange', 2);

INSERT INTO orders (order_id, product_name, price)
VALUES (2, 'Grape', 4);

INSERT INTO orders (order_id, product_name, price)
VALUES (2, 'Peach', 6);

现在，我们使用listagg函数对每个订单的商品名称进行聚合，并将结果存储在名为orders_list的新表格中：

CREATE TABLE orders_list AS
SELECT order_id, LISTAGG(product_name, ',') WITHIN GROUP (ORDER BY price) AS products
FROM orders
GROUP BY order_id;

接下来，我们使用listunagg函数将orders_list表格中的聚合值解析为原始行，并按照价格从低到高进行排序：

SELECT order_id, product_name, price
FROM orders_list, TABLE(listunagg(products, ',')) o
ORDER BY price;

运行以上SQL语句后，我们会得到以下结果：

ORDER_ID    PRODUCT_NAME    PRICE
2           Orange          2
1           Banana          3
2           Grape           4
1           Apple           5
2           Peach           6

从以上结果中可以看出，listunagg函数根据指定的排序方式，将聚合值解析为原始行，并按照价格从低到高进行排序。