skillup

技術ブログ

Database

外部結合 応用編

投稿日:

本日は「達人に学ぶ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にあたるものは下記のようにして形成できます。

 

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

netbeansのJPQL補助機能

JPAでは基本的に生のSQLではなく、JPQLを使って書きます。 SQLでもタイプミスにイライラさせられることは多いのですが、JPQLはもっとですね・・・爆 そこで使えるのがNetbeansの入力補助 …

no image

cakeでのトランザクション、コミット、ロールバック

cakePHP(2.X系)でのトランザクション、コミット、ロールバックについて。 cakePHPでトランザクションを書ける場合、Model内に [crayon-5ce4b5aea0d703007314 …

no image

mavenのリモートリポジトリについて

JPAでO/Rマッパーに慣れてからというもの通常のSQLをごりごり書くのが億劫になってきました。 億劫というかいろいろとリスクがありますね。 問題点としてはコンパイルするときにエラーが検知できなかった …

no image

複数GROUP BYでの注意

GROUP BYしたときに件数が増えるという現象があったので一応メモ。というか当たり前のことですが・・・ たとえば以下のようなテーブルがあったとします。 student id student_name …

no image

SQL基礎 条件式はunionよりもcaseで

複雑な条件式があったときにcase式を使うことでパフォーマンスを向上させることができます。 ※一般にunionを使うよりも高速なことが多い。 例1 ある条件により別の列を使いたいとき、 [crayon …