Edited at

前々処理5:サクサク使うSQL(クロス集計)

More than 1 year has passed since last update.

項目
内容
補足

目標
SQLでサクサクとクロス集計する

背景
クロス集計用の前処理をSQLで行いエクセルに渡したい

前提
エクセルでクロス集計の経験あり

学習
SQL case 構文


  • ちょっと解説

データは、年齢、性別、身長、体重のように、項目が横にならんでいますが、クロス集計は、このうちひとつの項目を縦に値を展開して、集計(主に、数え上げ)しなおすものです。

貿易統計では、輸出入、月、国、HSコード、数量、金額などの項目があります。今回のクロス集計では、輸出入を縦(輸出と輸入)に展開し、月別の項目数を数え上げてみます。


case 構文を学ぶ

おなじみ tmp.dbをあけて、下記のSQLを実行してください。

今回、学習するのは、case という構文です。

case 構文 は、下記にあるので見ればわかると思いますが、簡単に解説します。

case 対象の項目

when 条件 then 値

※ある条件に合致したら、値を返す

※繰り返すことができます。

else をつかってそれ以外の選択もできます。

最後に end 新規項目名

でおわり、項目を続ける場合には、,(カンマ)を忘れないようにしましょう。

最初の case 構文は、輸出が、1, 輸入が 2 というのがわかりにくいので、輸出、輸入の文字列をわりあてるためです。1,2 でいいなら不要で、exp_imp をかわりに配置します。

集計関数のsum()の中に、case 構文を書くことも可能です。条件に該当するときは、1をたてて、集計するので、実際には、数え上げをしています。条件は、月が、01のときは、m01 という新規項目をたてています。データは、8月分までしかないので、8月でおわりにして、最後に、総数をならべています。

select 

case exp_imp
when 1 then '輸出'
else '輸入'
end export_import,
sum(case month when '01' then 1 else 0 end ) as m01,
sum(case month when '02' then 1 else 0 end ) as m02,
sum(case month when '03' then 1 else 0 end ) as m03,
sum(case month when '04' then 1 else 0 end ) as m04,
sum(case month when '05' then 1 else 0 end ) as m05,
sum(case month when '06' then 1 else 0 end ) as m06,
sum(case month when '07' then 1 else 0 end ) as m07,
sum(case month when '08' then 1 else 0 end ) as m08,
count(*) as total
from ym_latest
group by exp_imp

結果は、下記です。実際の作業では、エクセルにコピペして使うのが楽です。

項目は、export_import,m01 とかになっているので、エクセルにいってから、下記の表のように、追記してあげてください。

輸出入
1月
2月
3月
4月
5月
6月
7月
8月
総数

輸出
62907
68583
71542
68844
67102
69662
68994
67632
545266

輸入
47694
47056
49600
46809
47823
48428
49042
49086
385538

これは、数えあげているだけですが、金額(Value)を集計することも可能です。

sum(case month when '01' then 1 else 0 end ) の 1の部分を、Value に変えます。結構簡単ですね。やってみましょう。