はじめに

SQLでクロス集計形式にするのって結構大変ですよね？
ということで、単純なSQLの集計クエリの結果をクロス集計に変形するrubyスクリプトを作りました。

使い方

SQLの集計結果をTSVファイルとして出力したファイルを
パイプでcross-tab.rbに食わせると
クロス集計型に変形して標準出力に出力します。

cat sample.tsv | ./cross-tab.rb

サンプルとして使うデータ

mysqlのSakila Sample Databaseのpaymentテーブルのデータを使います。
https://dev.mysql.com/doc/sakila/en/sakila-structure-tables-payment.html

paymentテーブル

mysql> desc payment;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                 | Null | Key | Default           | Extra                       |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id   | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| customer_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| staff_id     | tinyint(3) unsigned  | NO   | MUL | NULL              |                             |
| rental_id    | int(11)              | YES  | MUL | NULL              |                             |
| amount       | decimal(5,2)         | NO   |     | NULL              |                             |
| payment_date | datetime             | NO   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

例1:集計項目が1つだけの場合

paymentテーブルのデータをpayment_dateで日付ごとに集計して、
staff_idごとにその日の支払い件数の合計を出します。

サンプルクエリ1

SELECT 
  DATE_FORMAT(payment_date,'%Y-%m-%d') date, 
  staff_id, 
  count(*) 
FROM payment 
GROUP BY date,staff_id;

クエリ結果1(抜粋)

+------------+----------+----------+
| date       | staff_id | count(*) |
+------------+----------+----------+
| 2005-05-24 |        1 |        4 |
| 2005-05-24 |        2 |        4 |
| 2005-05-25 |        1 |       73 |
| 2005-05-25 |        2 |       64 |
| 2005-05-26 |        1 |       96 |
| 2005-05-26 |        2 |       78 |
| 2005-05-27 |        1 |       84 |
| 2005-05-27 |        2 |       83 |
| 2005-05-28 |        1 |      119 |
| 2005-05-28 |        2 |       77 |

サンプルクエリ1の結果をTSVファイルに出力したのが、sample-data/sample1.tsv です。

sample-data/sample1.tsv

date	staff_id	count(*)
2005-05-24	1	4
2005-05-24	2	4
2005-05-25	1	73
2005-05-25	2	64
2005-05-26	1	96
2005-05-26	2	78
2005-05-27	1	84
2005-05-27	2	83
2005-05-28	1	119
2005-05-28	2	77

このファイルを作成したcross-tabスクリプトに通すと以下のようになります。

head -11 sample-data/sample1.tsv | ./cross-tab.rb

処理結果1

date	1_count(*)	2_count(*)
2005-05-24	4	4
2005-05-25	73	64
2005-05-26	96	78
2005-05-27	84	83
2005-05-28	119	77

例2:集計項目が2つある場合

例1では
payment_dateで日付ごとに集計して、
staff_idごとにその日の支払い件数の合計を出しましたが、
さらに支払い金額(amount)の合計も集計したくなったとしましょう。

集計用のクエリを以下のように変更しました。

サンプルクエリ2

SELECT 
  DATE_FORMAT(payment_date,'%Y-%m-%d') date, 
  staff_id, 
  count(*), 
  sum(amount) 
FROM payment 
GROUP BY date,staff_id;

クエリ結果2(抜粋)

+------------+----------+----------+-------------+
| date       | staff_id | count(*) | sum(amount) |
+------------+----------+----------+-------------+
| 2005-05-24 |        1 |        4 |       15.96 |
| 2005-05-24 |        2 |        4 |       13.96 |
| 2005-05-25 |        1 |       73 |      323.27 |
| 2005-05-25 |        2 |       64 |      250.36 |
| 2005-05-26 |        1 |       96 |      401.04 |
| 2005-05-26 |        2 |       78 |      353.22 |
| 2005-05-27 |        1 |       84 |      357.16 |
| 2005-05-27 |        2 |       83 |      328.17 |
| 2005-05-28 |        1 |      119 |      480.81 |
| 2005-05-28 |        2 |       77 |      323.23 |

サンプルクエリ2の結果をTSVファイルに出力したのが、sample-data/sample2.tsv です。

このファイルも同様にcross-tabスクリプトに通すと以下のようになります。

head -11 sample-data/sample2.tsv | ./cross-tab.rb

処理結果2

date	1_count(*)	1_sum(amount)	2_count(*)	2_sum(amount)
2005-05-24	4	15.96	4	13.96
2005-05-25	73	323.27	64	250.36
2005-05-26	96	401.04	78	353.22
2005-05-27	84	357.16	83	328.17
2005-05-28	119	480.81	77	323.23