2018/02/02 追記：substring_indexの内部の動きがものすごく間違っていたので修正
先日、「2017-11-19 21:18:52」というようなtimestamp型のデータを使って、日付ごとにカウントを取ってほしいと言われ、思わず「そんな無茶な
」と言った私を救ってくれた関数「substring_index」の使い方についてです。
やりたいこと
以下のようなテーブルがあったとします。
| ID | createDate |
|---|---|
| 1 | 2017-11-19 21:18:52 |
| 2 | 2017-11-17 01:24:31 |
| 3 | 2017-11-17 02:18:37 |
| 4 | 2017-11-19 05:10:04 |
| 5 | 2017-11-16 16:28:39 |
| 6 | 2017-11-19 02:18:12 |
| 7 | 2017-11-16 12:40:35 |
日付ごとのレコード数のカウントを取りたいので、以下のような結果を出したい
| createDate | count(createDate) |
|---|---|
| 2017-11-16 | 2 |
| 2017-11-17 | 2 |
| 2017-11-19 | 3 |
とにかくやってみる
「2017-11-19」に作成されたデータは、IDが１・４・６の３つです。
日付ごとにカウントしたいので、こんなselect文を書いてみました
select createDate, count(*) from テーブル名 group by createDate;
このselect文で、createDateごとにカウントが取れるはず
しかし、createDateはtimestamp型なので、このような結果になってしまいます
これでは、やりたいことと違いますね
| createDate | count(createDate) |
|---|---|
| 2017-11-16 12:40:35 | 1 |
| 2017-11-16 16:28:39 | 1 |
| 2017-11-17 01:24:31 | 1 |
| 2017-11-17 02:18:37 | 1 |
| 2017-11-19 02:18:12 | 1 |
| 2017-11-19 05:10:04 | 1 |
| 2017-11-19 21:18:52 | 1 |
substring_indexを使ってみる
substring_indexとは
内部の動きはこんな感じ
1. 第２引数で指定された文字を使って、 第１引数で指定した文字列全体を区切る。 第１引数の中で、１個目の第２引数が出てくるところまで、２個目の第２引数が出てくるところまで・・・と少しずつ長い文字列を取得していく。
2. 先頭から 最初に取得した順にindexを振る（先頭は
ですよー）。
3. 第３引数で指定したindexの文字を取得する。
なので、substring_index('2017-11-19 05:10:04', ' ', 1)ってやると、
1. 「2017-11-19 05:10:04」を「2017-11-19」と「05:10:04 2017-11-19 05:10:04」に分ける。
2. 「2017-11-19」にindex=1を、「05:10:04 2017-11-19 05:10:04」にindex=2を振る。
3. index=1である「2017-11-19」を返す。
substring_indexをつかって
再度SQLを書いてみる（｀・ω・´）b
select substring_index(createDate, ' ', 1), count(*)
from テーブル名
group by substring_index(createDate, ' ', 1);
無事、想定通りの結果が返ってきました
| createDate | count(createDate) |
|---|---|
| 2017-11-16 | 2 |
| 2017-11-17 | 2 |
| 2017-11-19 | 3 |
めでたし、めでたし