PostgreSQLで月と年でグループ化クエリ結果を表示する方法

2024-04-02

PostgreSQLで月と年でグループ化クエリ結果を表示する方法

基本的な例

以下のクエリは、ordersテーブルから注文日と合計金額を取得し、月と年でグループ化して表示します。

SELECT
  date_trunc('month', order_date) AS month,
  year(order_date) AS year,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, year
ORDER BY year, month;

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

| month | year | total_sales |
|---|---|---|
| 2023-01 | 2023 | 10000 |
| 2023-02 | 2023 | 12000 |
| 2023-03 | 2023 | 15000 |
| ... | ... | ... |

date_trunc('month', order_date) は、注文日を月単位で切り捨てます。year(order_date) は、注文年の値を取得します。SUM(total_amount) は、各グループの合計金額を計算します。

より複雑な例

SELECT
  date_trunc('month', order_date) AS month,
  year(order_date) AS year,
  customer_id,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, year, customer_id
ORDER BY year, month, customer_id;

| month | year | customer_id | total_sales |
|---|---|---|---|
| 2023-01 | 2023 | 1 | 5000 |
| 2023-01 | 2023 | 2 | 7000 |
| 2023-02 | 2023 | 1 | 6000 |
| 2023-02 | 2023 | 2 | 8000 |
| ... | ... | ... | ... |

この例では、GROUP BY句にcustomer_idを追加することで、顧客IDごとに月と年の売上を集計しています。

その他のヒント

複数の集計関数を組み合わせることができます。
WHERE句を使用して、グループ化する前にデータをフィルタリングできます。

-- テーブル orders
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL
);

-- サンプルデータ挿入
INSERT INTO orders (order_date, customer_id, total_amount)
VALUES ('2023-01-01', 1, 1000),
       ('2023-01-15', 2, 2000),
       ('2023-02-05', 1, 3000),
       ('2023-02-10', 2, 4000),
       ('2023-03-01', 1, 5000),
       ('2023-03-15', 2, 6000);

-- 月と年でグループ化
SELECT
  date_trunc('month', order_date) AS month,
  year(order_date) AS year,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, year
ORDER BY year, month;

-- 結果
-- | month | year | total_sales |
-- |---|---|---|
-- | 2023-01 | 2023 | 3000 |
-- | 2023-02 | 2023 | 7000 |
-- | 2023-03 | 2023 | 11000 |

-- 月と年、顧客IDでグループ化
SELECT
  date_trunc('month', order_date) AS month,
  year(order_date) AS year,
  customer_id,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, year, customer_id
ORDER BY year, month, customer_id;

-- 結果
-- | month | year | customer_id | total_sales |
-- |---|---|---|---|
-- | 2023-01 | 2023 | 1 | 1000 |
-- | 2023-01 | 2023 | 2 | 2000 |
-- | 2023-02 | 2023 | 1 | 3000 |
-- | 2023-02 | 2023 | 2 | 4000 |
-- | 2023-03 | 2023 | 1 | 5000 |
-- | 2023-03 | 2023 | 2 | 6000 |

このコードを実行するには、PostgreSQLデータベースとサンプルデータが必要です。サンプルデータを挿入するには、INSERT INTOステートメントを実行します。

上記のサンプルコードは、月と年でグループ化クエリ結果を表示する方法を理解するのに役立ちます。

PostgreSQLで月と年でグループ化クエリ結果を表示する他の方法

CASE式を使用する

以下のクエリは、CASE式を使用して、月と年を結合した列を作成し、その列でグループ化しています。

SELECT
  CASE
    WHEN month = 1 THEN 'Jan'
    WHEN month = 2 THEN 'Feb'
    ...
  END AS month_name,
  year(order_date) AS year,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month_name, year
ORDER BY year, month_name;

| month_name | year | total_sales |
|---|---|---|
| Jan | 2023 | 3000 |
| Feb | 2023 | 7000 |
| Mar | 2023 | 11000 |

ウィンドウ関数を使用する

以下のクエリは、ウィンドウ関数ROW_NUMBER()を使用して、各月の中で注文が行われた順序を取得し、その順序でグループ化しています。

SELECT
  month,
  year,
  ROW_NUMBER() OVER (PARTITION BY month, year ORDER BY order_date) AS order_number,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY month, year
ORDER BY year, month, order_number;

| month | year | order_number | total_sales |
|---|---|---|---|
| 2023-01 | 2023 | 1 | 1000 |
| 2023-01 | 2023 | 2 | 2000 |
| 2023-02 | 2023 | 1 | 3000 |
| 2023-02 | 2023 | 2 | 4000 |
| 2023-03 | 2023 | 1 | 5000 |
| 2023-03 | 2023 | 2 | 6000 |

外部結合を使用する

以下のクエリは、月と年を表すテーブルを作成し、ordersテーブルと外部結合して、月と年でグループ化しています。

-- 月と年を表すテーブル
CREATE TABLE months (
  month_id INT PRIMARY KEY,
  month_name VARCHAR(10) NOT NULL
);

-- サンプルデータ挿入
INSERT INTO months (month_id, month_name)
VALUES (1, 'Jan'),
       (2, 'Feb'),
       ...;

-- 外部結合
SELECT
  m.month_name,
  year(order_date) AS year,
  SUM(total_amount) AS total_sales
FROM orders
LEFT JOIN months m ON date_trunc('month', order_date) = m.month_id
GROUP BY m.month_name, year
ORDER BY year, m.month_name;