PostgreSQLのストアドプロシージャを使って売上データを管理する

対象読者

簡単なSQLを理解されている方。

本記事のゴール

• 月次売上の集計処理をストアドプロシージャで実現します。
• ストアドプロシージャの基本的な書き方と活用方法を学びます。

ストアドプロシージャとは？

ストアドプロシージャは、データベースに保存された一連のSQLステートメントを実行するプログラムです。ネットワーク通信を減らし、効率的にデータ処理を行うことができます。

処理概要

店舗の1ヶ月の売上を集計し、売上数が少ない商品にフラグを立てます。

処理の流れは以下になります。

1. 日次売上データを月次売上テーブルに集計して登録する
2. 日次売上データを履歴テーブルに保存する
3. 日次売上テーブルの内容をクリアする
4. 古い履歴データを削除する
5. 売上が少ない商品にフラグを立てる

各処理の詳細

処理1: 日次売上テーブルから月次売上テーブルに登録する

日次売上データを集計し、月次売上テーブルに転送します。

INSERT INTO monthly_sales_table (
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
)
SELECT 
    item_name,
    cost_price,
    sale_price,
    SUM(sold_quantity) AS total_quantity,           -- 販売個数の合計
    sale_price * SUM(sold_quantity) AS total_sales  -- 売上金額（販売個数 × 売価）
FROM daily_sales_table
GROUP BY item_name, cost_price, sale_price;

処理2: 日次売上テーブルを履歴テーブルに登録する

INSERT INTO archived_daily_sales_table (
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
)
SELECT 
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
FROM daily_sales_table;

処理3: 日次売上テーブルの内容をクリアする

DELETE FROM daily_sales_table;

処理4: 履歴テーブルの古いデータを削除する

DELETE FROM archived_daily_sales_table
WHERE created_at < CURRENT_DATE - INTERVAL '2 years';

処理5: 売上が少ない商品にフラグを立てる

UPDATE monthly_sales_table SET sales_alert_flag = true
WHERE sold_quantity < 10;

ストアドプロシージャ全体

こちらの5つの処理を、ストアドプロシージャにまとめます。
名称はprocess_monthly_salesとし、引数なしで作成しています。

CREATE OR REPLACE PROCEDURE process_monthly_sales()
LANGUAGE plpgsql
AS $$
BEGIN

-- 処理1: 日次売上テーブルから月次売上テーブルに登録する
INSERT INTO monthly_sales_table (
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
)
SELECT 
    item_name,
    cost_price,
    sale_price,
    SUM(sold_quantity) AS total_quantity,           -- 販売個数の合計
    sale_price * SUM(sold_quantity) AS total_sales  -- 売上金額（販売個数 × 売価）
FROM daily_sales_table
GROUP BY item_name, cost_price, sale_price;


-- 処理2: 日次売上テーブルを日次売上_履歴テーブルに登録する
INSERT INTO archived_daily_sales_table (
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
)
SELECT 
    item_name,         -- 商品名
    cost_price,        -- 原価
    sale_price,        -- 売価
    sold_quantity,     -- 販売個数
    total_sales        -- 売上
FROM daily_sales_table;


-- 処理3: 日次売上テーブルの内容をクリアする
DELETE FROM daily_sales_table;


-- 処理4: 日次売上_履歴テーブルの2年以上前のデータを削除する
DELETE FROM archived_daily_sales_table
WHERE created_at < CURRENT_DATE - INTERVAL '2 years';


-- 処理5: 売上が少なかった商品にフラグを立てる
UPDATE monthly_sales_table
SET sales_alert_flag = true
WHERE sold_quantity < 10;


END;
$$;

実行方法

CALL process_monthly_sales();

---

まとめ

ストアドプロシージャは、複数のSQL処理を一括して管理することで、業務効率を大幅に向上させる便利なツールです。今回の記事では、日次売上データを管理するための具体的な5つの処理を紹介しました。

• 処理の流れ:

  日次売上データを月次売上テーブルに集計 → 履歴として保存 → 日次売上をクリア → 古いデータの削除 → 売上が少ない商品にフラグを付与、という一連の流れをストアドプロシージャで自動化しました。

• 活用のメリット:

  このストアドプロシージャを実行すれば、売上データの整理・集計・監視までを一括で効率よく処理できます。さらに、ネットワーク通信を最小化し、SQLクエリの実行速度を向上させることも期待できます。

最後に

記事を読んでくださった方は、是非弊社開発課のXもフォローしてください。
毎日エンジニアに向けた情報発信を行っています。