データベースを使いこなそう！SQLで月と年ごとにデータをグルーピングする方法

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

例

売上データのテーブル sales があり、date 列に日付情報、amount 列に売上金額が格納されているとします。 このテーブルを月と年ごとにグループ化し、売上合計額を求めるには、以下のクエリを実行します。

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

解説

• YEAR(date)：date 列の年を抽出します。
• SUM(amount)：amount 列の値を合計します。
• GROUP BY YEAR(date), MONTH(date)：YEAR(date) と MONTH(date) の値ごとにグループ化します。
• ORDER BY year, month：結果を年と月の昇順でソートします。

その他の集計関数

SUM 以外にも、AVG (平均)、MIN (最小値)、MAX (最大値) などの集計関数を使用できます。 例えば、売上平均を求めるには、以下のように AVG 関数を使用します。

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

注意事項

• グループ化する列は、数値型である必要はありません。 文字列列をグループ化することもできます。
• 複数の列をグループ化するには、, で区切って列を指定します。 例えば、顧客 ID と国ごとにグループ化するには、以下のようにします。

SELECT
  customer_id,
  country,
  SUM(amount) AS total_sales
FROM sales
GROUP BY customer_id, country
ORDER BY customer_id, country;

SQL Server 以外のデータベース

上記のクエリは、SQL Server 以外にも、MySQL、PostgreSQL などの主要なデータベースでほぼ同様に動作します。 ただし、データベースによっては、集計関数の名前や構文がわずかに異なる場合があります。

-- サンプルデータの作成
CREATE TABLE sales (
  id INT IDENTITY PRIMARY KEY,
  date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO sales (date, amount) VALUES
  ('2023-01-10', 100.00),
  ('2023-01-20', 50.00),
  ('2023-02-15', 120.00),
  ('2023-02-22', 80.00),
  ('2023-03-10', 150.00),
  ('2023-03-25', 60.00);

-- 月と年ごとに売上合計額をグループ化して表示
SELECT
  YEAR(date) AS year,
  MONTH(date) AS month,
  SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(date), MONTH(date)
ORDER BY year, month;

このコードは、以下の操作を実行します。

1. sales という名前のテーブルを作成します。 このテーブルには、id、date、amount という 3 つの列があります。
2. サンプルデータを sales テーブルに挿入します。 このデータは、2023 年 1 月から 3 月までの売上を表します。
3. sales テーブルを月と年ごとにグループ化し、売上合計額を計算します。
4. 結果を年と月の昇順でソートします。

実行結果

year | month | total_sales
------- | -------- | --------
2023 | 1 | 150.00
2023 | 2 | 200.00
2023 | 3 | 210.00

この結果は、2023 年 1 月の売上合計額が 150 ドル、2 月の売上合計額が 200 ドル、3 月の売上合計額が 210 ドルであることを示しています。

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

CASE 式を使用して、日付列を月と年の値に変換してから、GROUP BY 句でグループ化できます。

SELECT
  CASE
    WHEN MONTH(date) = 1 THEN 'January'
    WHEN MONTH(date) = 2 THEN 'February'
    ...
    ELSE MONTH(date)
  END AS month_name,
  YEAR(date) AS year,
  SUM(amount) AS total_sales
FROM sales
GROUP BY CASE
      WHEN MONTH(date) = 1 THEN 'January'
      WHEN MONTH(date) = 2 THEN 'February'
      ...
      ELSE MONTH(date)
    END,
  YEAR(date)
ORDER BY year, month_name;

SELECT
  y.year,
  m.month,
  SUM(amount) AS total_sales
FROM sales s
JOIN (
  SELECT DISTINCT YEAR(date) AS year
  FROM sales
) AS y
ON s.YEAR(date) = y.year
JOIN (
  SELECT DISTINCT MONTH(date) AS month
  FROM sales
) AS m
ON s.MONTH(date) = m.month
GROUP BY y.year, m.month
ORDER BY y.year, m.month;

ピボットテーブルを使用する

多くのデータベース管理システムには、ピボットテーブルというツールが組み込まれています。 ピボットテーブルを使用すると、データを簡単にグループ化して集計できます。

各方法の比較

どの方法を選択するかは、状況によって異なります。 シンプルでわかりやすい方法が必要な場合は、GROUP BY 句と集計関数を使用する方法がおすすめです。 月の名前を取得する必要がある場合は、CASE 式を使用する方法がおすすめです。 より柔軟な方法が必要な場合は、集計関数とサブクエリを使用する方法がおすすめです。 使いやすい方法が必要な場合は、ピボットテーブルを使用する方法がおすすめです。