11gR2からGROUP_CONCAT(複数行をまとめて1列に)が可能に
以前にMySQLのGROUP_CONCAT関数についてのエントリーを書いていたのですが、あれから5年。
1対多の関係を持つ親子テーブルがあった場合、1つの主キーに対して結合結果が複数行になることはよくあると思いますが、
MySQLでは上記の関数を使うことで複数行内の列をひとつの文字列としてまとめることができます。
・
・
・Oracleでも出来なくはないんですが、かなり面倒なんですよねぇ。
Oracleでも11g R2から同様の集計関数 LISTAGGが追加されており、容易に対応が実施できるようになりました。
まず私の目を惹いたのは、LISTAGG(LIST AGGREGATE)です。簡単にいうと、グループ化された行に存在する値を集約し、1つのリストを生成します。
従業員のリストを例にとってみましょう。LISTAGGはCOUNTの機能のように、この表を集約し、すべての文字列を1つの大きな文字列に連結してくれます。
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 部に所属しています。
ここで、各社員が所属する部署をカンマ区切で欲しい場合は、外部結合を用いて以下のような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