#はじめに
【MySQLでユーザー抽出をするときのこと】
-
基本的には日付ベースとかでソートすると思うけど、
- プロモーションがはじまった
- この機能をリリースした
ときとかで速報に近いものを出したいときあるじゃん。(たぶん)
-
hour(<datetime>なカラム)だと日付合わせられないし、日付をまたいじゃうと時間に統合されちゃう
やりたいこと
- SELECT文でデータをカウントする
- X日分のデータを24時間ごとに見たい
- それを1つのクエリで済ましたい
- スプレッドシートに展開するかもだけど、抽出時に昇順で並べておきたい
#んじゃどうするの？
結論：
DATE_FORMATでデータを成形します。
※元々datetimeがyyyy-mm-dd hh:mm:ssで入っていると仮定し
(例：2016-05-16 23:12:47)
アウトプットとして欲しいのはmm/dd hh:00を複数日程分とします。
(例：05/16の23時台のデータ)
やること
SELECT DATE_FORMAT(<datetime>,"%m/%d %H:00")
を文頭に入れる。
重要:
ここの「H」は大文字でないといけません。
なぜかと言うと、小文字の「h」を使うと12h換算で返ってくるためです。
「H」のとき
05/16 00:00
05/16 01:00
05/16 02:00
05/16 03:00
05/16 04:00
05/16 05:00
05/16 06:00
05/16 07:00
05/16 08:00
05/16 09:00
05/16 10:00
05/16 11:00
05/16 12:00
05/16 13:00
05/16 14:00
05/16 15:00
05/16 16:00
05/16 17:00
05/16 18:00
05/16 19:00
05/16 20:00
05/16 21:00
05/16 22:00
05/16 23:00
05/17 00:00
きちんと 24hで返ってくる
「h」のとき
05/16 01:00
05/16 02:00
05/16 03:00
05/16 04:00
05/16 05:00
05/16 06:00
05/16 07:00
05/16 08:00
05/16 09:00
05/16 10:00
05/16 11:00
05/16 12:00
※AM1:00のデータもPM1:00(=13:00)のデータも01:00に統合されちゃいます。。
「k」のとき
ちなみに「k」でもいけません。
05/16 0:00
05/16 10:00
05/16 11:00
05/16 12:00
05/16 13:00
05/16 14:00
05/16 15:00
05/16 16:00
05/16 17:00
05/16 18:00
05/16 19:00
05/16 1:00
05/16 20:00
05/16 21:00
05/16 22:00
05/16 23:00
05/16 2:00
05/16 3:00
05/16 4:00
05/16 5:00
05/16 6:00
05/16 7:00
05/16 8:00
05/16 9:00
※24hで返ってくるけど、10の位を基準にソートされるので、順番になりません。
その他
当然だけど、文頭で
SELECT DATE_FORMAT(<datatime>,"%m/%d %H:00")
を指定しているので
WHERE以下の条件に
GROUP BY
DATE_FORMAT(<datetime>,"%m/%d %H:00")
ORDER BY
DATE_FORMAT(<datetime>,"%m/%d %H:00")
をいれてくださいね。
相性が良さそうなもの
COUNT : その時間帯に登録したユーザー,送られたメッセージなどをカウントする機会は多そう。
BETWEEN <Aの期間> AND <Bの期間>：特定の期間を指定したい機会はあるでしょう。等号/不等号もしかり。
SUM(IF(条件,1,0)) : リリース後の影響を見る際などに、2パターンのデータとったりとかか使えそう。
実際に使えそうな形に成形
ユースケース：
・messagesというメッセージ情報を保存するテーブル
・createdというカラムにdatetimeの形(yyyy-mm-dd hh-mm-ss)でレコードが入る
・deletedというカラムで'0'は消えてない,'1'は消えている
⇒では、ここから特定期間の時間帯ごとの傾向を見てみる
SELECT
DATE_FORMAT(created,"%m/%d %H:00") AS created_time,
SUM(IF(deleted = "0",1,0)) AS valid_messages,
SUM(IF(deleted = "1",1,0)) AS invalid_messages,
FROM
messages
WHERE
created BETWEEN '2014-05-01' AND '2014-05-07'
GROUP BY
DATE_FORMAT(created,"%m/%d %H:00")
ORDER BY
DATE_FORMAT(created,"%m/%d %H:00")
;
全部適当に書いたｗ
なんかこんな感じで使えればm(_ _)m