SQLによるクロス集計 (SQL: Cross tab)

「SQLで小計と合計を取得する (SQL: Subtotal and Grand Total)」では小計と合計を取得するSQLについて説明しました。
今回はクロス集計を行う方法について確認します。

【sum とdecodeによる集計】
古典的な方法は以下の様にsumとdecodeを組み合わせる構文です。

List 1

select prd.prod_id, prd.prod_name, sum(decode(extract(month from sls.time_id), 1, sls.quantity_sold)) as JAN, sum(decode(extract(month from sls.time_id), 2, sls.quantity_sold)) as FEB, sum(decode(extract(month from sls.time_id), 3, sls.quantity_sold)) as MAR, sum(decode(extract(month from sls.time_id), 4, sls.quantity_sold)) as APR, sum(decode(extract(month from sls.time_id), 5, sls.quantity_sold)) as MAY, sum(decode(extract(month from sls.time_id), 6, sls.quantity_sold)) as JUN, sum(decode(extract(month from sls.time_id), 7, sls.quantity_sold)) as JUL, sum(decode(extract(month from sls.time_id), 8, sls.quantity_sold)) as AUG, sum(decode(extract(month from sls.time_id), 9, sls.quantity_sold)) as SEP, sum(decode(extract(month from sls.time_id), 10, sls.quantity_sold)) as OCT, sum(decode(extract(month from sls.time_id), 11, sls.quantity_sold)) as NOV, sum(decode(extract(month from sls.time_id), 12, sls.quantity_sold)) as DEC, sum(quantity_sold) as subtotal from sales sls, products prd where sls.prod_id = prd.prod_id and sls.time_id between to_date('19980101','yyyymmdd') and to_date('19981231','yyyymmdd') group by prd.prod_id, prd.prod_name order by prd.prod_id; 

出力結果は以下の通りです。

Figure 1: sum and decode

この例では結果セットがそのままクロス集計表の形式になっているため、結果セットを扱う側のプログラム処理が簡単であるという長所があります。
この構文は横軸（列）の構成が年月の場合など、固定している場合には有用です。


【pivot】
Oracle Database 11g から、新たにpivot句が導入されました。
以下のSQLは前述のSQLと同等です。仕様によるものかは不明ですが、pivotを行う前に、対象となるデータを副問い合わせで絞り込んでおく必要があるようです。

List 2

select *
  from (select prd.prod_id,
               prd.prod_name,
               extract(month from sls.time_id) time_id,
               sls.quantity_sold from sales sls, products prd
         where sls.prod_id = prd.prod_id
           and sls.time_id between to_date('19980101','yyyymmdd') and to_date('19981231','yyyymmdd'))
 pivot (sum(quantity_sold) for time_id in (1,2,3))
 order by prod_id;

出力結果はFigure 1を参照してください。

残念ながら、現状ではpivot句のinに指定する値をサブクエリーで指定することができず、固定値として指定しなければならないため、この部分を動的SQLで組み立てる等の作り込が必要です。
この辺りには機能仕様の詰めの甘さを感じます。将来対応を期待する箇所です。


【group by cube()の使用】
group by cube()は、クロス集計結果を戻します。列の構成はselect句の指定のままですので、結果セットを受け取ったプログラム側でクロス集計表形式に展開する必要がありますが、前述の２つの方式と異なり、横軸の要素が事前に不明である場合でも、すべての組み合わせを自動で集計する点で優位です。
group by cube()は2つ以上のディメンジョンを同時に計算することができます。以下の例では、一般的な2次元の集計に、product表を結合してproduct.prod_nameを表示しています。

List 3

select prd.prod_id,
       prd.prod_name,
       sls.mm,
       sls.sum_quantity
  from products prd,
       (
        select prod_id,
               extract(month from time_id) as mm,
               sum(quantity_sold) as sum_quantity
          from sales
         where time_id between to_date('19980101','yyyymmdd') and to_date('19981231','yyyymmdd')
         group by cube (prod_id, extract(month from time_id))
       ) sls
 where prd.prod_id(+) = sls.prod_id
 order by prd.prod_id, sls.mm;

出力結果は以下の通りです。

Figure 2: group by cube()

---

[Summary]
There are three ways to perform cross tabulation.

[sum and decode]
The traditional way is List1. Figure 1 shows its result.

[pivot]
Oracle Database 11g releases pivod operator. With pivot, you can rewrite the previous SQL very simple. Please see List 2.
Unfortunately, you need to specify in clause with static values - in clause does not accept sub query. We hope this function will be improved in the future release.

[group by cube()]
group by cube() produces cross-tabulation values. Since it does not format its results like spread sheet, it is your application's duty to format spread sheet like output. Please see List 3 and Figure 2.

(*) for group by rollup(), please refer the past post here.