
LISTAGG 関数を使用した値のグループ化
このチュートリアルでは、LISTAGG 関数の使用方法を示します。この関数を使用して、グループ内の一連のストリング値を 1 つのストリングに集約できます。 このチュートリアルでは、LISTAGG 関数を取り上げ、実用的な例を示し、よく似た集約関数 XMLAGG と比較してみます。
ソフトウェア要件と前提条件
- FL 501 Db2® 12 機能レベル501以上を有効にするには、APAR PI70535 用のPTFを適用する必要があります。注: APAR を適用するだけでなく、ACTIVATE コマンドを発行して、明示的に機能レベルをアクティブにする必要があります。 以下に例を示します。
-db2a ACTIVATE FUNCTION LEVEL (V12R1M501)
V12R1M501
に対してアプリケーションのバインドまたは再バインドを実行しなければならない場合もあります。V12R1M501
に対して DSNTEP3.DSNTEP3.(*) を再バインドする例を以下に示します。REBIND PACKAGE(DSNTEP3.DSNTEP3.(*)) APPLCOMPAT(V12R1M501)
LISTAGG について
LISTAGG 関数を使用して、グループ内の一連のストリング値を 1 つのストリングに集約できます。そのために、'WITHIN GROUP' 節で指定されている順序に基づいてストリング式の値を追加します。
- LISTAGG を単一セットの集約関数として実行する場合は、すべての行で操作を実行して 1 つの出力行を返します。
- LISTAGG をグループ・セットの集約関数として実行する場合は、GROUP BY 節で定義されているグループごとに操作を実行して 1 つの出力行を返します。
LISTAGG の構文を以下の図に示します。
入力の string-expression は、ストリング (文字、グラフィック、バイナリー)、数値データ・タイプ、LOB のいずれかでなければなりません。 数値引数と LOB は、暗黙的キャスト (数値から VARCHAR、CLOB から最大長 32704 の VARCHAR、DBCLOB から最大長 16352の VARGRAPHIC、BLOB から最大長 32704の VARBINARY) によってサポートされています。
区切り文字として、リテラル、特殊レジスター、変数のいずれか、またはリテラル、特殊レジスター、変数のいずれかに基づく式を使用できます。ただし、非決定論的関数または外部アクションを実行する関数が含まれている式は使用できません。
LISTAGG の結果データ・タイプは、入力データ・タイプによって決まります。その点を以下の表にまとめています。
入力データ・タイプ | 出力データ・タイプ |
---|---|
CHAR(n) または VARCHAR(n) | VARCHAR |
GRAPHIC(n) または VARGRAPHIC(n) | VARGRAPHIC |
BINARY(n) または VARBINARY(n) | VARBINARY |
例:LISTAGGを使用して、同じ部署に所属する従業員のリストを出力する
EMPLSという名前のテーブルがあり、そのテーブルには従業員の名前、従業員ID、部署ID、従業員の生年が含まれているとします。
DROP TABLE EMPLS;
CREATE TABLE EMPLS
(Dept_ID INT,
E_Name VARCHAR(20),
EMP_ID INT,
Birth INT);
INSERT INTO EMPLS VALUES(10, 'Jack', 0012, 1983);
INSERT INTO EMPLS VALUES(10, 'Lily', 0015, 1990);
INSERT INTO EMPLS VALUES(20, 'Tom', 0019, 1983);
INSERT INTO EMPLS VALUES(20, 'Bob', 0022, 1976);
INSERT INTO EMPLS VALUES(20, 'Frank', 0004, 1983);
INSERT INTO EMPLS VALUES(20, 'Tom', 0014, 1985);
INSERT INTO EMPLS VALUES(30, 'Jerry', 0028, 1991);
INSERT INTO EMPLS VALUES(30, 'Chris', 0021, 1981);
INSERT INTO EMPLS VALUES(30, 'Jill', 0002, 1984);
INSERT INTO EMPLS VALUES(30, 'Jerry', 0031, 1984);
INSERT INTO EMPLS VALUES(30, 'Allan', 0006, 1995);
Dept_ID | E_Name | Emp_ID | Birth |
---|---|---|---|
10 | ジャック | 0012 | 1983 |
10 | Lily | 0015 | 1990 |
20 | Tom | 0019 | 1983 |
20 | Bob | 0022 | 1976 |
20 | Frank | 0004 | 1983 |
20 | Tom | 0014 | 1985 |
30時間まで | Jerry | 0028 | 1991 |
30時間まで | Chris | 0021 | 1981 |
30時間まで | Jill | 0002 | 1984 |
30時間まで | Jerry | 0031 | 1984 |
30時間まで | Allan | 0006 | 1995 |
今度は、このテーブルから、同じ部署に所属する全従業員の名前を、従業員IDと生年月日順に昇順で1つの行に出力したいと仮定します。
1 つの方法は、まず従業員の名前と部門を表から抽出するカーソルを定義することです。 次に、カーソルからデータを取得し、アプリケーションロジックを使用して同じ部署の従業員を特定し、その名前を結合することができます。 この方法も確かに可能ですが、かなりの労力が必要です。
もう 1 つの方法を以下の SQLPL にまとめています (# はこの場合の SQL 終了文字です)。
DROP TABLE RESULT#
CREATE TABLE RESULT(DEPT_ID INT, EMP_NAMES VARCHAR(100))#
DROP PROCEDURE AggEmpName#
CREATE PROCEDURE AggEmpName ()
BEGIN
DECLARE STMT VARCHAR(50) DEFAULT 'No name';
DECLARE PRE_Dept_ID INT;
SET STMT= '';
SET PRE_Dept_ID= 0;
FOR MYC CURSOR FOR
SELECT E_Name, Dept_ID
FROM EMPLS
ORDER BY Dept_ID, Birth, Emp_ID
DO
IF PRE_Dept_ID = 0 THEN
SET PRE_Dept_ID = Dept_ID;
SET STMT = STMT ||E_Name||';';
ELSE
IF PRE_Dept_ID = Dept_ID THEN
SET STMT = STMT ||E_Name||';';
ELSE
INSERT INTO RESULT VALUES (PRE_Dept_ID,STMT);
SET PRE_Dept_ID = Dept_ID;
SET STMT= '';
SET STMT = STMT ||E_Name||';';
END IF;
END IF;
END FOR;
INSERT INTO RESULT VALUES (PRE_Dept_ID,STMT);
END#
CALL AggEmpName () #
SELECT * FROM RESULT#
まず、結果(同じ部署に属する従業員の名前)を格納するための結果テーブルを作成します。 SQLPL(AggEmpName
)の本体内部では、 STMT
で同じ部署の従業員名を連結します。 部署IDが変更された場合、結合した文字列を部署IDとともにRESULTテーブルに挿入します。 次に、 STMT
をリセットし、次の部門の名前を連結し始めます。 以下のような結果になります。
DEPT_ID EMP_NAMES
10 Jack;Lily;
20 Bob;Frank;Tom;Tom;
30 Chris;Jill;Jerry;Jerry;Allan;
3 record(s) selected
LISTAGG 関数を使用すると、この作業が非常にシンプルになります。 次のような照会を行ったとします。
SELECT Dept_ID,
LISTAGG(ALL E_Name, '; ')
WITHIN GROUP (ORDER BY Birth, Emp_ID)
AS Name_List
FROM EMPLS
GROUP BY Dept_ID;
分かりやすい構文です。 各部署(GROUP BY Dept_ID)で、従業員の名前を集約し、BirthとEmp_IDでソートします。次の結果に示されているように:
Dept_ID | Name_List |
---|---|
10 | Jack; Lily |
20 | Bob; Frank; Tom; Tom |
30時間まで | Chris; Jill; Jerry; Jerry; Allan |
例:LISTAGGによって作成された各部署グループから重複する従業員名を削除する
グループごとに重複名を削除したい場合は、DISTINCT キーワードを使用します (以下の照会を参照)。
SELECT Dept_ID,
LISTAGG(DISTINCT E_Name, '; ')
WITHIN GROUP (ORDER BY E_Name, Birth, Emp_ID)
AS Name_List
FROM EMPLS
GROUP BY Dept_ID;
Dept_ID | Name_List |
---|---|
10 | Jack; Lily |
20 | Bob; Frank; Tom |
30時間まで | Allan; Chris; Jerry; Jill |
このように、独自のアプリケーション・ロジックを実装するよりも、はるかに使いやすく、保守も容易です。
SELECT Dept_ID,
LISTAGG(DISTINCT E_Name, '; ')
WITHIN GROUP (ORDER BY Birth, Emp_ID)
AS Name_List
FROM EMPLS
GROUP BY Dept_ID;
-214, ERROR: AN EXPRESSION IN THE FOLLOWING POSITION, OR STARTING WITH 1 IN THE ORDER BY CLAUSE IS NOT
VALID. REASON CODE = 8
RC 8 DISTINCT is specified in the LISTAGG function and the first argument expression cannot be matched exactly
with the first sort key expression of the ORDER BY specified in the WITHIN GROUP clause. This reason code occurs
only when clause-type is ORDER BY.
このルールが必要なのは、非決定論的な結果を回避するためです。
例えば、表に以下のデータが含まれているとします (一部の列を省略しています)。 Tom という名前の従業員が 2 人いて、生まれた年がそれぞれ異なっています。
E_Name | Birth |
---|---|
Tom | 1983 |
Bob | 1986 |
Tom | 1990 |
Jill | 1980 |
Jill, Tom, Bob
Jill, Bob, Tom
-214の制限がない場合、 Db2 はどの行(Tom 1983またはTom 1990)が重複しており、削除する必要があるのかを特定できません。
LISTAGG と XMLAGG の比較
XML のことをよく理解している人なら、XMLAGG という XML の集約関数のことも既に知っているかもしれません。 XMLAGG は、Db2 9 for z/OS で導入されました。
同じ(または同様の)結果を得るために、XMLAGGでできる最も近いことは次のとおりです
SELECT DEPT_ID,
XMLAGG(XMLTEXT(E_NAME || '; ')
ORDER BY Birth, EMP_ID)
AS Name_List
FROM EMPLS
GROUP BY Dept_ID;
XMLAGG関数はXMLデータ型のみを引数にとるので、E_NAMEにXMLTEXT()を適用し、';'を区切り文字として連結する必要があります。 以下のような結果になります。
DEPT_ID NAME_LIST
10 Jack; Lily;
20 Bob; Frank; Tom; Tom;
30 Chris; Jill; Jerry; Jerry; Allan;
3 record(s) selected
- XMLAGG 内で重複を排除することはできません。 LISTAGGでは、DISTINCTキーワードを使用できます。
- XMLAGG の戻りデータ・タイプは常に XML です。 そのため、結果の使用方法によっては、XMLAGG の出力をアプリケーションで必要なデータ・タイプにキャストすることが必要になります。
- LISTAGG の VARCHAR 出力は 32704 に制限されています。 XMLLAGG にはこうしたサイズ制限がありません。
- XMLAGG と LISTAGG の出力をよく見ると、XMLAGG 出力 (NAME_LIST) の終わりには ; (区切り文字) がありますが、LISTAGG 出力の終わりにはありません。 XMLAGGステートメントでは、連結演算子(||)を使用して「手動」で区切り文字が追加されますが、LISTAGGの区切り文字は「組み込み」であり、トークンがもう存在しない場合は追加されません。
