Puzzle35 - SQLパズル演習

問題

• 在庫の部品残数を累計的に求める

  req_date   | req_qty | req_onhand_qty |
  -----------+---------+----------------+
  1994-07-01 |     100 |            100 |
　1994-07-02 |     120 |            220 |
　1994-07-03 |    -150 |             70 |
　1994-07-04 |      50 |            120 |
　1994-07-05 |     -35 |             85 |

fumikoの回答

SQL文

 SELECT   a1.req_date, sum(a2.req_qty) as req_onhand_qty
 FROM     InventoryAdjustments as a1,InventoryAdjustments as a2
 WHERE    a1.req_date >= a2.req_date
 GROUP BY a1.req_date

考え方

• ２つのテーブルa1,a2を用意
• a1の日付>=a2の日付の条件で組み合わせる

(↓group byする前の状態。select a1.req_date,a2.req_qtyとしたとき)

   a1date   | a2qty
 -----------+-------
 1994-07-01 |   100
 1994-07-02 |   100
 1994-07-02 |   120
 1994-07-03 |   100
 1994-07-03 |   120
 1994-07-03 |  -150
 1994-07-04 |   100
 1994-07-04 |   120
 1994-07-04 |  -150
 1994-07-04 |    50
 1994-07-05 |   100
 1994-07-05 |   120
 1994-07-05 |  -150 
 1994-07-05 |    50
 1994-07-05 |   -35

• 日付ごとにグループ化して合計を求める

実行結果

  req_date  | req_onhand_qty
  ----------+----------------
 1994-07-01 |            100
 1994-07-02 |            220
 1994-07-03 |             70
 1994-07-04 |            120
 1994-07-05 |             85

satomiの回答

SQL文

SELECT I1.req_date,I1.req_qty,(SELECT SUM(req_qty)
                               FROM InventoryAdjustments I2
                               WHERE I1.req_date>=I2.req_date)as req_onhand_qty
FROM InventoryAdjustments I1;

考え方

• SELECT節の副問合わせを考える。
  • 自己結合を使う
  • 1994-07-01→1994-07-01
  • 1994-07-02→1994-07-01,1994-07-02......
  • このように、I1.req_dateと日付が同じかそれ以前の、要求数量の合計を計算する。
• InventoryAdjustments　I1から要求日、要求数量、累計を求める。

実行結果

 ------------+---------+----------------+
  req_date   | req_qty | req_onhand_qty |
  -----------+---------+----------------+
  1994-07-01 |     100 |            100 |
　1994-07-02 |     120 |            220 |
　1994-07-03 |    -150 |             70 |
　1994-07-04 |      50 |            120 |
　1994-07-05 |     -35 |             85 |

おさらい

PostgreSQLで解1と解2のコストを計算してみました。

解1の処理コスト

QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Index Scan using inventoryadjustments_pkey on inventoryadjustments a1  (cost=0.
00..56302.10 rows=1940 width=8) (actual time=0.332..0.543 rows=5 loops=1)
   SubPlan
     ->  Aggregate  (cost=28.97..28.98 rows=1 width=4) (actual time=0.053..0.054
 rows=1 loops=5)
           ->  Bitmap Heap Scan on inventoryadjustments a2  (cost=9.26..27.35 ro
ws=647 width=4) (actual time=0.016..0.021 rows=3 loops=5)
                 Recheck Cond: (req_date <= $0)
                 ->  Bitmap Index Scan on inventoryadjustments_pkey  (cost=0.00.
.9.10 rows=647 width=0) (actual time=0.009..0.009 rows=3 loops=5)
                       Index Cond: (req_date <= $0)
 Total runtime: 0.811 ms
(8 行)

解2の処理コスト

                                 QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---
GroupAggregate  (cost=0.00..47677.40 rows=200 width=12) (actual time=0.106..0.2
63 rows=5 loops=1)
   ->  Nested Loop  (cost=0.00..38265.91 rows=1254533 width=12) (actual time=0.0
45..0.184 rows=15 loops=1)
         ->  Index Scan using inventoryadjustments_pkey on inventoryadjustments
a1  (cost=0.00..77.35 rows=1940 width=8) (actual time=0.022..0.033 rows=5 loops=
1)
         ->  Index Scan using inventoryadjustments_pkey on inventoryadjustments
a2  (cost=0.00..11.60 rows=647 width=8) (actual time=0.005..0.012 rows=3 loops=5
)
               Index Cond: (a2.req_date <= a1.req_date)
Total runtime: 0.464 ms
(6 行)

GROUP BYを使った解2より、サブクエリを使った解1のほうが早いはずですが・・・以上より、解2の方がコストが低いってなぜですかね＊￣O￣)ノ