LISTAGGのスカラー的用法
SQL LISTAGG関数は、ポケットのように身近に使える関数です。つい最近、ドロップダウン ボックスに取り込むコンマ区切り値（CSV）のストリングを構築するのにLISTAGG関数を使用しました。カーソルとループよりはるかに効率的です。OracleではLISTAGGが集約およびスカラーの両方の目的で使用できるということをどこかで読んだことがあります。Db2では、スカラー的用法はサポートされていませんが、同じことを別のやり方で行う方法があります。
ここで紹介しようとしていることを理解しやすくするために、Ben Brumm氏による記事「 Oracle LISTAGG Function Usage, Tips & Examples（Oracle LISTAGGの使い方、ヒント、例）」の「Example 7」をご参照ください。もちろん、有益な情報が豊富なので、記事全体のご一読もお勧めです。
IBM Knowledge Centerでは、Db2 for iでのLISTAGGの役割について非常に明確に記載されています。 LISTAGGについて説明している7.4版のページには規則が1つだけあり、その規則では、「LISTAGGをOLAP指定の一部として使用することはできません」とされています。しかし、そのことは、同じようなことを別のやり方で実行できないという意味ではありません。
説明のために、従業員情報のデータベースをセットアップしましょう。
declare global temporary table depts
(ID dec(3), Name char(30));
insert into session.depts values
( 1, 'Accounting'),
( 2, 'Production'),
( 3, 'Research & Development'),
( 4, 'Quality Assurance'),
( 5, 'Customer Service'),
( 6, 'Purchasing'),
( 7, 'Information Technology');
declare global temporary table emps
(ID dec(3), Last char(12), First char(12), DeptID dec(3));
insert into session.emps values
( 1, 'Dover', 'Ben', 2),
( 2, 'Butler', 'Peanut', 7),
( 3, 'Makit', 'Willie', 2),
( 4, 'Wont', 'Betty', 2),
( 5, 'Kake', 'Patty', 7),
( 8, 'De Lion', 'Dan', 1);では、ここでLISTAGGを使用してみます。以下のシンプルな照会では、すべての部署名から1つのCSVストリングが構築されます。
select listagg(trim(d.Name),',')
within group(order by d.Name) as list
from session.depts as d
LIST
================================================================
Accounting,Customer Service,Information Technology,Production,Purchasing,Quality Assurance,Research & Developmentブラウザでコードが折り返して表示されるとしたらご容赦ください。
GROUP BY節はないので、結果セット全体が1つのグループとして扱われています。ドロップダウン ボックスをロードするのに使用したのは、このような照会でした。 もうひとつ試してみましょう。
select d.Name,
listagg(trim(e.Last),',')
within group(order by e.Last) as Workers
from session.emps as e
join session.depts as d
on e.DeptID = d.ID
group by d.Name, d.ID
order by d.Name
Name Workers
======================= ================
Accounting De Lion
Information Technology Butler,Kake
Production Dover,Makit,Wontこの例にはGROUP BYが あります 。LISTAGGは各部署の従業員を取得します。この照会にはORDER BY節が2つあります。1つ目のORDER BYは、部署の従業員を姓でソートします。2つ目のORDER BYは、結果セットをソートします。
どちらの照会も要約照会です。結果セットは、非グループ化列（フィールド）を持つことができません。非グループ化列が必要な場合は、LISTAGGで詳細照会を使用する必要があります。LISTAGGはスカラー関数ではないので、次のように、列に組み込むことができます。
select e.First, e.Last, e.DeptID,
(select listagg(trim(x.Last),',') within group(order by x.Last)
from session.emps as x
where x.DeptID = e.DeptID) as Workers
from session.emps as e
order by e.Last
First Last DeptID Workers
======== ======== ====== ================
Peanut Butler 7 Butler,Kake
Dan De Lion 1 De Lion
Ben Dover 2 Dover,Makit,Wont
Patty Kake 7 Butler,Kake
Willie Makit 2 Dover,Makit,Wont
Betty Wont 2 Dover,Makit,Wont4番目の列のSELECTステートメントは、結果セットの当該行にある部署のすべての従業員を選択します。EMPSは、当該行にある部署について、2回、照会されます。結果は、行ごとに集約データがある詳細レポートとなります。
状況によっては、当該行の従業員をリストから削除したほうが適切な場合もあるかもしれません。
select e.First, e.Last, e.DeptID,
(select listagg(trim(x.Last),',') within group(order by x.Last)
from session.emps as x
where x.DeptID = e.DeptID
and x.ID <> e.ID) as Coworkers
from session.emps as e
order by e.Last
First Last DeptID Coworkers
======== ======== ====== ==========
Peanut Butler 7 Kake
Dan De Lion 1 (null)
Ben Dover 2 Makit,Wont
Patty Kake 7 Butler
Willie Makit 2 Dover,Wont
Betty Wont 2 Dover,Makitこの照会は、各行の従業員の同僚をリストすることになります。また、4番目の列のSELECTは、選択されている行および内部のSELECTから列を参照してデータを結合します。
LISTAGGの素晴らしさについては言葉では言い尽くせません。使用すれば使用するほど、好きになるはずです。
