Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hidesroom

可視化SQL | LIFO と FIFO

Posted at

初めに

この問題は、SQLパズル #59
『後入れ先出し と 先入れ先出し』を解説します
手元に『SQL パズル』があればわかりやすいです

LIFO, FIFO は一度は耳にした事があるのでは？
と思います

LIFO(Last In, First Out)
　⇒ 直近に仕入れた商品から販売する
　⇒ 後入れ、先出し

FIFO(First In, First Out)
　⇒ 過去に仕入れた商品から販売する
　⇒ 先入れ、先出し

▼ LIFO, FIFO イメージ
re_0.png

このパズルでやろうとすることは
売れた商品の仕入価格をどう計算するか？ です

100個の商品が売れたとします
商品の仕入価格を
過去に仕入れた価格から計算する(LIFO)
または
直近に仕入れた価格から計算する(FIFO)・・の

2通りの方法で算出します・・・が
注意する点があります
　⇒ 同じ日に100個の商品を仕入れていない
　⇒ つまり、計100個の商品は
　　『違う仕入単価』商品の合計になる

ここを考慮する必要があります

テーブルとデータ

SQL
-- 在庫テーブル
CREATE TABLE WidgetInventory(
  receipt_nbr INTEGER NOT NULL PRIMARY KEY,
  purchase_date DATE NOT NULL,
  qty_on_hand INTEGER NOT NULL CHECK (qty_on_hand >= 0),
  unit_price DECIMAL (5,2) NOT NULL
);

 -- receipt_nbr   : 受入番号
 -- purchase_date : 仕入日
 -- qty_on_hand   : 入庫数(仕入た数)
 -- unit_price    : 仕入単価

INSERT INTO WidgetInventory VALUES(1, '2005-08-01', 15, 10.00);
INSERT INTO WidgetInventory VALUES(2, '2005-08-02', 25, 12.00);
INSERT INTO WidgetInventory VALUES(3, '2005-08-03', 40, 13.00);
INSERT INTO WidgetInventory VALUES(4, '2005-08-04', 35, 12.00);
INSERT INTO WidgetInventory VALUES(5, '2005-08-05', 45, 10.00);

-- テーブル確認
SELECT * FROM WidgetInventory;

▼ 出力

receipt_nbr purchase_date qty_on_hand unit_price
1 2005-08-01 15 10.00
2 2005-08-02 25 12.00
3 2005-08-03 40 13.00
4 2005-08-04 35 12.00
5 2005-08-05 45 10.00

このテーブルから100個売れた商品の仕入価格を
　① 仕入日を上から取出す(青色)
　② 仕入日を下から取出す(オレンジ)
これらを確認していきます

FIFO
　⇒ 仕入日を上から取出す(青色)
　⇒ 2005-08-01 から時系列に取出す
　　2005-08-04 で合計が100を超える

LIFO
　⇒ 仕入日を下から取出す(オレンジ)
　⇒ 2005-08-05 からさかのぼって取出す
　　 2005-08-03 で合計が100を超える

合計が100となる日を特定して
仕入価格を計算するのが
このパズルの面白いところです

▼ 100個分の商品を取出す
re_1.jpg

