広告

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



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

実際の使用例としては、 というようなものが考えられる。
結論としては
+-----------+---------------+------+-----+---------+----------------+
| 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'のフォーマットをいじれば他の時間粒度(時間ごと、週ごとなど) にも対応可能だ。