Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
316
Help us understand the problem. What is going on with this article?
@yakatsuka

MySQL 日時ごとの集計まとめ

More than 5 years have passed since last update.

月別、日別、時間別にレコード数を集計

一月毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m') AS time, COUNT(*) AS count FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m');

一日毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m-%d') AS time, COUNT(*) AS count FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m%d');

一時間毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS count FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m%d%H');

30分毎

mysql> SELECT FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800, 0) * 1800) AS time, COUNT(*) AS count FROM テーブル名 GROUP BY TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800, 0);

月別、日別、時間別にカラムの値の合計値を集計

一月毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m') AS time, SUM(合計値を求めるカラム) AS sum FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m');

一日毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m-%d') AS time, SUM(合計値を求めるカラム) AS sum FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m%d');

一時間毎

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m-%d %H:00:00') AS time, SUM(合計値を求めるカラム) AS sum FROM テーブル名 GROUP BY DATE_FORMAT(カラム1, '%Y%m%d%H');

30分毎

mysql> SELECT FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800, 0) * 1800) AS time, SUM(合計値を求めるカラム) FROM テーブル名 GROUP BY TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800, 0);

日付範囲指定でデータ集計

現在日時の現在の日付~1週間前までの集計(1日ごとのデータ集計)

mysql> SELECT DATE_FORMAT(カラム1, '%Y-%m-%d') AS time, SUM(合計値を求めるカラム) AS sum FROM テーブル名 WHERE カラム1 BETWEEN DATE_SUB(curdate(), interval 7 day) AND DATE_ADD(curdate(), interval 0 day) GROUP BY DATE_FORMAT(カラム1, '%Y%m%d');

現在日時の現在の日付~1週間前までの集計(30分ごとのデータ集計)

mysql> SELECT FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800, 0) * 1800) AS time, SUM(合計値を求めるカラム) AS sum FROM テーブル名 WHERE カラム1 BETWEEN DATE_SUB(curdate(), interval 7 day) AND DATE_ADD(curdate(), interval 0 day) GROUP BY TRUNCATE(UNIX_TIMESTAMP(カラム1) / 1800,0);

316
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
316
Help us understand the problem. What is going on with this article?