在庫管理に役立つ！PostgreSQLで商品別累積在庫をリアルタイム集計

2024-07-02

PostgreSQLで累積合計カウントを取得する方法

ウィンドウ関数は、通常の集計関数とは異なり、現在の行だけでなく、その行の前後のレコードも含めた範囲（ウィンドウ）に対して集計処理を実行することができます。これにより、レコードのシーケンスにおける累積的な値を計算することが可能になります。

PostgreSQLには、様々なウィンドウ関数が用意されていますが、累積合計カウントを取得するために特に役立つ関数は以下の2つです。

SUM()関数: 指定した列の値を合計します。
COUNT()関数: 指定した条件に合致するレコードの個数をカウントします。

累積合計カウントの取得方法

以下に、PostgreSQLで累積合計カウントを取得する基本的な例を示します。

SELECT
  id,
  transaction_date,
  amount,
  SUM(amount) OVER (ORDER BY transaction_date) AS running_total
FROM transactions;

このクエリは、transactionsテーブルからすべてのレコードを選択し、各レコードのID、取引日、金額、および取引日順に累積合計金額を表示します。

応用例

累積合計カウントは、様々な分析やレポート作成に活用できます。以下に、具体的な例をいくつか紹介します。

売上推移の分析: 商品の売上個数や売上金額の累積合計を算出することで、時間の経過とともに売上どのように推移しているかを分析することができます。
顧客ごとの購入履歴の分析: 顧客ごとの注文数や購入金額の累積合計を算出することで、各顧客の購買傾向を分析することができます。
在庫管理: 商品ごとの在庫数の累積合計を算出することで、現在の在庫状況を把握することができます。

まとめ

PostgreSQLのウィンドウ関数を使用することで、レコードの集まりに対して簡単に累積合計カウントを取得することができます。この機能は、分析やレポート作成において非常に役立つので、ぜひ活用してみてください。

PostgreSQLで累積合計カウントを取得するサンプルコード

データセット

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  transaction_date DATE NOT NULL,
  customer_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
);

INSERT INTO transactions (transaction_date, customer_id, amount)
VALUES
  ('2023-10-05', 123, 100.00),
  ('2023-10-05', 456, 50.00),
  ('2023-10-06', 123, 200.00),
  ('2023-10-07', 321, 150.00),
  ('2023-10-07', 456, 75.00);

このテーブルには、取引ID、取引日、顧客ID、金額の4つの列が含まれています。

顧客ごとの累積売上金額

以下のクエリは、各顧客の取引日順に累積売上金額を算出します。

SELECT
  customer_id,
  transaction_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS running_total
FROM transactions;

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

customer_id | transaction_date | amount | running_total
-------------+-----------------+---------+--------------
123          | 2023-10-05      | 100.00 | 100.00
123          | 2023-10-06      | 200.00 | 300.00
321          | 2023-10-07      | 150.00 | 150.00
456          | 2023-10-05      | 50.00  | 50.00
456          | 2023-10-07      | 75.00 | 125.00

この結果から、顧客123は2023年10月に合計300ドル、顧客321は150ドル、顧客456は125ドルの売上を達成していることがわかります。

日付ごとの累積売上個数

SELECT
  transaction_date,
  COUNT(*) OVER (ORDER BY transaction_date) AS running_total
FROM transactions;

transaction_date | running_total
-----------------+--------------
2023-10-05      | 2
2023-10-06      | 3
2023-10-07      | 5

この結果から、2023年10月5日には2件、10月6日には3件、10月7日には5件の取引が行われたことがわかります。

その他の例

上記の例以外にも、様々な集計関数や条件式を組み合わせて、様々な累積合計カウントを算出することができます。

特定の顧客グループの累積合計カウント
カテゴリごとの累積合計カウント

ご自身のニーズに合わせて、自由にクエリをカスタマイズしてみてください。

サブクエリを使用する方法では、現在の行よりも前の行の合計値を計算するサブクエリを内側のクエリで定義し、それを外側のクエリで参照します。

SELECT
  id,
  transaction_date,
  amount,
  (
    SELECT SUM(amount)
    FROM transactions AS t2
    WHERE t2.transaction_date <= t1.transaction_date
  ) AS running_total
FROM transactions AS t1;

このクエリは、transactionsテーブルを2回参照する必要があります。そのため、ウィンドウ関数を使用する方法よりも処理速度が遅くなる可能性があります。しかし、複雑な条件式が必要な場合などに有効な方法です。

CTEを使用する方法

CTE（Common Table Expression）を使用する方法では、一時的な中間テーブルを作成し、そのテーブル上で累積合計を計算します。

WITH running_totals AS (
  SELECT
    id,
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date) AS running_total
  FROM transactions
)
SELECT * FROM running_totals;

この方法は、サブクエリを使用する方法よりも処理速度が速くなります。また、複雑な集計処理をCTEにカプセル化することで、クエリをより読みやすくすることができます。

それぞれの方法の比較

方法	利点	欠点
ウィンドウ関数	シンプルで処理速度が速い	複雑な条件式には不向き
サブクエリ	複雑な条件式に対応できる	処理速度が遅い
CTE	処理速度が速く、クエリをわかりやすくできる	サブクエリよりも記述が複雑

どの方法を使用するかは、データ量、クエリ複雑性、パフォーマンス要件などの状況に応じて判断する必要があります。

一般的には、データ量が少ない場合やシンプルなクエリの場合はウィンドウ関数を使用し、データ量が多い場合や複雑なクエリの場合はサブクエリやCTEを使用するのが良いでしょう。

上記以外にも、PostgreSQLには様々な集計関数や条件式が用意されています。これらの機能を組み合わせることで、より複雑な累積合計カウントを取得することも可能です。

sql postgresql aggregate-functions