SQLアタマアカデミー

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

この記事を読むのに必要な時間:およそ 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
注2)
この「答えから考える」という方法は,数学の手法として古くから知られていますが,アルゴリズムのように「使えば機械的に答えが出る」という厳密なものではありません。こういう「必ずうまくいくわけではないが,役に立つこともある」という程度の補助的な方法を,ヒューリスティクス(heuristic)とか発見的解法と呼びます。あくまで補助なので,公式のように覚え込んで固執すると逆にマイナスになります。使うときは「大人の裁量」でお願いします。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ

コメント

コメントの記入