Help us understand the problem. What is going on with this article?

MySQL 日時ごとの集計まとめ

More than 3 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);

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
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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