補充判定レポート｜SQL演習

補充判定レポート

全商品について総在庫total_stockと総出荷total_shippedを取得します。在庫0はREORDER、在庫が出荷の2倍未満はWATCH、それ以外はOKとしてactionを付け、商品id順に表示してください。

⌘ / Ctrl + Enter

テーブル

読み込み中…

実行結果

ヒント

全商品を残すため、productsから2つの集計結果へLEFT JOINします。
NULLを0として扱ってからCASEの順序を決めます。

解説・解答例

要件を「在庫集約」「出荷集約」「全商品への結合」「優先順位付き判定」に分解します。先に0在庫を判定しないと、出荷0の商品がWATCH/OKへ流れる可能性があります。

sql

WITH stock_totals AS (
  SELECT product_id, SUM(quantity) AS total_stock FROM stock GROUP BY product_id
), shipment_totals AS (
  SELECT product_id, SUM(quantity) AS total_shipped FROM shipments GROUP BY product_id
)
SELECT p.name AS product,
       COALESCE(st.total_stock, 0) AS total_stock,
       COALESCE(sh.total_shipped, 0) AS total_shipped,
       CASE
         WHEN COALESCE(st.total_stock, 0) = 0 THEN 'REORDER'
         WHEN st.total_stock < COALESCE(sh.total_shipped, 0) * 2 THEN 'WATCH'
         ELSE 'OK'
       END AS action
FROM products p
LEFT JOIN stock_totals st ON st.product_id = p.id
LEFT JOIN shipment_totals sh ON sh.product_id = p.id
ORDER BY p.id;

演習一覧へ戻る →