trdsql 合計を行に追加する
Window関数により元のファイルの内容に列を追加して、集計結果を出せました。 ただ、人が確認する場合は、集計の結果行が最後に出るほうが確認しやすくなります。
通常のSQLでも元の内容と集計結果を別々に出してUNIONを使うことで、一つの結果として出すことが出来ますが、一回で済むならばそれに越したことはありません。
SQLite3ではサポートされていませんが、PostgreSQLとMySQLならばサポートされている文があります。
ROLLUP
Window関数でも使用した以下のCSVファイルを使用します。
id,class,name,score
1,A,bob,174
2,A,alice,248
3,A,carol,163
4,B,dave,289
5,B,eve,157
6,B,flank,272通常のGROUP BYで全体の合計又は、class毎の合計が出せました。
ただし、class毎の合計と全体の合計を出すにはWindow関数を使用して別の列に出していました。
GROUP BYに ROLLUPを指定することで、両方を出力できます。
PostgreSQL ROLLUP
PostgreSQLでは、GROUP BY 列名の代わりにGROUP BY ROLLUP(列名)を使用することで、通常のGROUP BYに加えて、全体の集計結果を出力します。
$ trdsql -driver "postgres" -dsn "dbname=trdsql_test" -oat -ih \
"SELECT class, SUM(score::int) AS score FROM score.csv GROUP BY ROLLUP(class) ORDER BY class"
+-------+------+
| class | sum |
+-------+------+
| A | 585 |
| B | 718 |
| | 1303 |
+-------+------+
MySQL ROLLUP
MySQLでは、GROUP BY 列名の後に WITH ROLLUPを付けると、通常のGROUP BYに加えて、全体の集計結果を出力します。
$ trdsql -driver mysql -oat -ih \
"SELECT class, SUM(CAST(score AS SIGNED)) AS score FROM score.csv GROUP BY class WITH ROLLUP "
+-------+-------+
| class | score |
+-------+-------+
| A | 585 |
| B | 718 |
| | 1303 |
+-------+-------+
GROUPING SETS
PostgreSQLでは、さらに柔軟に出力することができます。
通常のCSVの内容に追加して、classの小計と全体の合計を出したい場合は、更にグループ化を制御する必要があります。
GROUPING SETSは柔軟なグループ化が可能です。
GROUPING SETSで id,name,class（つまりid別ですが、nameとclassも出力対象に含めるため、指定します）、class別、総合計（指定なし）の３つのグループ化をして出力すると以下のように、小計、合計が出力できます。
$ trdsql -driver "postgres" -dsn "dbname=trdsql_test" -oat -ih \
"SELECT id, name,class, SUM(score::int) AS score " \
" FROM score.csv GROUP BY GROUPING SETS((class,id,name),(class),()) "\
" ORDER BY class"
+----+-------+-------+-------+
| id | name | class | score |
+----+-------+-------+-------+
| 1 | bob | A | 174 |
| 2 | alice | A | 248 |
| 3 | carol | A | 163 |
| | | A | 585 |
| 4 | dave | B | 289 |
| 5 | eve | B | 157 |
| 6 | flank | B | 272 |
| | | B | 718 |
| | | | 1303 |
+----+-------+-------+-------+
上記の GROUPING SETSは、ROLLUP(class,(id,name))で簡略化できます。
$ trdsql -driver "postgres" -dsn "dbname=trdsql_test" -oat -ih \
"SELECT id,name,class, SUM(score::int) AS score " \
" FROM score.csv GROUP BY ROLLUP(class,(id,name)) " \
" ORDER BY class"
ROLLUP(class,id,name) とするとidとnameそれぞれで、グループ化してしまうので、同じscoreの行が2行づつ出てしまいます。
idとnameは１つにしつつ、class,(id,name)のそれぞれでグループ化するとGROUPING SETSと同じ意味になります。
MySQL WITH ROLLUP
MySQLでは、GROUPING SETSがないので、class,id毎でWITH ROLLUPにり結果は出せますが、nameを出力することは出来ません。
$ trdsql -driver mysql -oat -ih \
"SELECT id,class, SUM(CAST(score AS SIGNED)) AS score " \
" FROM score.csv GROUP BY class,id WITH ROLLUP"
+----+-------+-------+
| id | class | score |
+----+-------+-------+
| 1 | A | 174 |
| 2 | A | 248 |
| 3 | A | 163 |
| | A | 585 |
| 4 | B | 289 |
| 5 | B | 157 |
| 6 | B | 272 |
| | B | 718 |
| | | 1303 |
+----+-------+-------+
nameの方を集約させてしまうという手もありかもしれません。
GROUP_CONCAT()により文字列を接続することで、集約できます。
$ trdsql -driver mysql -oat -ih \
"SELECT id,GROUP_CONCAT(name) as name,class, SUM(CAST(score AS SIGNED)) AS score " \
" FROM score.csv GROUP BY class,id WITH ROLLUP"
+----+--------------------------------+-------+-------+
| id | name | class | score |
+----+--------------------------------+-------+-------+
| 1 | bob | A | 174 |
| 2 | alice | A | 248 |
| 3 | carol | A | 163 |
| | bob,alice,carol | A | 585 |
| 4 | dave | B | 289 |
| 5 | eve | B | 157 |
| 6 | flank | B | 272 |
| | dave,eve,flank | B | 718 |
| | bob,alice,carol,dave,eve,flank | | 1303 |
+----+--------------------------------+-------+-------+