【SQL/MySQL】Group by date only on a Datetime column

2024-04-09

SQL/MySQLでDatetime型カラムを日付のみでグループ化する

DATE_FORMAT 関数を使う

この方法は、DATE_FORMAT 関数を使って日付のみを含む新しい文字列カラムを作成し、そのカラムでグループ化します。

例:

SELECT
  DATE_FORMAT(date_column, '%Y-%m-%d') AS date_only,
  COUNT(*) AS count
FROM table_name
GROUP BY date_only
ORDER BY date_only;

解説:

DATE_FORMAT(date_column, '%Y-%m-%d') は、date_column の日付のみを YYYY-MM-DD 形式で抽出します。
COUNT(*) は、各グループ内のレコード数をカウントします。
GROUP BY date_only は、date_only カラムでグループ化します。
ORDER BY date_only は、結果を日付順に並べ替えます。

DATE 型に変換する

この方法は、DATE 型に変換することで時間情報を切り捨て、日付のみでグループ化します。

SELECT
  date_column AS date_only,
  COUNT(*) AS count
FROM table_name
GROUP BY date_column
ORDER BY date_column;

date_column は、DATE 型に変換されます。
時間情報は切り捨てられ、日付のみが残ります。
他の部分は最初の例と同じです。

どちらの方法でも同じ結果を得られますが、パフォーマンスや可読性などの観点からどちらを選ぶべきか決める必要があります。

パフォーマンス: DATE_FORMAT 関数は処理に時間がかかる場合があります。そのため、データ量が大きい場合は DATE 型に変換する方法の方がパフォーマンスが向上する可能性があります。
可読性: DATE_FORMAT 関数を使った方が、コードの意味が分かりやすくなります。

その他の注意点

どちらの方法を使う場合でも、date_column が NULL 値を含んでいる場合は、GROUP BY 句で NULL 値をどのように処理するかを指定する必要があります。
DATE_FORMAT 関数を使う場合は、使用する日付形式に注意する必要があります。

-- テーブルとデータの準備
CREATE TABLE IF NOT EXISTS my_table (
  id INT,
  date_column DATETIME
);

INSERT INTO my_table (id, date_column) VALUES
  (1, '2023-12-01 10:29:00'),
  (2, '2023-12-02 12:34:56'),
  (3, '2023-12-03 14:56:78'),
  (4, '2023-12-04 16:18:00'),
  (5, '2023-12-05 18:39:22');

-- 日付のみでグループ化
SELECT
  DATE_FORMAT(date_column, '%Y-%m-%d') AS date_only,
  COUNT(*) AS count
FROM my_table
GROUP BY date_only
ORDER BY date_only;

結果:

date_only | count
---------+-------
2023-12-01 | 1
2023-12-02 | 1
2023-12-03 | 1
2023-12-04 | 1
2023-12-05 | 1

-- テーブルとデータの準備
CREATE TABLE IF NOT EXISTS my_table (
  id INT,
  date_column DATETIME
);

INSERT INTO my_table (id, date_column) VALUES
  (1, '2023-12-01 10:29:00'),
  (2, '2023-12-02 12:34:56'),
  (3, '2023-12-03 14:56:78'),
  (4, '2023-12-04 16:18:00'),
  (5, '2023-12-05 18:39:22');

-- 日付のみでグループ化
SELECT
  date_column AS date_only,
  COUNT(*) AS count
FROM my_table
GROUP BY date_column
ORDER BY date_column;

date_only | count
---------+-------
2023-12-01 | 1
2023-12-02 | 1
2023-12-03 | 1
2023-12-04 | 1
2023-12-05 | 1

NULL 値を処理する

SELECT
  DATE_FORMAT(date_column, '%Y-%m-%d') AS date_only,
  COUNT(*) AS count
FROM my_table
GROUP BY date_only WITH ROLLUP;

日付形式を変更する

SELECT
  DATE_FORMAT(date_column, '%Y%m') AS date_only,
  COUNT(*) AS count
FROM my_table
GROUP BY date_only
ORDER BY date_only;

SQL/MySQLでDatetime型カラムを日付のみでグループ化する他の方法

EXTRACT 関数は、Datetime 型カラムから日付、月、年などの要素を抽出することができます。

SELECT
  EXTRACT(YEAR FROM date_column) AS year,
  EXTRACT(MONTH FROM date_column) AS month,
  COUNT(*) AS count
FROM my_table
GROUP BY year, month
ORDER BY year, month;

EXTRACT(YEAR FROM date_column) は、date_column から年を抽出します。

CASE 式を使うと、日付のみを抽出する独自のロジックを記述することができます。

SELECT
  CASE
    WHEN date_column BETWEEN '2023-12-01' AND '2023-12-31' THEN '2023-12'
    ELSE 'その他'
  END AS date_only,
  COUNT(*) AS count
FROM my_table
GROUP BY date_only
ORDER BY date_only;

この例では、date_column が 2023-12-01 から 2023-12-31 の範囲内であれば 2023-12 とし、それ以外の場合は その他 としています。

SELECT
  date_only,
  COUNT(*) AS count
FROM (
  SELECT
    DATE_FORMAT(date_column, '%Y-%m-%d') AS date_only
  FROM my_table
) AS t
GROUP BY date_only
ORDER BY date_only;