外部結合 応用編

本日は「達人に学ぶSQL徹底指南書」の外部結合の応用編です。

下記のような3つのテーブルがあるとします。例によってここを使わしてもらってます。

tblsex
sex_cd | sex
——–+—–
m | 男
f | 女

tblage
age_class | age_range
———–+———–
1 | 21～30歳
2 | 31～40歳
3 | 41～50歳
(3 行)

tblpop
pref_name | age_class | sex_cd | population
———–+———–+——–+————
秋田 | 1 | m | 400
秋田 | 3 | m | 1000
秋田 | 1 | f | 800
秋田 | 3 | f | 1000
青森 | 1 | m | 700
青森 | 1 | f | 500
青森 | 3 | f | 800
東京 | 1 | m | 900
東京 | 1 | f | 1500
東京 | 3 | f | 1200
千葉 | 1 | m | 900
千葉 | 1 | f | 1000
千葉 | 3 | f | 900

ここからいわゆる下記のようなクロス集計表を作成します。

age_range | sex | touhoku_pop | kantou_pop
———–+—–+————-+————
21～30歳 | 男 |     1100 | 1800
21～30歳 | 女 |  1300 | 2500
31～40歳 | 男 |                         |
31～40歳 | 女 |                         |
41～50歳 | 男 | 1000 |
41～50歳 | 女 | 1800 | 2100

最初下記のようにtblpopを中心にJOINして考えましたが、うまくいきません。

age_range | sex | pref_name | population
———–+—–+———–+————
21～30歳 | 男 | 秋田 | 400
41～50歳 | 男 | 秋田 | 1000
21～30歳 | 女 | 秋田 | 800
41～50歳 | 女 | 秋田 | 1000
21～30歳 | 男 | 青森 | 700
21～30歳 | 女 | 青森 | 500
41～50歳 | 女 | 青森 | 800
21～30歳 | 男 | 東京 | 900
21～30歳 | 女 | 東京 | 1500
41～50歳 | 女 | 東京 | 1200
21～30歳 | 男 | 千葉 | 900
21～30歳 | 女 | 千葉 | 1000
41～50歳 | 女 | 千葉 | 900
31～40歳 | | |

上記のようにやっても普通にやると31歳～のエリアが男女別にちゃんとでません。元のレコードがないから当たり前ですが・・

この時に必要になってくるのが、この本でよく出てくる組み合わせの考え方です。
最初の集合の考えを使い年齢×性別の表を作ります。

sex_cd | sex | age_class | age_range
——–+—–+———–+———–
m | 男 | 1 | 21～30歳
m | 男 | 2 | 31～40歳
m | 男 | 3 | 41～50歳
f | 女 | 1 | 21～30歳
f | 女 | 2 | 31～40歳
f | 女 | 3 | 41～50歳

イメージ的にいうとこのテーブルとtblpopを結合させるようとします。
単純にFROM句に続けて書くと6*13 = 78行になりますが、
全ては必要ありませんので、結合を考えます。
また年齢＊性別で作ったテーブルはエイリアスをつけて一つのテーブルとしておかないと結合できません。
もとの枠組み的なテーブルなのでbasic_frameとしておきます。

sex_cd | sex | age_class | age_range | population | pref_name

——–+—–+———–+———–+————+———–
f | 女 | 1 | 21～30歳 | 500 | 青森
f | 女 | 1 | 21～30歳 | 1500 | 東京
f | 女 | 1 | 21～30歳 | 1000 | 千葉
f | 女 | 1 | 21～30歳 | 800 | 秋田
f | 女 | 2 | 31～40歳 | |
f | 女 | 3 | 41～50歳 | 900 | 千葉
f | 女 | 3 | 41～50歳 | 800 | 青森
f | 女 | 3 | 41～50歳 | 1200 | 東京
f | 女 | 3 | 41～50歳 | 1000 | 秋田
m | 男 | 1 | 21～30歳 | 400 | 秋田
m | 男 | 1 | 21～30歳 | 900 | 東京
m | 男 | 1 | 21～30歳 | 700 | 青森
m | 男 | 1 | 21～30歳 | 900 | 千葉
m | 男 | 2 | 31～40歳 | |
m | 男 | 3 | 41～50歳 | 1000 | 秋田

ここまで来たら6合目までは来ているはず・・・caseでpref_nameから地方をつくり、GROUP BYでSUMします

age_range | sex | district | sum
———–+—–+———-+——
21～30歳 | 女 | 関東 | 2500
21～30歳 | 女 | 東北 | 1300
21～30歳 | 男 | 関東 | 1800
21～30歳 | 男 | 東北 | 1100
31～40歳 | 女 | |
31～40歳 | 男 | |
41～50歳 | 女 | 関東 | 2100
41～50歳 | 女 | 東北 | 1800
41～50歳 | 男 | 東北 | 1000

ここから行列変換を使えばいい・・と思っていたのですが一筋縄でいきませんでした。
ここまでを1テーブルと考えてやればまたいけるかもしれませんが・・・

最終的にはdistrictをGROUP BYで出さずに年齢と性別ですでにグルーピングしているため単純にこの部分の合計を出すと年齢、性別でグルーピングされた全地域人口がでますが、CASEで青森、秋田ならSUMを出すとやれ該当の人口分だけ計算してくれることになります。(東京と千葉は無視します。)
同じことを関東にもあてはめて出力すればOKです。

age_range | sex | touhoku_pop | kantou_pop
———–+—–+————-+————
21～30歳 | 男 | 1100 | 1800
21～30歳 | 女 | 1300 | 2500
31～40歳 | 男 | |
31～40歳 | 女 | |
41～50歳 | 男 | 1000 |
41～50歳 | 女 | 1800 | 2100

模範解答を見ますと、やはりbasic_frameにあたるものを作っていました。

私はこの本ででてきたFROM句に2つ並べる方法をつかっていましたが、CROSS JOINという結合(総組み合わせ)があるようです。

basic_frameにあたるものは下記のようにして形成できます。