クロス集計
クロス集計とは、特定の情報（通常は2種類）を縦軸と横軸に割り振り、相関や変化を表にして表現する集計方法です。アンケート調査の分析などでよく使われます。
SQLサンプルと解説
サンプルとして冒頭で準備した顧客テーブルを用います。
縦の集計値として「sex（性別）」、横の集計値として「place（地域）」を用いて、ユーザー数を集計したいケースを考えます。
欲しい結果は以下です。
sex | tokyo | osaka | fukuoka ----------+-------+-------+--------- M | 2 | 2 | 1 W | 4 | 1 | 2
SQLはリスト5のようになります。単純な集計のように思えますが、サブクエリが必要なため少々複雑です。順を追って確認していきます。
select/* （3） */
sex,
max(case seq_tmp when 1 then uu_count else null end) as tokyo,/* （3）-（a） */
max(case seq_tmp when 2 then uu_count else null end) as osaka,
max(case seq_tmp when 3 then uu_count else null end) as fukuoka
from(
select
*,
row_number() over (partition by sex) as seq_tmp/* （2） */
from(/* （1） */
select
sex,
place,
count(user_id)as uu_count/* （1）-（b） */
from
customer
group by place,sex/* （1）-（a） */
order by place desc
)count_tmp
) x
group by
sex
;
（1）性別・地域ごとのユーザー数集計
内側のクエリから確認していきます。
- （a）sex、place列で集計します。
- （b）ユーザー数をcount関数で集計し、uu_countとします。
このクエリによって得られる途中結果は以下です。
sex | place | uu_count ----------+------------------+---------- M | Tokyo | 2 W | Tokyo | 4 M | Osaka | 2 W | Osaka | 1 W | Fukuoka | 2 M | Fukuoka | 1
（2） 「地域」に連番を付与する
上のテーブルをもとに、「横の分類」で表現したい項目で連番を取ります。「Tokyo＝1,Osaka=2,Fukuoka=3」となるように連番を付けます。
連番を付与するのはrow_number関数の役割です。性別ごとにそれぞれ連番を取るため、sexでグルーピングしています。
row_number関数の構文は以下の通りです。項目1でグループ化し（オプション）、項目2でソート後に番号を付与します。
ROW_NUMBER ( ) OVER ( [ PARTITION BY <項目1> ] ORDER BY <項目2> )
この時点で結果は以下のようになります。
sex | place | uu_count | seq_tmp ----------+------------------+----------+--------- M | Tokyo | 2 | 1 M | Osaka | 2 | 2 M | Fukuoka | 1 | 3 W | Tokyo | 4 | 1 W | Osaka | 1 | 2 W | Fukuoka | 2 | 3
（3）CASE式で集計値を縦方向から横方向に変換する
CASE式を使ってseq_tmpで指定される番号ごとにユーザー数の集計値を表示させます。
CASE式の構文は以下の通りです。構文は2種類あり、WHENのあとに値のみ記述する構文（単純CASE式）と、「<項目> = <値1> 」のように式を記述する構文（検索CASE式）があります。
上のリスト5では単純CASE式の構文を利用しています。
CASE <項目> WHEN <値1> THEN <値4> WHEN <値2> THEN <値5> ELSE <値3> END
CASE WHEN <項目> = <値1> THEN <値4>
WHEN <項目> = <値2> THEN <値5>
ELSE <値3> END
例えば（a）の行では、seq_tmpが1である（＝Tokyoのユーザー数の集計値）のみが実際の数値で、あとはNullとなるため、max関数で対象となるユーザー数の集計値だけが表示されます。以上で、「性別ごと」「地域ごと」の2軸で集計ができました。
