| 項目 | 内容 | 補足 |
|---|---|---|
| 目標 | ＳＱＬでサクサクとクロス集計する | |
| 背景 | クロス集計用の前処理をＳＱＬで行いエクセルに渡したい | |
| 前提 | エクセルでクロス集計の経験あり | |
| 学習 | SQL case 構文 |
- ちょっと解説
データは、年齢、性別、身長、体重のように、項目が横にならんでいますが、クロス集計は、このうちひとつの項目を縦に値を展開して、集計（主に、数え上げ）しなおすものです。
貿易統計では、輸出入、月、国、ＨＳコード、数量、金額などの項目があります。今回のクロス集計では、輸出入を縦（輸出と輸入）に展開し、月別の項目数を数え上げてみます。
case 構文を学ぶ
おなじみ tmp.dbをあけて、下記のＳＱＬを実行してください。
今回、学習するのは、case という構文です。
case 構文 は、下記にあるので見ればわかると思いますが、簡単に解説します。
case 対象の項目
when 条件 then 値
※ある条件に合致したら、値を返す
※繰り返すことができます。
else をつかってそれ以外の選択もできます。
最後に end 新規項目名
でおわり、項目を続ける場合には、,(カンマ）を忘れないようにしましょう。
最初の case 構文は、輸出が、1, 輸入が 2 というのがわかりにくいので、輸出、輸入の文字列をわりあてるためです。1,2 でいいなら不要で、exp_imp をかわりに配置します。
集計関数のsum()の中に、case 構文を書くことも可能です。条件に該当するときは、1をたてて、集計するので、実際には、数え上げをしています。条件は、月が、01のときは、m01 という新規項目をたてています。データは、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 とかになっているので、エクセルにいってから、下記の表のように、追記してあげてください。
| 輸出入 | １月 | ２月 | ３月 | ４月 | ５月 | ６月 | ７月 | ８月 | 総数 |
|---|---|---|---|---|---|---|---|---|---|
| 輸出 | 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 ) の １の部分を、Value に変えます。結構簡単ですね。やってみましょう。