泥庭

2013年5月22日

SQLWorldにお邪魔してきました

Filed under: 勉強会 — タグ: — yone64 @ 12:08 AM

平日開催のSQLWorldに行ってきました。
http://sqlworld.org/event/20130521/

SQLを書いてみよう。なテーマだったのですが。そのうち、一問に実際過去に業務で悩んだやつをベースにしたものを採用してもらいました(8年ぐらい前?)。↓の7問目です。
http://tsqlrunner.azurewebsites.net/20130521.html

とりあえず、当時を思い出しながら復習を書いてみます。

1.とりあえず何はなくとも、部品表テーブルに在庫テーブルはJoinします。よね?

製品名 材料名 必要量 数量
TV台 ガラス 2 5
TV台 ネジ 6 10
TV台 8 13
ネジ 4 10
3 13
食器棚 ガラス 2 5
食器棚 ネジ 16 10
食器棚 8 13
SELECT 部品表.製品名, 
       部品表.材料名, 
       部品表.必要量, 
       在庫.数量 
FROM   部品表 
       INNER JOIN 
       在庫 
       ON 部品表.材料名 = 在庫.材料名;

2.そうすると、製品名、材料名毎に部品が足りているかが分かります。

製品名 材料名 必要量 数量 十分
TV台 ガラス 2 5
TV台 ネジ 6 10
TV台 8 13
ネジ 4 10
3 13
食器棚 ガラス 2 5
食器棚 ネジ 16 10 ×
食器棚 8 13
SELECT 部品表.製品名, 
       部品表.材料名, 
       部品表.必要量, 
       在庫.数量, 
       CASE WHEN 部品表.必要量 <= 在庫.数量 THEN 'o' ELSE 'x' END AS 十分 
FROM   部品表 
       INNER JOIN 
       在庫 
       ON 部品表.材料名 = 在庫.材料名; 

3.これを、製品名毎に区切ってすべて○だったら作成可能な訳ですが、それが結構難しい。逆に、作成不可能なものは簡単に抽出できます。↓な感じ。

製品名
食器棚
SELECT DISTINCT 部品表.製品名 
FROM   部品表 
       INNER JOIN 
       在庫 
       ON 部品表.材料名 = 在庫.材料名 
WHERE  部品表.必要量 > 在庫.数量;

4.じゃあ、全体から作成不能なものをひけば良いよね。的発想で、当時の限界がコレ。

製品名
TV台
SELECT DISTINCT 製品名 
FROM   部品表 
WHERE  製品名 NOT IN (SELECT 部品表.製品名 
                   FROM   部品表 
                          INNER JOIN 
                          在庫 
                          ON 部品表.材料名 = 在庫.材料名 
                   WHERE  部品表.必要量 > 在庫.数量);

で、NOT INって件数増えると重いですよね。ってのが当時の思い出。(現実はもっと複雑だったためNOT INが多段で刺さってました)
NOT EXISTSだと、INDEXが効くらしいのでもう少しマシだったかもしれません。

ここからが今日の見聞きしてきた話。

2’.数量と必要量との差分を計算して、差分がマイナスだったら足りてないわけですね。

製品名 材料名 必要量 数量 差分
TV台 ガラス 2 5 3
TV台 ネジ 6 10 4
TV台 8 13 5
ネジ 4 10 6
3 13 10
食器棚 ガラス 2 5 3
食器棚 ネジ 16 10 -6
食器棚 8 13 5
SELECT 部品表.製品名, 
       部品表.材料名, 
       部品表.必要量, 
       在庫.数量, 
       在庫.数量 - 部品表.必要量 AS 差分 
FROM   部品表 
       INNER JOIN 
       在庫 
       ON 部品表.材料名 = 在庫.材料名;

3’.製品に、一つでもマイナス値があればダメなので、製品毎に最小値をとればOKです。でも、この最小値をとる発想が出てこない。

製品名 最小値
TV台 3
6
食器棚 -6
SELECT   部品表.製品名, 
         MIN(在庫.数量 - 部品表.必要量) AS 最小値 
FROM     部品表 
         INNER JOIN 
         在庫 
         ON 部品表.材料名 = 在庫.材料名 
GROUP BY 部品表.製品名; 

4’.ここまで来るとあとは、最小値が0以上のものを選ぶだけです。

製品名
TV台
SELECT   部品表.製品名 
FROM     部品表 
         INNER JOIN 
         在庫 
         ON 部品表.材料名 = 在庫.材料名 
GROUP BY 部品表.製品名 
HAVING   MIN(在庫.数量 - 部品表.必要量) >= 0;

うん。美しい。


 

2件のコメント »

  1. ごめんさない、問題/回答のURL 変えてますー
    http://tsqlrunner.azurewebsites.net/20130521.html

    コメント by お だ — 2013年6月27日 @ 10:11 AM

  2. ありがとうございます。更新しました。

    コメント by yone64 — 2013年6月27日 @ 10:56 AM


RSS feed for comments on this post. TrackBack URI

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

WordPress.com Blog.

%d人のブロガーが「いいね」をつけました。