はじめに
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