複数行をまとめて1行に集約（LISTAGG関数）

概要

以前にMySQLのGROUP_CONCAT関数についてのエントリーを書いていたのですが、
あれから5年。

1対多の関係を持つ親子テーブルがあった場合、1つの主キーに対して結合結果が複数行になることはよくあると思いますが、
MySQLでは上記の関数を使うことで複数行内の列をひとつの文字列としてまとめることができます。
Oracleでも出来なくはないんですが、かなり面倒なんですよねぇ。

Oracleでも11g R2から同様の集計関数 LISTAGGが追加されており、
容易に対応が実施できるようになりました。

LISTAGG

LISTAGG(集約対象の列名, セパレート文字列) WITHIN GROUP (order by 集約対象のソート指定列(指定なしの場合はnull))

公式リファレンス

公式のSQLリファレンスはこちらになります。

LISTAGGは、指定されたメジャーについて、
ORDER BY句に指定された各グループ内でデータを順序付け、
メジャー列の値を結合します。

• 単一セットの集計ファンクションとして使用すると、
  LISTAGGはすべての行に対して操作を行い、1つの出力行を戻します。
• グループ・セットの集計に使用すると、
  このファンクションはGROUP BY句に定義されたグループごとに操作を行い、
  そのグループごとに1つの出力行を戻します。
• 分析ファンクションとして使用すると、
  LISTAGGはquery_partition_clauseに指定された1つ以上の式に基づくグループに、
  問合せの結果セットを分割します。

使用例

では実際の使用例をまとめてみます。

スキーマ準備

前回のエントリーと同じく、まずテーブル定義ですね。社員が複数の部署に所属するような形式のテーブルを作成します。

スキーマの作成

CREATE TABLE DEPT(ID NUMBER(3), NAME VARCHAR2(10));
CREATE TABLE EMP(ID NUMBER(3), NAME VARCHAR2(10));
CREATE TABLE DEPT_EMP( EMPID NUMBER(3), DEPTID NUMBER(3));

サンプルデータ作成

ここに以下のデータを挿入。DEPT と EMP は多対多の関係であるため、
対応表が DEPT_EMP になります。

サンプルデータ作成

INSERT INTO EMP VALUES ( 1, 'JOHN');
INSERT INTO EMP VALUES ( 2, 'SCOTT');
INSERT INTO EMP VALUES ( 3, 'SMITH');
INSERT INTO DEPT VALUES ( 1, 'MARKET');
INSERT INTO DEPT VALUES ( 2, 'HRM');
INSERT INTO DEPT VALUES ( 3, 'SALES');
INSERT INTO DEPT_EMP VALUES(1, 1);
INSERT INTO DEPT_EMP VALUES(1, 2);
INSERT INTO DEPT_EMP VALUES(2, 2);
INSERT INTO DEPT_EMP VALUES(3, 1);
INSERT INTO DEPT_EMP VALUES(3, 2);
INSERT INTO DEPT_EMP VALUES(3, 3);

データを SQL で取得すると以下のような形になります。

SELECT DEPT_EMP.EMPID, EMP.NAME, DEPT.NAME
FROM DEPT_EMP
     LEFT JOIN EMP ON DEPT_EMP.EMPID=EMP.ID
     LEFT JOIN DEPT ON DEPT_EMP.DEPTID=DEPT.ID
ORDER BY DEPT_EMP.EMPID;

     EMPID NAME             NAME
---------- ---------------- ---------------
         1 JOHN             HRM
         1 JOHN             MARKET
         2 SCOTT            HRM
         3 SMITH            SALES
         3 SMITH            MARKET
         3 SMITH            HRM

上記のように、JOHNさんは HRM と MARKET 部に所属、
SMITH さんは SALES と HRM と MARKET 部に所属しています。

LISTAGG の使用と結果

ここで、各社員が所属する部署をカンマ区切で欲しい場合は、
外部結合を用いて以下のようなSQLを発行します。

SELECT EMP.NAME,
       LISTAGG(DEPT.NAME, ',') WITHIN GROUP (order by DEPT.NAME) AS '部署名連結'
FROM DEPT_EMP
     LEFT JOIN EMP ON DEPT_EMP.EMPID=EMP.ID
     LEFT JOIN DEPT ON DEPT_EMP.DEPTID=DEPT.ID
GROUP BY DEPT_EMP.EMPID, EMP.NAME
ORDER BY DEPT_EMP.EMPID;

ここでは集約対象を DEPT.NAME （部署名）、
集約時の並び順を DEPT.NAME （部署名）で指定しています。
そのため、集約した「部署名連結」列では、
アルファベットで一番最初に来る「HRM」が先頭に、「SALES」が末尾になります。

NAME            部署名連結            
--------------- ----------------------
JOHN            HRM,MARKET
SCOTT           HRM
SMITH           HRM,MARKET,SALES

「部署名連結」列内の並び順を DEPT.ID （部署ID）にしたい場合は
LISTAGG の ORDER BY に DEPT.ID を指定すればOK。

SELECT EMP.NAME,
       LISTAGG(DEPT.NAME, ',') WITHIN GROUP (order by DEPT.ID) AS '部署名連結'
FROM DEPT_EMP
     LEFT JOIN EMP ON DEPT_EMP.EMPID=EMP.ID
     LEFT JOIN DEPT ON DEPT_EMP.DEPTID=DEPT.ID
GROUP BY DEPT_EMP.EMPID, EMP.NAME
ORDER BY DEPT_EMP.EMPID;

NAME            部署名連結            
--------------- ----------------------
JOHN            MARKET,HRM
SCOTT           HRM
SMITH           MARKET,HRM,SALES

SUMやAVG、MAXなどと同じく、グループ集計関数であるため、
GROUP BY 句に載っていない列は SELECT 句で列指定出来ないため注意。