SQLアタマアカデミー

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

この記事を読むのに必要な時間:およそ 2 分

このケースでは,答えを出すために必要なテーブルは比較的簡単にわかります。元のテーブルにリーダー列を追加した,図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の難易度と処理のパフォーマンスに大きく影響します。

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

著者プロフィール

ミック

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

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

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

コメント

コメントの記入