売上分析に役立つ！PostgreSQLで日ごとの注文件数・平均単価・最大注文金額を簡単に集計

PostgreSQLでタイムスタンプを日ごとにグループ化する方法

必要なもの

• PostgreSQLデータベース
• タイムスタンプを含むテーブル

手順

1. EXTRACT関数を使用して、タイムスタンプ列から日付部分を抽出します。
2. GROUP BY句を使用して、抽出された日付部分でグループ化します。
3. 集計関数を使用して、必要な集計値を算出します。

例

次の例では、ordersテーブルのcreated_at列を日ごとにグループ化し、各日の注文件数をカウントします。

SELECT
  EXTRACT(DAY FROM created_at) AS order_date,
  COUNT(*) AS order_count
FROM orders
GROUP BY order_date
ORDER BY order_date;

このクエリは次の結果を返します。

order_date | order_count
-----------+------------
2024-05-15 | 10
2024-05-16 | 12
2024-05-17 | 15

その他の集計関数

COUNT以外にも、さまざまな集計関数を使用することができます。 例えば、以下のようなことができます。

• AVG(column_name): 列の平均値を算出します。

複数の列でグループ化することもできます。 例えば、ordersテーブルのcreated_at列とcustomer_id列でグループ化し、各顧客ごとの日別注文件数をカウントするには、次のようにクエリを記述します。

SELECT
  EXTRACT(DAY FROM created_at) AS order_date,
  customer_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY order_date, customer_id
ORDER BY order_date, customer_id;

PostgreSQLを使用してタイムスタンプを日ごとにグループ化することで、データの分析や可視化を容易に行うことができます。 上記の例を参考に、さまざまな集計関数やグループ化オプションを活用して、必要な情報を取得してください。

PostgreSQLでタイムスタンプを日ごとにグループ化する方法：サンプルコード

-- ordersテーブルのスキーマ
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

-- サンプルデータ挿入
INSERT INTO orders (customer_id, created_at, amount)
VALUES
  (1, '2024-05-15 10:00:00', 50.00),
  (1, '2024-05-16 12:00:00', 75.00),
  (1, '2024-05-17 15:30:00', 100.00),
  (2, '2024-05-16 11:00:00', 60.00),
  (2, '2024-05-17 16:00:00', 85.00);

-- タイムスタンプを日ごとにグループ化し、集計値を算出
SELECT
  EXTRACT(DAY FROM created_at) AS order_date,
  COUNT(*) AS order_count,
  AVG(amount) AS average_order_amount,
  MAX(amount) AS max_order_amount
FROM orders
GROUP BY order_date
ORDER BY order_date;

order_date | order_count | average_order_amount | max_order_amount
-----------+------------+--------------------+------------------
2024-05-15 | 1          | 50.00               | 50.00
2024-05-16 | 2          | 65.00               | 75.00
2024-05-17 | 2          | 92.50               | 100.00

説明:

1. EXTRACT(DAY FROM created_at) AS order_date: created_at列から日付部分のみを抽出し、order_dateという名前の別名で列を追加します。
2. COUNT(*) AS order_count: 全ての行をカウントし、order_countという名前の列に結果を格納します。
3. AVG(amount) AS average_order_amount: amount列の平均値を算出し、average_order_amountという名前の列に結果を格納します。
4. GROUP BY order_date: 上記の集計を、order_dateごとにグループ化して実行します。

このサンプルコードを参考に、必要な集計やグループ化に合わせて調整してください。

PostgreSQLでタイムスタンプを日ごとにグループ化する方法：その他の方法

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

PostgreSQLのウィンドウ関数を使用すると、集計をグループ化せずに実行できます。 例えば、ordersテーブルのcreated_at列を日ごとにグループ化し、各日の注文件数、平均注文金額、最大注文金額を算出するには、次のようにクエリを記述します。

SELECT
  created_at::date AS order_date,
  COUNT(*) OVER (PARTITION BY created_at::date) AS order_count,
  AVG(amount) OVER (PARTITION BY created_at::date) AS average_order_amount,
  MAX(amount) OVER (PARTITION BY created_at::date) AS max_order_amount
FROM orders
ORDER BY order_date;

サブクエリを使用して、日ごとの集計値を算出し、メインクエリで結果を結合する方法もあります。 例えば、次のようにクエリを記述できます。

WITH daily_orders AS (
  SELECT
    EXTRACT(DAY FROM created_at) AS order_date,
    COUNT(*) AS order_count,
    AVG(amount) AS average_order_amount,
    MAX(amount) AS max_order_amount
  FROM orders
  GROUP BY order_date
)
SELECT * FROM daily_orders
ORDER BY order_date;

ビューを使用する

日ごとの集計を頻繁に実行する場合は、ビューを作成して結果を保存しておくのも良い方法です。 例えば、次のようにビューを作成できます。

CREATE VIEW daily_order_summary AS
SELECT
  EXTRACT(DAY FROM created_at) AS order_date,
  COUNT(*) AS order_count,
  AVG(amount) AS average_order_amount,
  MAX(amount) AS max_order_amount
FROM orders
GROUP BY order_date;

その後、以下のクエリでビューから結果を取得できます。

SELECT * FROM daily_order_summary
ORDER BY order_date;

• データ量が少ない場合は、基本的な方法で十分でしょう。
• 集計を頻繁に実行する場合は、ビューを作成しておくと便利です。
• クエリをより柔軟に記述したい場合は、ウィンドウ関数を使用するのが良いでしょう。