全組合せを作る(LIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2

最初に・・・
WidgetInventoryテーブルをCROSS JOINして
全組合せを作ります

WidgetInventory テーブルには5行のデータが有るので
5 x 5 = 25 行のデータが出来ます

このデータを加工してLIFOとなるような形にします
　⇒ 欲しい行はクリアな数字部分です

▼ 出力
re_1.png

元データ を作る(LIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
WHERE W1.purchase_date <= W2.purchase_date

ここから WHERE句でデータを絞り込みます
　⇒ W1.purchase_date <= W2.purchase_date
　⇒ 『W1.仕入日』と同じ or 後日となる『W2.仕入日』
　⇒ 15行が残ります

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ 全ての W2.purchase_date が条件に合う

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ 4つの W2.purchase_date が条件に合う
　⇒ 画像を確認

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ 2つの W2.purchase_date が条件に合う
　⇒ 画像を確認

これは何をやっているの・・(・_・?)

この組合せを作る理由は
後々わかってきます

▼ 出力(画像左側)
re_3.png

全組合せを作る事はこのパズルではよくやっています
下記記事を参照ください

元データ を加工する①(LIFO)

SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date <= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date

▼ 出力

purchase_date unit_price tot_qty_on_hand tot_cost
2005-08-01 10 160 1840.00
2005-08-02 12 145 1690.00
2005-08-03 13 120 1390.00
2005-08-04 12 80 870.00
2005-08-05 10 45 450.00

クエリの中を細かく見てみます
『元データ を作る(LIFO)』を再確認します

左側(W1)の purchase_date, unit_price で集約(GROUP BY)すると
右側(W2)の『過去の仕入単価』と『その時の個数』をSUM()して
過去分の『仕入価格』が計算できます
　⇒ 画像の青色部分

つまり、 元データ を作る(LIFO) で行った事は
W1.仕入日より前の、W2仕入日をJOINして
過去の仕入単価と個数を取出す・・準備です

▼ 過去分の『仕入価格』を計算する準備
re_4.png

過去分の『仕入価格』を計算してみる
　⇒ 下記画像を確認

仕入日：2005-08-05
　⇒ 『仕入単価』10円で45個入庫
　　　仕入合計450(10 x 45)円
～～～～～～～～～～～～～～～～～～～～～～
仕入日：2005-08-04
　⇒ 『仕入単価』12円で35個入庫
　　仕入合計420円(12 x 35)
　⇒ 2005-08-05の分と合算すると
　　全入庫数80個、全仕入合計870円
～～～～～～～～～～～～～～～～～～～～～～
仕入日：2005-08-03
　⇒ 『仕入単価』13円で40個入庫
　　仕入合計520円(13 x 40)
　⇒ 2005-08-04,2005-08-05 の分と合算すると
　　全入庫数120個、全仕入合計1390円

後は同じ要領です・・・

▼ 過去分の『仕入価格』を計算してみる
re_5.png

ようやくこのSQLの全貌が見えてきました・・が
このクイズで求めたいのは
商品100個分の『仕入価格』です

『仕入日』が2005-08-04の時
　⇒ 全入庫数は80

『仕入日』が2005-08-03の時
　⇒ 全入庫数は120

商品100個分を取出すのはもうひと手間必要で
次の作業は・・
『仕入日』2005-08-03 の行を取出してマイナス20する

元データ を加工する②(LIFO)

SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MAX(stock_date) FROM LIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS max_date
FROM LIFO AS L1

▼ 出力 (max_date 追加)

purchase_date unit_price tot_qty_on_hand tot_cost max_date
2005-08-01 10 160 1840.00 2005-08-03
2005-08-02 12 145 1690.00 2005-08-03
2005-08-03 13 120 1390.00 2005-08-03
2005-08-04 12 80 870.00 2005-08-03
2005-08-05 10 45 450.00 2005-08-03

『元データ を加工する①(LIFO)』を CTEで記述
　⇒ LIFO としました

確認するために・・
SELECT文の中に、max_dateカラムを追加しています
　⇒『全入荷数』が100以上となる stock_date
　⇒ 複数日あります
　　2005-08-01,2005-08-02,2005-08-03
　⇒ ここから新しい日付を選ぶ
　　 MAX(stock_date)
　　 2005-08-03

今はLIFOで仕入日を見ているので
MAX()を使います
次のFIFOでは、ここがMIN()になります

▼ 仕入日 2005-08-03 を取出す
re_6.png

やっとここまでたどり着きました
最後の仕事は・・
2005-08-03が保持している総在庫数から
20個を引き算すると
商品100個分の『仕入価格』が求まります

▼ 商品100個分の『仕入価格』
re_7.png

回答SQL(LIFO)

SQL
WITH LIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date <= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  LIFO AS L1
WHERE 
  stock_date = (SELECT MAX(stock_date) FROM LIFO AS L2 
                WHERE tot_qty_on_hand >= 100)

仕入日(2005-08-03)を取出す条件を
WHERE句に記載
　⇒ stock_date = (SELECT MAX(stock_date) ・・・
　⇒ 2005-08-03の行の取出し

▼出力(LIFO)

stock_date tot_cost tot_qty_on_hand tot_qty_on_hand - 100 unit_price cost
2005-08-03 1390 120 20 13 1130

LIFOで商品100個分の『仕入価格』は1130円です

tot_cost
　⇒ 仕入日(2005-08-03)での全仕入金額(1390円)
tot_qty_on_hand
　⇒ 仕入日(2005-08-03)での全入庫数(120個)
tot_qty_on_hand - 100
　⇒ 余分に多い20個の取出し
　　 120 - 100 = 20
unit_price
　⇒ 仕入日(2005-08-03)での仕入価格(13円)
cost
　⇒ 全仕入金額 - 20個分の仕入価格
　⇒ 1390 - (20 x 13) = 1130円

次に FIFO で算出してみます　୧(๑›◡‹ ๑)୨

全組合せを作る(FIFO)

SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2

LIFO と同様に
WidgetInventoryテーブルをCROSS JOINして
全組合せを作ります

このデータを加工してFIFOとなるような形にします
　⇒ 欲しい行クリアな数字部分です

▼ 出力(FIFO)
re_8.png

▼ W2.purchase_dateが違います(´◉ω◉`))

re_8_1.png

元データ を作る(FIFO)

SQL
SELECT W1.*, W2.*
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date 
     -- FIFOと向きが逆です

【memo】
LIFOとの違いは
比較演算子の向きが逆です

ここから WHERE句でデータを絞り込みます
　⇒ W1.purchase_date >= W2.purchase_date
　⇒ 『W1.仕入日』と同じ or 前日となる『W2.仕入日』
　⇒ 15行が残ります

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-01
　⇒ 1つの W2.purchase_date が条件に合う

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-02
　⇒ 2つの W2.purchase_date が条件に合う
　⇒ 画像を確認

～～～～～～～～～～～～～～～～～～～～～～
W1.purchase_date：2005-08-04
　⇒ 4つの W2.purchase_date が条件に合う
　⇒ 画像を確認

▼ 出力(画像左側)
re_9.png

元データ を加工する①(FIFO)

SQL
SELECT
  W1.purchase_date AS stock_date, 
  W1.unit_price AS unit_price,
  SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
  SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
FROM WidgetInventory AS W1, WidgetInventory AS W2
WHERE W1.purchase_date >= W2.purchase_date
GROUP BY W1.purchase_date, W1.unit_price

ORDER BY W1.purchase_date

▼ 出力

purchase_date unit_price tot_qty_on_hand tot_cost
2005-08-01 10 15 150.00
2005-08-02 12 40 450.00
2005-08-03 13 80 970.00
2005-08-04 12 115 1390.00
2005-08-05 10 160 1840.00

LIFOと同じように
クエリの中を細かく見てみます

左側(W1)の purchase_date, unit_price で集約(GROUP BY)すると
右側(W2)の『過去の仕入単価』と『その時の個数』をSUM()して
過去分の『仕入価格』が計算できます
　⇒ 画像の青色部分

▼ 過去分の『仕入価格』を算出する準備
re_10.png

過去分の『仕入価格』を算出してみる
　⇒ 下記画像を確認

仕入日：2005-08-01
　⇒ 『仕入単価』10円で15個入庫
　　仕入合計150(10 x 15)円
～～～～～～～～～～～～～～～～～～～～～～
仕入日：2005-08-03
　⇒ 『仕入単価』13円で40個入庫
　　仕入合計520円(13 x 40)
　⇒ 2005-08-01,2005-08-02の分と合算すると
　　全入庫数80個、全仕入合計970円

後は同じ要領です・・・

▼ 過去分の『仕入価格』を算出する
re_11.png

このクイズで求めたいのは
商品100個分の『仕入価格』です

『仕入日』が2005-08-03の時
　⇒ 全入庫数は80
　
『仕入日』が2005-08-04の時
　⇒ 全入庫数は115

商品100個分を取出すのはもうひと手間必要です
次の作業は・・
『仕入日』2005-08-04 の行を取出してマイナス15する

元データ を加工する②(FIFO)

SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT 
 L1.*,
 (SELECT MIN(stock_date) FROM FIFO AS L2 
  WHERE tot_qty_on_hand >= 100) AS min_date
FROM FIFO AS L1

▼ 出力 (min_date 追加)

purchase_date unit_price tot_qty_on_hand tot_cost min_date
2005-08-01 10 15 150.00 2005-08-04
2005-08-02 12 40 450.00 2005-08-04
2005-08-03 13 80 970.00 2005-08-04
2005-08-04 12 115 1390.00 2005-08-04
2005-08-05 10 160 1840.00 2005-08-04

『元データ を加工する①(FIFO)』を CTEで記述
　⇒ FIFO としました

確認するために・・
SELECT文の中に、min_dateカラムを追加しています
　⇒『全入荷数』が100以上となる stock_date
　⇒ 複数日あります
　　2005-08-04,2005-08-05
　⇒ ここから新しい日付を選ぶ
　　 MIN(stock_date)
　　 2005-08-04

LIFOではMAX()を使用しましたが
FIFOでは、ここがMIN()になります

▼ 仕入日 2005-08-04 を取出す
re_12.png

最後の仕事は・・
2005-08-04が保持している総在庫数から
15個を引き算すると
商品100個分の『仕入価格』が求まります

▼ 商品100個分の『仕入価格』
re_13.png

回答SQL(FIFO)

SQL
WITH FIFO AS (
  SELECT 
    W1.purchase_date AS stock_date, 
    W1.unit_price AS unit_price,
    SUM(W2.qty_on_hand) AS tot_qty_on_hand, 
    SUM(W2.qty_on_hand * W2.unit_price) AS tot_cost
  FROM WidgetInventory AS W1, WidgetInventory AS W2
  WHERE W1.purchase_date >= W2.purchase_date
  GROUP BY W1.purchase_date, W1.unit_price
)

SELECT
  stock_date,
  tot_cost,
  tot_qty_on_hand,
  (tot_qty_on_hand - 100),
  unit_price,
  (tot_cost - ((tot_qty_on_hand - 100) * unit_price)) AS cost
FROM 
  FIFO AS L1
WHERE 
  stock_date = (SELECT MIN(stock_date) FROM FIFO AS L2 
                WHERE tot_qty_on_hand >= 100)

仕入日(2005-08-04)を取出す条件を
WHERE句に記載
　⇒ stock_date = (SELECT MIN(stock_date) ・・・
　⇒ 2005-08-04の行の取出し

▼出力(FIFO)

stock_date tot_cost tot_qty_on_hand tot_qty_on_hand - 100 unit_price cost
2005-08-04 1390 115 15 12 1210

FIFOで商品100個分の『仕入価格』は1210円です

tot_cost
　⇒ 仕入日(2005-08-04)での全仕入金額(1390円)
tot_qty_on_hand
　⇒ 仕入日(2005-08-04)での全入庫数(115個)
tot_qty_on_hand - 100
　⇒ 余分に多い20個の取出し
　　 115- 100 = 15
unit_price
　⇒ 仕入日(2005-08-04)での仕入価格(12円)
cost
　⇒ 全仕入金額 - (20個分の仕入価格)
　⇒ 1390 - (15 x 12) = 1210円

以上となります

このクイズの題名は LIFO & FIFO ですが
このクイズのテクニックは
100個分の個数を取出す時のMAX,MIN関数の使用や
元データの組合せを作成する所にあるかと思います

参考文献

SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック

1
3
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?