最近色んな機能を作る時に、簡単に数値を集計してみて様子を見るということがよくあった。そこで今回はその時に使ったクエリの紹介。
【2016/10/18 10:28追記】
社内でHOUR関数とかGROUP BYにalias名を使ったらもっと簡単にできるよと言われたので、それぞれ追記してみます。
日間の作成数の集計
1日このアクションが何回行われたかとかが集計できる。date_columnにはcreatedみたいなカラムを指定し、table_nameには集計したいテーブルを入れる。他にもCOUNTの仕方を工夫したらいろいろ集計できそう。
SELECT DATE(date_column) as date, COUNT(*) as count FROM table_name GROUP BY DATE(date_column);
【改善版】
SELECT DATE(date_column) as date, COUNT(*) as count FROM table_name GROUP BY `date`;
週間の作成数の集計
上記日間の週間版。
SELECT YEARWEEK(date_column) as `yearweek`, COUNT(*) as count FROM table_name GROUP BY YEARWEEK(date_column);
【改善版】
SELECT YEARWEEK(date_column) as `yearweek`, COUNT(*) as count FROM table_name GROUP BY `yearweek`;
1時間ごとの集計
日間集計をさらに1時間単位にしたい時に。時間カラムとSUBSTRを使うといろいろ出来て便利ですね。
SELECT SUBSTR(date_column, 1, 13) as `yeardayhour`, count(*) as count FROM table_name GROUP BY SUBSTR(created_at, 1, 13);
【改善版】
SELECT SUBSTR(date_column, 1, 13) as `yeardayhour`, count(*) as count FROM table_name GROUP BY `yeardayhour`;
どの時間帯に作成されているか集計
何時の時間帯が一番活発か知りたい時に。
SELECT SUBSTR(date_column, 12, 2) as `hour`, count(*) as count FROM table_name GROUP BY SUBSTR(date_column, 12, 2);
【改善版】
SELECT HOUR(date_column) as `hour`, count(*) as count FROM table_name GROUP BY `hour`;
どの曜日に作成されているか集計
今度は何曜日に活発か知りたい時に。
SELECT DAYNAME(date_column) as `dayname`, count(*) as count FROM table_name GROUP BY DAYNAME(date_column);
【改善版】
SELECT DAYNAME(date_column) as `dayname`, count(*) as count FROM table_name GROUP BY `dayname`;
数字の桁数ごとの集計
例えば、文字数がどの範囲に収まっているかとかのヒストグラムを簡単に出したいときとかに。number_columnは数字が入っているカラムを指定しましょう。
SELECT CHAR_LENGTH(number_column) as number_of_digit, COUNT(*) as count FROM table_name GROUP BY CHAR_LENGTH(number_column);
【改善版】
SELECT CHAR_LENGTH(number_column) as number_of_digit, COUNT(*) as count FROM table_name GROUP BY `number_of_digit`;
まとめ
MySQLの関数をいろいろ使うと簡単にいろんな集計ができて便利。
また最近createdとかupdatedなカラムを全てのテーブルに入れるのはどうなのかという話も出ていましたが、こういうことがぱぱっと出来たり、後から追加するのはだるいとかいう問題もあって、容量やパフォーマンスの問題がなければ何も考えずに付ける派です。時間系カラムの最近の考えについては、また機会があったら書きます。
