SQLの集計結果をクロス表に変形するrubyスクリプト

# はじめに

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

https://bitbucket.org/cnaos/cross-tab/overview

## 使い方

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

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

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

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


```sql: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ごとにその日の支払い件数の合計を出します。


```sql:サンプルクエリ1
SELECT 
  DATE_FORMAT(payment_date,'%Y-%m-%d') date, 
  staff_id, 
  count(*) 
FROM payment 
GROUP BY date,staff_id;
```


```text:クエリ結果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 です。


```text: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スクリプトに通すと以下のようになります。

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

```text:処理結果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)の合計も集計したくなったとしましょう。

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


```sql:サンプルクエリ2
SELECT 
  DATE_FORMAT(payment_date,'%Y-%m-%d') date, 
  staff_id, 
  count(*), 
  sum(amount) 
FROM payment 
GROUP BY date,staff_id;
```


```text:クエリ結果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スクリプトに通すと以下のようになります。

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

```text:処理結果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
```