MySQLのGROUP BY句で売上データを月と年ごとに集計する方法

MySQLで月と年ごとにグループ化する方法

このチュートリアルでは、MySQLで月と年ごとにデータをグループ化する方法について説明します。 具体的には、GROUP BY句と集計関数を使用して、売上データの月ごとの売上合計、年間売上合計、各月の売上平均などを算出する方法を紹介します。

必要条件

このチュートリアルを理解するには、以下の知識が必要です。

• MySQLの基本的な知識
• SELECT句、WHERE句などの基本的なSQLクエリ
• GROUP BY句と集計関数の基本的な使用方法

準備

まず、練習用のデータベースとテーブルを作成する必要があります。 ここでは、salesという名前のテーブルを作成し、以下の列を持つと仮定します。

• order_id: 注文ID (主キー)
• order_date: 注文日
• customer_id: 顧客ID
• product_id: 商品ID
• quantity: 注文個数
• unit_price: 単価
• total_price: 合計金額

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

月ごとの売上合計を算出する

以下のクエリは、salesテーブルのデータを月ごとにグループ化し、各月の売上合計を算出します。

SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  SUM(total_price) AS total_sales
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

このクエリは、以下の結果を返します。

SELECT
  YEAR(order_date) AS year,
  SUM(total_price) AS total_sales
FROM sales
GROUP BY YEAR(order_date)
ORDER BY year;

SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  AVG(total_price) AS average_sales
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

その他の集計関数

上記の例では、SUM関数を使用して売上合計を算出しましたが、他の集計関数も使用できます。 例えば、以下のようにCOUNT関数を使用して注文数を、MIN関数を使用して最小売上金額、MAX関数を使用して最大売上金額などを算出することができます。

-- 注文数
SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  COUNT(*) AS order_count
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

-- 最小売上金額
SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  MIN(total_price) AS min_sales
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER

必要なテーブル

CREATE TABLE sales (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE NOT NULL,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_customer (customer_id) REFERENCES customers(customer_id),
  CONSTRAINT fk_product (product_id) REFERENCES products(product_id)
);

以下のサンプルデータを使用して、クエリを実行してみましょう。

INSERT INTO sales (order_date, customer_id, product_id, quantity, unit_price, total_price)
VALUES
  ('2023-01-01', 1, 1, 2, 10.00, 20.00),
  ('2023-01-02', 2, 2, 3, 5.00, 15.00),
  ('2023-01-03', 1, 3, 1, 20.00, 20.00),
  ('2023-01-10', 3, 1, 4, 10.00, 40.00),
  ('2023-02-05', 1, 2, 2, 5.00, 10.00),
  ('2023-02-15', 2, 3, 1, 20.00, 20.00),
  ('2023-02-20', 3, 1, 3, 10.00, 30.00);

クエリ

SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  SUM(total_price) AS total_sales,
  COUNT(*) AS order_count,
  AVG(total_price) AS average_sales
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

このコードは、GROUP BY句と集計関数を使用して、月ごとの売上データを集計する方法を示しています。 このコードを参考に、自分のニーズに合わせてクエリをカスタマイズすることができます。

説明

• YEAR(order_date): この式は、注文日の年から年を抽出します。
• SUM(total_price): この式は、各月の売上合計を算出します。
• COUNT(*): この式は、各月の注文数を算出します。
• GROUP BY YEAR(order_date), MONTH(order_date): この句は、データを年と月ごとにグループ化します。
• ORDER BY year, month: この句は、結果を年と月の昇順で並べ替えます。

このサンプルコードは、MySQLで月と年ごとにデータをグループ化する方法を理解するのに役立つ出発点となるでしょう。

MySQLで月と年ごとにグループ化する方法：その他の方法

DATE_FORMAT関数を使用して、注文日を「YYYY-MM」形式の文字列に変換してから、GROUP BY句でグループ化することができます。

SELECT
  SUBSTRING(DATE_FORMAT(order_date, '%Y-%m'), 1, 5) AS year_month,
  SUM(total_price) AS total_sales
FROM sales
GROUP BY SUBSTRING(DATE_FORMAT(order_date, '%Y-%m'), 1, 5)
ORDER BY year_month;

集計関数の別名を使用する

集計関数に別名を付けることで、クエリ結果の見やすさを向上させることができます。

SELECT
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  SUM(total_price) AS total_sales,
  COUNT(*) AS order_count,
  AVG(total_price) AS average_price
FROM sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

SELECT
  CASE
    WHEN MONTH(order_date) = 1 THEN '1月'
    WHEN MONTH(order_date) = 2 THEN '2月'
    ...
    ELSE MONTH(order_date)
  END AS month_name,
  SUM(total_price) AS total_sales
FROM sales
GROUP BY CASE
    WHEN MONTH(order_date) = 1 THEN '1月'
    WHEN MONTH(order_date) = 2 THEN '2月'
    ...
    ELSE MONTH(order_date)
  END
ORDER BY month_name;

サブクエリを使用して、月ごとの売上合計を算出する列を作成してから、GROUP BY句でグループ化することができます。

SELECT
  year,
  month,
  total_sales
FROM (
  SELECT
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(total_price) AS total_sales
  FROM sales
  GROUP BY YEAR(order_date), MONTH(order_date)
) AS subquery
ORDER BY year, month;

MySQLで月と年ごとにデータをグループ化するには、さまざまな方法があります。 上記の方法はほんの一例であり、ニーズに合わせて最適な方法を選択することができます。

補足

• ここでは、基本的なクエリのみを紹介しています。 より複雑な分析を行う場合は、WHERE句やHAVING句などの他の句を組み合わせて使用することができます。
• 性能を向上させるために、適切なインデックスを作成することを検討してください。