使った環境は次の通り。
$ sw_vers ProductName: Mac OS X ProductVersion: 10.12.5 BuildVersion: 16F73 $ mysql --version mysql Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1
サンプルのテーブルとレコードを用意する
まずはサンプルとして次のようなテーブルを用意する。 これは色々なアイテムの購入履歴を表している。 購入された時刻とアイテムのコード、そして数量が記録される。
> CREATE TABLE orders( -> datetime DATETIME, -> code VARCHAR(255) NOT NULL, -> amount INTEGER NOT NULL -> ); Query OK, 0 rows affected (0.02 sec)
上記のテーブルにいくつかレコードを追加しておく。 二分間におにぎりとパンとパスタがいくつか購入されている。
> INSERT INTO -> orders -> VALUES -> ('2017-06-30 20:00:00', 'Onigiri', 1), -> ('2017-06-30 20:00:00', 'Pan', 2), -> ('2017-06-30 20:00:00', 'Pasta', 1), -> ('2017-06-30 20:00:10', 'Onigiri', 3), -> ('2017-06-30 20:00:20', 'Pan', 2), -> ('2017-06-30 20:00:30', 'Pasta', 1), -> ('2017-06-30 20:01:00', 'Onigiri', 2), -> ('2017-06-30 20:01:00', 'Pan', 2), -> ('2017-06-30 20:01:00', 'Pasta', 2); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0
格納されたデータはこんな感じ。
> SELECT * FROM orders; +---------------------+---------+--------+ | datetime | code | amount | +---------------------+---------+--------+ | 2017-06-30 20:00:00 | Onigiri | 1 | | 2017-06-30 20:00:00 | Pan | 2 | | 2017-06-30 20:00:00 | Pasta | 1 | | 2017-06-30 20:00:10 | Onigiri | 3 | | 2017-06-30 20:00:20 | Pan | 2 | | 2017-06-30 20:00:30 | Pasta | 1 | | 2017-06-30 20:01:00 | Onigiri | 2 | | 2017-06-30 20:01:00 | Pan | 2 | | 2017-06-30 20:01:00 | Pasta | 2 | +---------------------+---------+--------+ 9 rows in set (0.00 sec)
特定の条件に一致するアイテムを確認する
このとき、特定のコードを持ったアイテムがどれだけ購入されたかを確認してみよう。
一般的には WHERE 句を使ってこうするはず。
> SELECT -> * -> FROM orders -> WHERE code like 'Onigiri'; +---------------------+---------+--------+ | datetime | code | amount | +---------------------+---------+--------+ | 2017-06-30 20:00:00 | Onigiri | 1 | | 2017-06-30 20:00:10 | Onigiri | 3 | | 2017-06-30 20:01:00 | Onigiri | 2 | +---------------------+---------+--------+ 3 rows in set (0.00 sec)
ただ、上記だと同時に一つのアイテムしか確認できない。 おにぎりとパンとパスタの売れ方を同時に時系列で見たい、という場合はどうすれば良いだろうか？
CASE 句で縦持ちのデータを横持ちに変換する
そういったときは CASE 句を使って新しいカラムを追加する。
一般的には条件分岐に用いる CASE 句だけど、次のようにすると特定の条件に一致する内容で新しいカラムを作れる。
これによって縦持ちのデータを横持ちにできる。
> SELECT -> datetime, -> CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END AS 'Onigiri', -> CASE WHEN code like 'Pan' THEN amount ELSE 0 END AS 'Pan', -> CASE WHEN code like 'Pasta' THEN amount ELSE 0 END AS 'Pasta' -> FROM orders; +---------------------+---------+-----+-------+ | datetime | Onigiri | Pan | Pasta | +---------------------+---------+-----+-------+ | 2017-06-30 20:00:00 | 1 | 0 | 0 | | 2017-06-30 20:00:00 | 0 | 2 | 0 | | 2017-06-30 20:00:00 | 0 | 0 | 1 | | 2017-06-30 20:00:10 | 3 | 0 | 0 | | 2017-06-30 20:00:20 | 0 | 2 | 0 | | 2017-06-30 20:00:30 | 0 | 0 | 1 | | 2017-06-30 20:01:00 | 2 | 0 | 0 | | 2017-06-30 20:01:00 | 0 | 2 | 0 | | 2017-06-30 20:01:00 | 0 | 0 | 2 | +---------------------+---------+-----+-------+ 9 rows in set (0.00 sec)
こうすれば、おにぎりとパンとパスタの売れ行きを同時に見られて嬉しい。
ちなみに条件に一致しないときのデフォルト値として ELSE 0 を指定しているのは地味に重要なポイント。
これを入れておかないと、どれにも一致しないときに NULL が返ってきてしまう。
> SELECT -> datetime, -> CASE WHEN code like 'Onigiri' THEN amount END AS 'Onigiri', -> CASE WHEN code like 'Pan' THEN amount END AS 'Pan', -> CASE WHEN code like 'Pasta' THEN amount END AS 'Pasta' -> FROM orders; +---------------------+---------+------+-------+ | datetime | Onigiri | Pan | Pasta | +---------------------+---------+------+-------+ | 2017-06-30 20:00:00 | 1 | NULL | NULL | | 2017-06-30 20:00:00 | NULL | 2 | NULL | | 2017-06-30 20:00:00 | NULL | NULL | 1 | | 2017-06-30 20:00:10 | 3 | NULL | NULL | | 2017-06-30 20:00:20 | NULL | 2 | NULL | | 2017-06-30 20:00:30 | NULL | NULL | 1 | | 2017-06-30 20:01:00 | 2 | NULL | NULL | | 2017-06-30 20:01:00 | NULL | 2 | NULL | | 2017-06-30 20:01:00 | NULL | NULL | 2 | +---------------------+---------+------+-------+ 9 rows in set (0.00 sec)
こうなると NULL の伝搬を考えなきゃいけなくなるので、ここからの集計処理が大変になる。
購入時刻でまとめる
ちなみにさっきの集計だと同じ時刻のものがそれぞれのアイテムごとに複数行で表示されてしまっていた。
こんなときは購入時刻で GROUP BY した上で、それぞれの CASE 句を SUM() 関数で合計すれば良い。
> SELECT -> datetime, -> SUM(CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END) AS 'Onigiri', -> SUM(CASE WHEN code like 'Pan' THEN amount ELSE 0 END) AS 'Pan', -> SUM(CASE WHEN code like 'Pasta' THEN amount ELSE 0 END) AS 'Pasta' -> FROM orders -> GROUP BY datetime; +---------------------+---------+------+-------+ | datetime | Onigiri | Pan | Pasta | +---------------------+---------+------+-------+ | 2017-06-30 20:00:00 | 1 | 2 | 1 | | 2017-06-30 20:00:10 | 3 | 0 | 0 | | 2017-06-30 20:00:20 | 0 | 2 | 0 | | 2017-06-30 20:00:30 | 0 | 0 | 1 | | 2017-06-30 20:01:00 | 2 | 2 | 2 | +---------------------+---------+------+-------+ 5 rows in set (0.00 sec)
同じ時刻が一つのレコードで確認できるようになって、さらに見やすくなった。
一定の時刻の範囲で集計する
ただ、先ほどの表示を見ると、まだ問題が残っていることに気づくはず。 全く同じ時刻に購入されたものは一つのレコードで表現されているけど、秒数が異なるものは違うレコードになってしまっている。
そんなときは集計したい時刻の範囲を切り出して、それを GROUP BY でまとめる。
次のクエリでは時刻を一分単位までしか見ないようにして集計している。
これなら秒数が異なっても同じレコードとしてまとめることができる。
> SELECT -> DATE_FORMAT(datetime, '%Y-%m-%d %H:%i') AS datetime_minute, -> SUM(CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END) AS 'Onigiri', -> SUM(CASE WHEN code like 'Pan' THEN amount ELSE 0 END) AS 'Pan', -> SUM(CASE WHEN code like 'Pasta' THEN amount ELSE 0 END) AS 'Pasta' -> FROM orders -> GROUP BY datetime_minute; +------------------+---------+------+-------+ | datetime_minute | Onigiri | Pan | Pasta | +------------------+---------+------+-------+ | 2017-06-30 20:00 | 4 | 4 | 2 | | 2017-06-30 20:01 | 2 | 2 | 2 | +------------------+---------+------+-------+ 2 rows in set (0.00 sec)
ちなみに、時刻の扱いは RDBMS ごとにかなり異なるので、次の DATE_FORMAT() 関数を使った SQL は別の実装では動かないと思う。
まとめ
縦に色んなデータが入ったテーブルを横に並べるには CASE 句を使うと良い。
- 作者: 加嵜長門,田宮直人,丸山弘詩
- 出版社/メーカー: マイナビ出版
- 発売日: 2017/03/27
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
