SQLアタマアカデミー

第4回行か列か、それが問題だ~スカラサブクエリの使い方 (2)逆から考える

逆から考える

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

  • 1:プロジェクトリーダー
  • 2:サブリーダー
  • 0:メンバー
図2 集計用のキーがないテーブル

ProjectEmps

project_id
(プロジェクトID)
emp_id
(社員ID)
leader_fl g
(リーダーフラグ)
AA0000011
AA0000022
AA0000032
AA0000040
AA0000050
BB9900011
BB9900050
KG3300101
KG3300112
KG3300070
XB4900061
XB4900010
XB4900070
XB4900220

ここで、各プロジェクトにリーダーは必ず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
(メンバー人数)
AA00522
BB99201
KG33311
XB49403

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

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

図4 求めたい結果(リーダーの配下人数)

リーダーの配下人数

leader
(リーダー)
all_cnt
(全体人数)
sub_leader_cnt
(サブリーダー人数)
member_cnt
(メンバー人数)
0001523
0006303
0010211

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

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

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

図5 ミッシングリンクを探せ

ProjectEmps

project_id
(プロジェクトID)
emp_id
(社員ID)
leader_fl g
(リーダーフラグ)
AA0000011
AA0000022
AA0000032
AA0000040
AA0000050
BB9900011
BB9900050
KG3300101
KG3300112
KG3300070
XB4900061
XB4900010
XB4900070
XB4900220

リーダーの配下人数

leader
(リーダー)
all_cnt
(全体人数)
sub_leader_cnt
(サブリーダー人数)
member_cnt
(メンバー人数)
0001523
0006303
0010211

このケースでは、答えを出すために必要なテーブルは比較的簡単にわかります。元のテーブルにリーダー列を追加した、図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'),              ←leader列を作るためのスカラサブクリエ
       emp_id,
       leader_flg
  FROM ProjectEmps PE0;

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

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

図7 リスカラサブクエリが複数行を返した場合のProjectLeadersテーブルのイメージ

ProjectLeaders

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

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

おすすめ記事

記事・ニュース一覧