ray88’s diary

お仕事で困ったとき用の自分用の覚書

トップ > SQL指南書 外部結合で行列変換③　クロス表で入れ子の表側を作る

SQL指南書 外部結合で行列変換③　クロス表で入れ子の表側を作る

SQL 指南書集 目次 - ray88’s diary
■統計表の作成業務では表題や表頭を入れ子にした表を作りたいという要望が発生する

■例題：県別・年齢階級別・性別の人口データを保持する「TblPop」から次のようなクロス表を作成する



■誤ったSQL文の例
【考え方】
STEP１：「TblPop」テーブルより年齢階級の列、性別の列、とCASE式で東北の人口列、関東の人口列を作成を抽出したテーブル「DATA」を作成する

STEP2：「DATA」テーブルと年齢階級マスタ「TblAge」をRIGHT JOINで結合する

※STEP2までで止めた場合のSQLはこちら

/* 最初の外部結合で止めた場合：年齢階級「2」も結果に現れる(p.90) */
SELECT MASTER1.age_class AS age_class,
       DATA.sex_cd AS sex_cd,
       DATA.pop_tohoku AS pop_tohoku,
       DATA.pop_kanto AS pop_kanto
  FROM (SELECT age_class, sex_cd,
               SUM(CASE WHEN pref_name IN ('青森', '秋田')
                        THEN population ELSE NULL END) AS pop_tohoku,
               SUM(CASE WHEN pref_name IN ('東京', '千葉')
                        THEN population ELSE NULL END) AS pop_kanto
          FROM TblPop
         GROUP BY age_class, sex_cd) DATA
        RIGHT OUTER JOIN TblAge MASTER1
           ON MASTER1.age_class = DATA.age_class
 ORDER BY age_class ASC,sex_cd DESC

STEP3：STEP2の出力結果のテーブルと性別マスタ「TblSex」を結合する
　　　※ここで誤りが発生する。STEP2で出力した結合結果のテーブルには年齢階層「２」の「性別」列が
　　　　NULL値のため、性別マスタと結合し際に「ON MASTER2.sex_cd = NULL」となり、結果がunkown
　　　　となるため、年齢階層２の行が抽出されなくなってしまう。結合テーブルの順番を逆にしても同じ。


/* 外部結合で入れ子の表側を作る：間違ったSQL(p.89) */
SELECT MASTER1.age_class AS age_class,
       MASTER2.sex_cd AS sex_cd,
       DATA.pop_tohoku AS pop_tohoku,
       DATA.pop_kanto AS pop_kanto
  FROM (SELECT age_class, sex_cd,
               SUM(CASE WHEN pref_name IN ('青森', '秋田')
                        THEN population ELSE NULL END) AS pop_tohoku,
               SUM(CASE WHEN pref_name IN ('東京', '千葉')
                        THEN population ELSE NULL END) AS pop_kanto
          FROM TblPop
         GROUP BY age_class, sex_cd) DATA
        RIGHT OUTER JOIN TblAge MASTER1 /* 外部結合1：年齢階級マスタと結合 */
           ON MASTER1.age_class = DATA.age_class
              RIGHT OUTER JOIN TblSex MASTER2 /* 外部結合2：性別マスタと結合 */
                 ON MASTER2.sex_cd = DATA.sex_cd
ORDER BY age_class ASC,sex_cd DESC;

■解決方法：CROSS JOINを使用して外部結合を１度で済ませる
　※表側を入れ子にするときはその形のマスタをあらかじめ用意すればよい。
　　３レベル以上の入れ子の場合も同じやり方で拡張可能。
　　CROSS JOIN 構文を持っていないDBの場合は「FROM TblAge,TblSex」のように結合条件指定なしで
　　テーブルを並べるとクロス結合と同じ演算子となる
【考え方】
STEP１：年齢階級マスタ「TblAge」と「TblSex」を CROSS JOIN で結合して「MASTER」テーブルを作成する
　　　※CROSS JOIN　は直積でテーブル内のアイテムの全ての組み合わせを出力する。ONでは結合しない

STEP２：「TblPop」テーブルより年齢階級の列、性別の列、とCASE式で東北の人口列、関東の人口列を作成を抽出したテーブル「DATA」を作成する

