JP / EN

MySQLで散発的なイベントの日付ごとの最大値・最小値などを集計する

タグ：mysql

ランダムな頻度で記録される数値データに関して、日付ごとの代表値（最大値）を集計したい、ということがある。

イベントが散発的に（例えば一日数回、ランダムな時間帯に、など）発生する
イベントが発生するたびにログがデータベースに落ちてくる
ログの各行にはイベントに関する何らかの数値とタイムスタンプが記録されている

というようなケースだ。

実際の使用例としては、

クローラ―を使ってSNSのフォロワー数を毎日記録したい（がクローラ―の出来が悪く記録される時間帯がまちまちである）
株取引が約定されるたびに価格を記録し、毎日最大値・最小値を記録したい

というようなものが考えられる。
結論としては

+-----------+---------------+------+-----+---------+----------------+
| Field     | Type          | Null | Key | Default | Extra          |
+-----------+---------------+------+-----+---------+----------------+
| id        | int           | NO   | PRI | NULL    | auto_increment |
| date      | datetime      | YES  |     | NULL    |                |
| follower  | int           | YES  |     | NULL    |                |
+-----------+---------------+------+-----+---------+----------------+

のようなテーブル(SNSの自分のアカウントのフォロワー数を毎日監視するようなタスクを想定) に対して

  select max(follower) as mfollower, max(date) as mdate, DATE_FORMAT(date, '%Y-%m-%d') as fdate
      from twitter_ff
      group by DATE_FORMAT(date, '%Y-%m-%d')

でできた。

ミソは

  DATE_FORMAT(date, '%Y-%m-%d')

によって丸めたタイムスタンプ値に関してgroup-byすることで"日付ごと"の集計を実現することだ。
もちろん'%Y-%m-%d'のフォーマットをいじれば他の時間粒度（時間ごと、週ごとなど）にも対応可能だ。

https://wonderhorn.net/