この例では会社の経理を例としています。経理では会社への入出金を仕訳伝票と言う単位で管理しています。SQL上ではこの伝票を単位としてタプルで記録している例です。つまり「x月ｙ日に交通費の経費支払いが行われた」というような情報を記録しています。この状態で1月から12月の交通費の月次合計が知りたい場合に今回のクロス集計を行います。

つまり年度の月ごと、科目ごとに縦横を変換して出すようなこと。

たとえば以下のような、カラムでデータが入っていたとする。

ID日付金額科目

これを

科目名4月5月6月

というように出したい場合がクロス集計である。

年度の表現

概念は説明するまでもないが、日本では1,2,3月は前の年度に含まれるため、これをSQLで書く。カラム内にcaseで書けばよく、それにasでalias名をつけてHavingで絞り込む。

select 
 (
   case when
     month(datecol) < 4
   then
     year(datecol)-1
   else
     year(datecol) end
 ) as nenndo
from
 shiwakelist
having nenndo=2014

alias 名は、whereを評価する際に生成されていないため、havingで絞り込む。あとは、必要に応じて価格や年度、月を出力してやればよい。月や価格は、その後のQueryに使うので追加しておけばよい。

caseの書式は以下。

case when 条件 then 動作１ else 動作２

条件が真なら動作１を実行し、偽なら条件2を実行する。

集計結果

select
 kname,
 sum(case when m='04' then price else 0 end) as 4月,
 sum(case when m='05' then price else 0 end) as 5月,
 sum(case when m='06' then price else 0 end) as 6月,
 sum(case when m='07' then price else 0 end) as 7月,
 sum(case when m='08' then price else 0 end) as 8月,
 sum(case when m='09' then price else 0 end) as 9月,
 sum(case when m='10' then price else 0 end) as 10月,
 sum(case when m='11' then price else 0 end) as 11月,
 sum(case when m='12' then price else 0 end) as 12月,
 sum(case when m='01' then price else 0 end) as 1月,
 sum(case when m='02' then price else 0 end) as 2月,
 sum(case when m='03' then price else 0 end) as 3月,
 sum(price) as price
from
(
  SELECT
   (
     case when
       month(f.date) < 4
     then
       year(f.date)-1
     else
       year(f.date) end
   ) as nenndo,
   month(f.date) as m,
   f.price,
   f.kname
  FROM
   vhrfurikae as f
  where
   f.isKashi=0
   having nenndo=2014
   order by year(f.date),m
 ) as s

group by kname;

　結果がこれ。サブクエリが、年度で絞り込むための年度を含む元データを生成する。それをkname（科目名）でGroupすることで一つのカラムに同一科目名のカラムが集合する。それを各カラムに振り分けするために、サブクエリ以外の部分でのcaseを書いている。

サブクエリの説明

vhrfurikaeは以下のようなカラムを含んでいる

IDisKashi(貸方フラグ)price（金額）date（日付）kname（科目名）

この状態で、日付よりcaseをつかって年度を生成する。これを元に絞り込みした。書いている時点では、日付をbetweenで指定しても良い気がするが、当時なぜ年度をわざわざ生成したのか思い出せない。

where dttm between '2017-04-01' and '2018-03-31'

最終的には、指定年度の情報を取り出すことを先にサブクエリで行っている。

クロス集計の出力

最終的に、カラムとして以下のようにしたい場合

科目名4月5月6月

SQLは、以下のような擬似的なSQLになるべきである

select
  科目名,
　4月の合計値,
　5月の合計値,
　　　：
　12月の合計値
from
  table

すなわちサブクエリから取り出した1つ1つのエントリを各月のカラムに集計していく必要がある。

それが以下の行である

sum(case when m='04' then price else 0 end) as 4月,

これは、以下のような意味を持つ。

1つのエントリの月が4月だった場合にはそのprice値を加算しそれを4月というカラムで利用する。また月が4月でない場合には、値を０として加算する。

これを12ヶ月分書けばクロス集計できる。イメージとしては、サブクエリで取り出された単一年度のデータのうちの1行がcase文の条件に引っかかりそこに合算されていくような感じである。