STEP3：STEP1で作成した「DATA」テーブルとSTEP2で作成した「MASTER」テーブルを
　　　　年齢階級クラスと性別コードをキーにしてLEFT JOIN で結合する

/* 外部結合で入れ子の表側を作る：正しいSQL(p.91) */
SELECT
  MASTER.age_class AS age_class,
  MASTER.sex_cd    AS sex_cd,
  DATA.pop_tohoku  AS pop_tohoku,
  DATA.pop_kanto   AS pop_kanto
FROM
  (SELECT age_class, sex_cd
   FROM TblAge
   CROSS JOIN TblSex) AS MASTER -- クロス結合でマスタ同士の直積を作る
LEFT OUTER JOIN
  (SELECT
     age_class,
     sex_cd,
     SUM(CASE WHEN pref_name IN ('青森', '秋田')
              THEN population ELSE NULL END) AS pop_tohoku,
     SUM(CASE WHEN pref_name IN ('東京', '千葉')
              THEN population ELSE NULL END) AS pop_kanto
   FROM TblPop
   GROUP BY age_class, sex_cd) AS DATA  -- DATAテーブル作成
    ON  MASTER.age_class = DATA.age_class
    AND MASTER.sex_cd = DATA.sex_cd
ORDER BY age_class ASC, sex_cd DESC;

■別解：「解決方法」のSQLのSTEP2を省略し、直接 tblPop と結合する（ビューを１つ減らしている）

/* 演習問題１：結合が先か、集約が先か？ (p.294) 
   インライン・ビューを一つ削除した修正版 */
SELECT MASTER.age_class AS age_class,
       MASTER.sex_cd AS sex_cd,
       SUM(CASE WHEN pref_name IN ('青森', '秋田')
                THEN population ELSE NULL END) AS pop_tohoku,
       SUM(CASE WHEN pref_name IN ('東京', '千葉')
                THEN population ELSE NULL END) AS pop_kanto
  FROM (SELECT age_class, sex_cd
          FROM TblAge CROSS JOIN TblSex) MASTER
        LEFT OUTER JOIN TblPop DATA      /* DATA はTblPop そのものであるのがミソ。 */
    ON MASTER.age_class = DATA.age_class
   AND MASTER.sex_cd = DATA.sex_cd
 GROUP BY MASTER.age_class, MASTER.sex_cd;

■テーブル作成SQL

/* クロス表で入れ子の表側を作る */
CREATE TABLE TblSex
(sex_cd   char(1), 
 sex varchar(5), 
 PRIMARY KEY(sex_cd));

CREATE TABLE TblAge 
(age_class char(1), 
 age_range varchar(30), 
 PRIMARY KEY(age_class));

CREATE TABLE TblPop 
(pref_name  varchar(30), 
 age_class  char(1), 
 sex_cd     char(1), 
 population integer, 
 PRIMARY KEY(pref_name, age_class,sex_cd));

INSERT INTO TblSex (sex_cd, sex ) VALUES('m',	'男');
INSERT INTO TblSex (sex_cd, sex ) VALUES('f',	'女');

INSERT INTO TblAge (age_class, age_range ) VALUES('1',	'21～30歳');
INSERT INTO TblAge (age_class, age_range ) VALUES('2',	'31～40歳');
INSERT INTO TblAge (age_class, age_range ) VALUES('3',	'41～50歳');

INSERT INTO TblPop VALUES('秋田', '1', 'm', 400 );
INSERT INTO TblPop VALUES('秋田', '3', 'm', 1000 );
INSERT INTO TblPop VALUES('秋田', '1', 'f', 800 );
INSERT INTO TblPop VALUES('秋田', '3', 'f', 1000 );
INSERT INTO TblPop VALUES('青森', '1', 'm', 700 );
INSERT INTO TblPop VALUES('青森', '1', 'f', 500 );
INSERT INTO TblPop VALUES('青森', '3', 'f', 800 );
INSERT INTO TblPop VALUES('東京', '1', 'm', 900 );
INSERT INTO TblPop VALUES('東京', '1', 'f', 1500 );
INSERT INTO TblPop VALUES('東京', '3', 'f', 1200 );
INSERT INTO TblPop VALUES('千葉', '1', 'm', 900 );
INSERT INTO TblPop VALUES('千葉', '1', 'f', 1000 );
INSERT INTO TblPop VALUES('千葉', '3', 'f', 900 );