初めに

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

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

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

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

▼ LIFO, FIFO イメージ

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

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個分の商品を取出す

全組合せを作る(LIFO)

SQL

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

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

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

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

▼ 出力

元データを作る(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 が条件に合う
　⇒ 画像を確認

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

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

▼ 出力(画像左側)

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

元データを加工する①(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して
過去の仕入単価と個数を取出す・・準備です

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

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

仕入日：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円

後は同じ要領です・・・

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

ようやくこの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 を取出す

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

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

回答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)

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

元データを作る(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 が条件に合う
　⇒ 画像を確認

▼ 出力(画像左側)

元データを加工する①(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()して
過去分の『仕入価格』が計算できます
　⇒ 画像の青色部分

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

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

仕入日：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円

後は同じ要領です・・・

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

このクイズで求めたいのは
商品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 を取出す

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

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

回答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, ミック