運営のせいで消滅予定

このブログは閉鎖予定です

【sql】MySQL 日時ごとの集計まとめ

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

一月毎

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

一日毎

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

一時間毎

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分毎

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


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

一月毎

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

一日毎

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

一時間毎

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分毎

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


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

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

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分ごとのデータ集計)

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)
;