LISTAGG関数
出題されやすいグループ関数には、表1の他にLISTAGG関数があります。
まずは次の検索結果を見てください。employees表から、DEPARTMENT_ID列が20あるいは60である人を求めています。
SQL> SELECT department_id,employee_id,last_name
2 FROM employees
3 WHERE department_id IN (20,60)
4 ORDER BY department_id,employee_id;
DEPARTMENT_ID EMPLOYEE_ID LAST_NAME
------------- ----------- ---------------
20 201 Hartstein
20 202 Fay
60 103 Hunold
60 104 Ernst
60 105 Austin
60 106 Pataballa
60 107 Lorentz
7行が選択されました。
これを該当者ではなく、該当者の人数を求めるように直すと次のとおりになります。
SQL> SELECT department_id,COUNT(*)
2 FROM employees
3 WHERE department_id IN (20,60)
4 GROUP BY department_id
5 ORDER BY department_id;
DEPARTMENT_ID COUNT(*)
------------- ----------
20 2
60 5
この2つの結果を統合できたらいいなって思いませんか？ つまり、「部門番号20には2人所属していて、それはHartsteinとFayだよ」という結果を1つのSQL文で求められたらってことです。それを実現するのがLISTAGG関数です。
LISTAGG(メジャー列名,'区切り文字') WITHIN GROUP (ORDER BY 並べ替え基準列名)
| 引数 | 説明 |
|---|---|
| メジャー列名 | リストする列名 |
| 区切り文字 | フィールドに値をリストするときのリスト間を区切る記号 |
| 並べ替え基準列名 | 値をリストするときの並べ替え基準列 |
LISTAGG関数の説明について、マニュアル（Oracle Database SQL言語リファレンス 12cリリース1 (12.1)）には「指定されたメジャーについて、ORDER BY句に指定された各グループ内でデータを順序付け、メジャー列の値を結合します」と書いてあるのですが、よくわかりませんね。
百聞は一見にしかず。実行例を見てみましょう。
SQL> SELECT department_id,COUNT(*)
2 ,LISTAGG(last_name,',') WITHIN GROUP (ORDER BY employee_id) AS last_name
3 FROM employees
4 WHERE department_id IN (20,60)
5 GROUP BY department_id;
DEPARTMENT_ID COUNT(*) LAST_NAME
------------- ---------- ----------------------------------------
20 2 Hartstein,Fay
60 5 Hunold,Ernst,Austin,Pataballa,Lorentz
LISTAGG関数の第1引数「メジャー列名」は、実行例2では縦に複数行で出力されていた従業員名（LAST_NAME）です。この従業員名はグループごと（WITHIN GROUP）、つまりGROUP BY句で指定している部門番号（DEPARTMENT_ID）ごとにリストされます。リストされる従業員名は、第2引数「区切り文字」に指定されたカンマ（,）区切りで表示されます。また、リストする従業員名の並び順を決めなければなりませんが、それを指定しているのが第3引数「並べ替え基準列名」です。この例ではORDER BY employee_id、つまり従業員IDの昇順で並べるように指定しています。
LISTAGG関数に関する問題は、この引数の意味が理解できていれば正解できますよ。
この記事は参考になりましたか？
- めざせオラクルマスターホルダー！ Oracle DB 12c SQL基礎 弱点克服スクール連載記事一覧
- この記事の著者
-
林 優子（ハヤシ ユウコ）
日本オラクル株式会社の教育ビジネスのスタートアップを全面的に支援し、バージョン5の頃からOracleに携わるベテラン講師として知る人も多い。Oracle認定講師を表彰するExcellent Instructorを連続受賞。1ランク上のITスペシャリスト育成を目標に、データベース分野にとどまらず「プレゼンテーション」、「ロジカルシンキング」などのトレーニングも手がけている。著書に『オラクルマスター教科書』シリーズ（翔泳社）、『プロとしてのデータモデリング入門』（SBクリエイティブ）など。その他、雑誌執筆、著書・メディア出演も多数。
※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です
