逆から考える
まずは、図2 のサンプルテーブルをご覧ください。これは、プロジェクトごとに参加している社員を対応づけたテーブルです。リーダーフラグの意味は次のとおりです。
1:プロジェクトリーダー
2:サブリーダー
0:メンバー
図2 集計用のキーがないテーブル
ProjectEmps
project_id(プロジェクトID) emp_id(社員ID) leader_fl g(リーダーフラグ)
AA00 0001 1
AA00 0002 2
AA00 0003 2
AA00 0004 0
AA00 0005 0
BB99 0001 1
BB99 0005 0
KG33 0010 1
KG33 0011 2
KG33 0007 0
XB49 0006 1
XB49 0001 0
XB49 0007 0
XB49 0022 0
ここで、各プロジェクトにリーダーは必ず1人だけ存在する必要があります。一方、サブリーダーとメンバーは、0人のこともあれば、複数人存在することもあります。
さて、このテーブルからプロジェクトごとの集計を行うことは、とても簡単です。たとえば、プロジェクト単位でそれぞれの職位の参加人数を抽出したいならば、リスト1 のようなクエリで実現できます。結果は図3 のとおり。
リスト1 プロジェクトごとの参加人数を抽出するクエリ
SELECT project_id,
COUNT(*) AS all_cnt,
SUM(CASE WHEN leader_flg = '2' THEN 1 ELSE 0 END) AS sub_leader_cnt,
SUM(CASE WHEN leader_flg = '0' THEN 1 ELSE 0 END) AS member_cnt
FROM ProjectEmps
GROUP BY project_id;
図3 プロジェクトの参加人数が抽出されたテーブル
プロジェクトの参加人数
project_id(プロジェクトID) all_cnt(全体人数) sub_leader_cnt(サブリーダー人数) member_cnt(メンバー人数)
AA00 5 2 2
BB99 2 0 1
KG33 3 1 1
XB49 4 0 3
このSQLに難しいところは特にありません。SUM関数の中でCASE式を使ってクロス表を作るテクニックも、当連載の読者にはすでにお馴染みでしょう。
ではちょっと角度を変えて、今度はプロジェクトリーダーごとに配下の延べ人数を集計してみましょう。プロジェクト横断的にリーダーだけをキーに集計すると考えると、求めたい結果は図4 のようなものです(リーダー自身はカウントせず、延べ人数なので同じ部下でも複数回カウントします) 。
図4 求めたい結果(リーダーの配下人数)
リーダーの配下人数
leader(リーダー) all_cnt(全体人数) sub_leader_cnt(サブリーダー人数) member_cnt(メンバー人数)
0001 5 2 3
0006 3 0 3
0010 2 1 1
これもまた、時として見てみたくなる集計表でしょう。先ほどの結果(図3)の「プロジェクトID」列が「リーダー」列に変わっただけの表ですから、これを求めることも何てことはないような気がします。ところが、予想に反してこれを元のテーブルから求める方法は、そう簡単に見通しが立ちません。リーダーを集約キーに使う、ということはすぐにわかるのですが、その肝心のキーとなる列が元のテーブルにないからです。
いま、「 リーダーが誰か」という情報は、emp_idとleader_fl gという2つの列を組み合わせないと得られません。したがって、ProjectEmpsの列はどれも、そのままの形では集約キーとして使えません。これが意味することは「この問題はテーブルの形を改変しないと解けない」ということです。
こういう場合、問題解決のテクニックとして覚えておいていただきたいのが、「 逆から考える」というものです。いま私たちは、求めたい答えについては明確な形で知っています。だから、「 答えを得るためには、どんなテーブルが必要か」という観点で、逆戻りするのです[2] ( 図5 ) 。
図5 ミッシングリンクを探せ
ProjectEmps
project_id(プロジェクトID) emp_id(社員ID) leader_fl g(リーダーフラグ)
AA00 0001 1
AA00 0002 2
AA00 0003 2
AA00 0004 0
AA00 0005 0
BB99 0001 1
BB99 0005 0
KG33 0010 1
KG33 0011 2
KG33 0007 0
XB49 0006 1
XB49 0001 0
XB49 0007 0
XB49 0022 0
リーダーの配下人数
leader(リーダー) all_cnt(全体人数) sub_leader_cnt(サブリーダー人数) member_cnt(メンバー人数)
0001 5 2 3
0006 3 0 3
0010 2 1 1
[2] この「答えから考える」という方法は、数学の手法として古くから知られていますが、アルゴリズムのように「使えば機械的に答えが出る」という厳密なものではありません。こういう「必ずうまくいくわけではないが、役に立つこともある」という程度の補助的な方法を、ヒューリスティクス(heuristic)とか発見的解法と呼びます。あくまで補助なので、公式のように覚え込んで固執すると逆にマイナスになります。使うときは「大人の裁量」でお願いします。
このケースでは、答えを出すために必要なテーブルは比較的簡単にわかります。元のテーブルにリーダー列を追加した、図6 のようなテーブルがあればいいのです。
図6 リーダー列を追加したテーブル
ProjectLeaders
このテーブルからであれば、leader列を集約キーとしたリスト2 のクエリですぐに答えが出ます。
リスト2 リーダーごとに配下の人数を集計するクエリ
SELECT leader,
SUM(CASE WHEN leader_flg <> '1' THEN 1 ELSE 0 END) AS all_cnt,
SUM(CASE WHEN leader_flg = '2' THEN 1 ELSE 0 END) AS sub_leader_cnt,
SUM(CASE WHEN leader_flg = '0' THEN 1 ELSE 0 END) AS member_cnt
FROM ProjectLeaders
GROUP BY leader;
あとは元のテーブルから、中間のProjectLeadersテーブルを作ることができれば、万事解決です。あるプロジェクトについてのリーダーを決定する条件は、「 leader_fl g = '1'」でわかりますから、これを条件にしたスカラサブクエリを使うことで、リーダー列を追加することができます(リスト3 ) 。
リスト3 ProjectLeadersテーブルの作成
CREATE VIEW ProjectLeaders(project_id, leader, emp_id, leader_flg)
AS
SELECT project_id,
(SELECT emp_id
FROM ProjectEmps PE1
WHERE PE0.project_id = PE1.project_id
AND leader_flg = '1'),
emp_id,
leader_flg
FROM ProjectEmps PE0;
いま、プロジェクトごとにリーダーは1人であるという条件があります。そのため、プ
ロジェクトID列を自己結合のキーとすることで、必ず1行につき1人のリーダーを定めることができるので、このスカラサブクエリはうまくいきます。
反対にプロジェクトに2人以上のリーダーがいた場合は、このサブクエリが複数行を返すためエラーとなります。これは、スカラ値が要求される場所に複合的な値が入力された場合、必然的に第一正規形を満たさなくなってしまうからです。たとえば、プロジェクト「AA00」において、「 0001」以外に「9999」というリーダーがいた場合、プロジェクト「AA00」の行には、2人をリーダーとして含めることが不可能なことは明らかです(図7 ) 。
図7 リスカラサブクエリが複数行を返した場合のProjectLeadersテーブルのイメージ
ProjectLeaders
このように、データを行として持つか列として持つか、ということは、その後の開発におけるSQLの難易度と処理のパフォーマンスに大きく影響します。
今回のように、リーダー単位で集計したいのに、リーダーが集約キーとして存在していない、というケースでは、「 一段かます」仲介が必要になります。したがって、テーブル設計のときから、どんな抽出結果を必要とするのか、要件をよく分析しておくことが重要です。