可視化SQL | LIFO と FIFO 

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

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

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

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

▼ LIFO, FIFO イメージ
![re_0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ee943fff-4398-815c-d5da-2c01b9c648b0.png)




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

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

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

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

# テーブルとデータ
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0df59348-93bb-c4ac-61cc-2a05b02ae82b.jpeg)


# 全組合せを作る(LIFO)
```SQL:SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
```

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

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

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

▼ 出力
![re_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c4e23217-29da-c9b8-a7f2-04513c5e8622.png)



# 元データ を作る(LIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7fc7943a-2aa1-f51b-5cd8-49f3b228c58a.png)



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

https://qiita.com/hidesroom/items/a6fdcf939c54d99b2417


# 元データ を加工する①(LIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/194abea5-913a-351f-eed9-66b44ada755e.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ac277886-0cab-68e9-b9bf-1a05a4b43c08.png)

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

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

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


# 元データ を加工する②(LIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5f0f42b4-81bc-b77d-0759-8581e8c5952c.png)



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

▼ **商品100個分の『仕入価格』**
![re_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0e6b3695-0d7b-7684-8871-daa655dbdbf2.png)




# 回答SQL(LIFO)
```SQL: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:SQL
SELECT W1.*, W2.*									
FROM WidgetInventory AS W1, WidgetInventory AS W2	
```

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


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

▼ 出力(FIFO)
![re_8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2b1158f1-aab4-3350-d106-55fa7f12a0aa.png)

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

![re_8_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/14c5ab59-489f-70fb-e367-e57ba4e02371.png)


# 元データ を作る(FIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ca384a1f-b3a6-34db-d288-4be6eb960180.png)


# 元データ を加工する①(FIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/815d2ba6-929f-409b-2b13-e6760c0e5c22.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a35257c8-e924-4a57-d3a1-c95db4ebfb5b.png)


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

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

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

# 元データ を加工する②(FIFO)
```SQL: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/596d9e06-8803-f9de-b39f-a50f7a8a2f23.png)



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

▼ **商品100個分の『仕入価格』**
![re_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0d2f8172-f1c9-c458-085b-a5ed861851d6.png)


# 回答SQL(FIFO)
```SQL: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, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)