[Oracle]拼接路径——LISTAGG和KEEP的用法

需求：

将以下路径明细

ROUTE_ID	ORIGIN	TARGET	ORDER
1	A	B	1
1	B	C	2
1	C	D	3
2	A	B	1
2	B	E	2

拼接成

ROUTE_ID	ROUTE
1	A->B->C->D
2	A->B->E

效果
 

分析：

每条路径明细中包含了起点和终点，我们拼接时只需要取每次的起点使用->连接在一起，再加上最后一行的终点即可。

实现：

1.使用LISTAGG拼接 ORIGIN 列

WITH ROUTE_DATA AS
 (SELECT 1 AS ROUTE_ID, 'A' AS ORIGIN, 'B' AS TARGET, 1 AS SEQ
    FROM DUAL
  UNION ALL
  SELECT 1, 'B', 'C', 2
    FROM DUAL
  UNION ALL
  SELECT 1, 'C', 'D', 3
    FROM DUAL
  UNION ALL
  SELECT 2, 'A', 'B', 1
    FROM DUAL
  UNION ALL
  SELECT 2, 'B', 'E', 2
    FROM DUAL)
SELECT ROUTE_ID,
       LISTAGG(ORIGIN, '->') WITHIN GROUP(ORDER BY SEQ) AS ROUTE
  FROM ROUTE_DATA
 GROUP BY ROUTE_ID;

得到结果：

	ROUTE_ID	ROUTE
1	1	A->B->C
2	2	A->B

2.使用KEEP，获取最后一行数据的 TARGET列并进行拼接

WITH ROUTE_DATA AS
 (SELECT 1 AS ROUTE_ID, 'A' AS ORIGIN, 'B' AS TARGET, 1 AS SEQ
    FROM DUAL
  UNION ALL
  SELECT 1, 'B', 'C', 2
    FROM DUAL
  UNION ALL
  SELECT 1, 'C', 'D', 3
    FROM DUAL
  UNION ALL
  SELECT 2, 'A', 'B', 1
    FROM DUAL
  UNION ALL
  SELECT 2, 'B', 'E', 2
    FROM DUAL)
SELECT ROUTE_ID,
       LISTAGG(ORIGIN, '->') WITHIN GROUP(ORDER BY SEQ) || '->' || MIN(TARGET) KEEP(DENSE_RANK LAST ORDER BY SEQ) AS ROUTE
  FROM ROUTE_DATA
 GROUP BY ROUTE_ID;

 得到结果：

	ROUTE_ID	ROUTE
1	1	A->B->C->D
2	2	A->B->E

现在已经达到了我们的效果。

附录：

1.LISTAGG的用法：

LISTAGG 是 Oracle 数据库中用于将多行数据合并为单个字符串的聚合函数。它通常用于将查询结果中的多个值连接成一个逗号分隔的字符串或其他分隔符。

下面是 LISTAGG 函数的基本用法：

LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY order_column) AS aggregated_column

• column_name：要合并的列名。
• delimiter：用于分隔合并后的值的分隔符，可以是字符串或表达式。
• order_column：可选项，指定排序的列名，以确保合并后的字符串顺序正确。
• aggregated_column：合并后的结果将放在此列中。

以下是一个示例，假设有一个名为 employees 的表，其中包含 employee_id 和 employee_name 列，你可以使用 LISTAGG 将员工姓名合并为逗号分隔的字符串：

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS employee_list
FROM employees
GROUP BY department_id;

上面的查询会按照 employee_id 的顺序，将每个部门中的员工姓名连接成一个逗号分隔的字符串，结果将显示每个部门的 department_id 和相应的员工列表。

请注意，LISTAGG 函数是 Oracle 特有的，如果你使用其他数据库管理系统，可能需要使用不同的方法来执行类似的操作。

2.KEEP的用法：

KEEP 子句结合 DENSE_RANK 是 Oracle 数据库中用于执行高级分析的强大工具。它允许你在查询结果中根据某些条件筛选行，然后应用聚合函数，如 SUM、MAX、MIN 等，以获取特定条件下的聚合值。

通常，DENSE_RANK 和 KEEP 一起使用，以获得在特定条件下的聚合值。以下是一个示例：

假设有一个名为 sales 的表，包含 product_id、sale_date 和 sale_amount 列。你想要查找每个产品的最高销售额，并且想要在查询结果中包括 product_id、sale_date 和最高销售额。

SELECT 
  product_id,
  MAX(sale_amount) KEEP (DENSE_RANK FIRST ORDER BY sale_amount DESC) AS max_sale_amount,
  FIRST_VALUE(sale_date) KEEP (DENSE_RANK FIRST ORDER BY sale_amount DESC) AS sale_date
FROM sales
GROUP BY product_id;

在上面的查询中：

• DENSE_RANK 用于排名每个产品的销售额，根据 sale_amount 降序排名。DENSE_RANK FIRST 表示我们只关心排名第一的行。
• KEEP 子句用于限定聚合函数（在此例中是 MAX）应该在哪些行上运行，即在 DENSE_RANK FIRST 排名第一的行上运行。
• 最终结果包括每个产品的 product_id、最高销售额 (max_sale_amount) 和对应的销售日期 (sale_date)。

这个查询将返回每个产品的最高销售额和对应的销售日期，而不仅仅是最高销售额的值。这是 DENSE_RANK 和 KEEP 子句的常见用法之一，用于获取在特定条件下的聚合值。