SQL实现多行记录聚合成一行

Oracle数据分组与聚合技术升级：从Row_number到Listagg函数的应用

最新推荐文章于 2024-03-23 12:02:12 发布

原创最新推荐文章于 2024-03-23 12:02:12 发布 · 970 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #listagg

Oracle 专栏收录该内容

126 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中，从使用row_number()over函数到引入Listagg函数进行数据分组与聚合的方法转变。通过实例展示了如何将相同deptno的不同ename记录聚合为一行，以及如何使用Listagg函数实现数据的按字段排序与分组，包括在不同上下文中应用Listagg函数的具体步骤和效果。同时，还展示了如何利用over子句进一步细化聚合结果。

将一个字段的多行记录按照另一个字段值进行分组，组合成一行记录。新组合成的一行记录可以使用特定字符如逗号加以区分。

在以前的文档中，我写好一个方法( http://mikixiyou.iteye.com/blog/1489127 )。使用row_number() over 函数。那是在Oracle 10g中使用到的，在11g中提供一个新函数listagg来实现该功能，简单易用。

在Oracle 10g中，如果将scott.emp中字段deptno相同的不同ename的记录，从多行记录聚合转换成一行。这是需要自己开发才能完成的。
scott.emp表中原始记录如下：
   DEPTNO ENAME
--------- ----------
       10 CLARK
       10 KING
       10 MILLER
       20 ADAMS
       20 FORD
       20 JONES

聚合后的结果如下：

   DEPTNO AGGREGATED_ENAMES
--------- -------------------------
       10 CLARK,KING,MILLER
       20 ADAMS,FORD,JONES

自己开发实现的程序如下：

select deptno, substr(max(sys_connect_by_path(ename, ',')), 2) as employees
  from (select deptno,
               ename,
               row_number() over(partition by deptno order by ename) as rown
          from emp)
 start with rown = 1
connect by prior deptno = deptno
       and prior rown = rown - 1
 group by deptno;

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1627981 )

在11g中，提供了一个新函数 listagg，可以实现这种功能。

listagg的语法如下：

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

WITHIN GROUP 是关键字，必须加。
ORDER BY 也是关键字，必须加。
OVER (PARTITION BY)是可选子句，加上能将聚合函数变成分析函数。

实现该功能的程序如下：

SELECT deptno,
       LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
  FROM emp
 GROUP BY deptno;

使用over子句将该函数变成分析函数。

SELECT deptno,
       ename,
       hiredate,
       LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees
  FROM emp;

将每个员工的信息都列出来，最后一项是该员工所在部门所有员工的员工排列，并且是按照雇佣时间排列的。

得到的结果如下：

     DEPTNO ENAME      HIREDATE    EMPLOYEES
--------- ---------- ----------- -------------------------------------
        10 CLARK      09/06/1981 CLARK,KING,MILLER
        10 KING       17/11/1981 CLARK,KING,MILLER
        10 MILLER     23/01/1982 CLARK,KING,MILLER
        20 SMITH      17/12/1980 SMITH,JONES,FORD,SCOTT,ADAMS
        20 JONES      02/04/1981 SMITH,JONES,FORD,SCOTT,ADAMS
        20 FORD       03/12/1981 SMITH,JONES,FORD,SCOTT,ADAMS
        20 SCOTT      19/04/1987 SMITH,JONES,FORD,SCOTT,ADAMS
        20 ADAMS      23/05/1987 SMITH,JONES,FORD,SCOTT,ADAMS
        30 ALLEN      20/02/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 WARD       22/02/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 BLAKE      01/05/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 TURNER     08/09/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 MARTIN     28/09/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 JAMES      03/12/1981 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES