逆から考える

まずは、図2のサンプルテーブルをご覧ください。これは、プロジェクトごとに参加している社員を対応づけたテーブルです。リーダーフラグの意味は次のとおりです。

• 1：プロジェクトリーダー
• 2：サブリーダー
• 0：メンバー

ここで、各プロジェクトにリーダーは必ず1人だけ存在する必要があります。一方、サブリーダーとメンバーは、0人のこともあれば、複数人存在することもあります。

さて、このテーブルからプロジェクトごとの集計を行うことは、とても簡単です。たとえば、プロジェクト単位でそれぞれの職位の参加人数を抽出したいならば、リスト1のようなクエリで実現できます。結果は図3のとおり。

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;

このSQLに難しいところは特にありません。SUM関数の中でCASE式を使ってクロス表を作るテクニックも、当連載の読者にはすでにお馴染みでしょう。

ではちょっと角度を変えて、今度はプロジェクトリーダーごとに配下の延べ人数を集計してみましょう。プロジェクト横断的にリーダーだけをキーに集計すると考えると、求めたい結果は図4のようなものです（リーダー自身はカウントせず、延べ人数なので同じ部下でも複数回カウントします⁠）⁠。

これもまた、時として見てみたくなる集計表でしょう。先ほどの結果（図3）の「プロジェクトID」列が「リーダー」列に変わっただけの表ですから、これを求めることも何てことはないような気がします。ところが、予想に反してこれを元のテーブルから求める方法は、そう簡単に見通しが立ちません。リーダーを集約キーに使う、ということはすぐにわかるのですが、その肝心のキーとなる列が元のテーブルにないからです。

いま、「⁠リーダーが誰か」という情報は、emp_idとleader_fl gという2つの列を組み合わせないと得られません。したがって、ProjectEmpsの列はどれも、そのままの形では集約キーとして使えません。これが意味することは「この問題はテーブルの形を改変しないと解けない」ということです。

こういう場合、問題解決のテクニックとして覚えておいていただきたいのが、「⁠逆から考える」というものです。いま私たちは、求めたい答えについては明確な形で知っています。だから、「⁠答えを得るためには、どんなテーブルが必要か」という観点で、逆戻りするのです[2]（⁠図5⁠）⁠。

このケースでは、答えを出すために必要なテーブルは比較的簡単にわかります。元のテーブルにリーダー列を追加した、図6のようなテーブルがあればいいのです。

このテーブルからであれば、leader列を集約キーとしたリスト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⁠）⁠。

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'),              ←leader列を作るためのスカラサブクリエ
       emp_id,
       leader_flg
  FROM ProjectEmps PE0;

いま、プロジェクトごとにリーダーは1人であるという条件があります。そのため、プ ロジェクトID列を自己結合のキーとすることで、必ず1行につき1人のリーダーを定めることができるので、このスカラサブクエリはうまくいきます。

反対にプロジェクトに2人以上のリーダーがいた場合は、このサブクエリが複数行を返すためエラーとなります。これは、スカラ値が要求される場所に複合的な値が入力された場合、必然的に第一正規形を満たさなくなってしまうからです。たとえば、プロジェクト「AA00」において、「⁠0001」以外に「9999」というリーダーがいた場合、プロジェクト「AA00」の行には、2人をリーダーとして含めることが不可能なことは明らかです（図7⁠）⁠。

このように、データを行として持つか列として持つか、ということは、その後の開発におけるSQLの難易度と処理のパフォーマンスに大きく影響します。

今回のように、リーダー単位で集計したいのに、リーダーが集約キーとして存在していない、というケースでは、「⁠一段かます」仲介が必要になります。したがって、テーブル設計のときから、どんな抽出結果を必要とするのか、要件をよく分析しておくことが重要です。