MySQL GROUP BYを使いこなして、ビジネスに役立つ分析レポートを作成しよう！

MySQLで日付をグループ化して集計する方法：GROUP BYによる日/月/年別集計

本記事では、GROUP BY句を用いた日/月/年別の集計方法について、具体的な例を交えて分かりやすく解説します。

準備

まず、以下のテーブル orders を用意します。

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATETIME,
  product_id INT,
  quantity INT,
  total_price DECIMAL(10,2)
);

このテーブルには、注文ID、顧客ID、注文日時、商品ID、数量、合計金額などの情報が含まれています。

日別集計

日別で注文数を集計するには、以下のSQLクエリを使用します。

SELECT DATE(order_date) AS order_day, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_date);

このクエリは、**DATE(order_date)**関数を使用して、order_date列から日付のみを抽出し、order_dayという別名で出力します。

また、**COUNT(*)**関数を使用して、order_dayごとに注文数を集計し、order_countという別名で出力します。

結果例

order_day | order_count
----------+------------
2024-03-08 | 10
2024-03-09 | 15
2024-03-10 | 20

月別集計

SELECT MONTH(order_date) AS order_month, COUNT(*) AS order_count
FROM orders
GROUP BY MONTH(order_date);

order_month | order_count
------------+------------
1           | 25
2           | 30
3           | 45

年別集計

SELECT YEAR(order_date) AS order_year, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date);

order_year | order_count
------------+------------
2023       | 100
2024       | 75

複数の列をグループ化

複数の列を同時にグループ化することも可能です。例えば、日別と商品ID別に注文数を集計するには、以下のSQLクエリを使用します。

SELECT DATE(order_date) AS order_day, product_id, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_date), product_id;

このクエリは、DATE(order_date)とproduct_id列をGROUP BY句で指定することで、日別と商品ID別に注文数を集計します。

order_day | product_id | order_count
----------+------------+------------
2024-03-08 | 1          | 5
2024-03-08 | 2          | 3
2024-03-09 | 1          | 7
2024-03-09 | 2          | 8

集計関数

GROUP BY句と組み合わせて、さまざまな集計関数を使用することができます。

• COUNT(*): データの数を集計
• SUM(): 数値型の列の合計値を計算
• AVG(): 数値

準備

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATETIME,
  product_id INT,
  quantity INT,
  total_price DECIMAL(10,2)
);

INSERT INTO orders (order_id, customer_id, order_date, product_id, quantity, total_price)
VALUES
  (1, 100, '2023-03-08 10:00:00', 1, 1, 100),
  (2, 101, '2023-03-08 11:00:00', 2, 2, 200),
  (3, 102, '2023-03-09 12:00:00', 1, 3, 300),
  (4, 103, '2023-03-09 13:00:00', 2, 4, 400),
  (5, 100, '2023-03-10 14:00:00', 1, 5, 500),
  (6, 101, '2023-03-10 15:00:00', 2, 6, 600),
  (7, 102, '2024-03-08 16:00:00', 1, 7, 700),
  (8, 103, '2024-03-08 17:00:00', 2, 8, 800),
  (9, 100, '2024-03-09 18:00:00', 1, 9, 900),
  (10, 101, '2024-03-09 19:00:00', 2, 10, 1000);

日別集計

SELECT DATE(order_date) AS order_day, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_date);

月別集計

SELECT MONTH(order_date) AS order_month, COUNT(*) AS order_count
FROM orders
GROUP BY MONTH(order_date);

年別集計

SELECT YEAR(order_date) AS order_year, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date);

複数の列をグループ化

SELECT DATE(order_date) AS order_day, product_id, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(order_date), product_id;

集計関数

-- 日別と商品ID別 の合計金額
SELECT DATE(order_date) AS order_day, product_id, SUM(total_price) AS total_revenue
FROM orders
GROUP BY DATE(order_date), product_id;

-- 日別 の 平均注文数
SELECT DATE(order_date) AS order_day, AVG(quantity) AS average_quantity
FROM orders
GROUP BY DATE(order_date);

MySQLで日付をグループ化して集計するには、GROUP BY句を使用します。DATE()、MONTH()、**YEAR()**などの関数と組み合わせることで、日別、月別、年別など、さまざまな粒度でデータを分析することができます。

また、COUNT(*)、SUM()、AVG()などの集計関数と組み合わせて、データの件数、合計値、平均値などを計算することができます。

日付をグループ化して集計するその他の方法

CASE式

CASE式を使用して、日付を特定の条件に基づいてグループ化することができます。

SELECT
  CASE
    WHEN MONTH(order_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN MONTH(order_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN MONTH(order_date) BETWEEN 7 AND 9 THEN 'Q3'
    ELSE 'Q4'
  END AS quarter,
  COUNT(*) AS order_count
FROM orders
GROUP BY quarter;

このクエリは、CASE式を使用して、order_date列を四半期ごとにグループ化し、各四半期の注文数を集計します。

サブクエリを使用して、日付をグループ化してから集計することができます。

SELECT
  t.quarter,
  COUNT(*) AS order_count
FROM (
  SELECT
    CASE
      WHEN MONTH(order_date) BETWEEN 1 AND 3 THEN 'Q1'
      WHEN MONTH(order_date) BETWEEN 4 AND 6 THEN 'Q2'
      WHEN MONTH(order_date) BETWEEN 7 AND 9 THEN 'Q3'
      ELSE 'Q4'
    END AS quarter
  FROM orders
) AS t
GROUP BY t.quarter;

ウィンドウ関数

MySQL 8.0以降では、ウィンドウ関数を使用して、日付をグループ化してから集計することができます。

SELECT
  DATE(order_date) AS order_day,
  COUNT(*) OVER (PARTITION BY DATE(order_date)) AS order_count
FROM orders;

どの方法を使用するべきかは、要件と使用しているMySQLのバージョンによって異なります。

GROUP BY句は、最もシンプルで汎用性の高い方法です。

サブクエリは、より複雑なグループ化ロジックが必要な場合に役立ちます。

ウィンドウ関数は、MySQL 8.0以降を使用している場合は、より効率的な方法で日付をグループ化するのに役立ちます。

MySQLで日付をグループ化して集計するには、GROUP BY句、CASE式、サブクエリ、ウィンドウ関数などの方法を使用することができます。