SQLパズル演習
SQLパズル演習

問題

  • Customers :顧客テーブル（customer_id:顧客ID, acct_balance:売掛金残高）
  • Orders :注文テーブル（customer_id:顧客ID, order_id:注文ID）
  • OrderDetails:注文明細テーブル（order_id:注文ID, item_id:製品ID, item_qty:注文数量）
  • Products :製品テーブル（item_id:製品ID, item_qty_on_hand:現存数量）
全ての製品を購入した顧客全員の買掛金残高の平均と、全てではないがいくつかの製品を購入した顧客全員の買掛金残高の平均を求めたい。 

*chiakiさんの答え
※chiakiさんはゼミ準備で大学に来れなかったので、以下のSQLは検証してません。次の見出しで検証を行います。

二つの平均を同時に出力する方法を思いつかなかったので、それぞれの場合についてSQL文を考えた。

全ての製品購入

SELECT  avg(C.acct_balance)
FROM  Customers C, Order O, OrderDetails D, Product P
WHERE  count(DISTINCT  D.item_id) = ( SELECT  count(*)
                                        FROM  Products )
       AND  D.item_id = P.item_id
       AND  O.order_id = D.order_id
       AND  C.customer_id = O.customer_id
GROUP  BY  O.customer_id

考え方

  • Customer_idでグループ化し、顧客それぞれについて製品総数と等しいかどうか調べる

いくつかの製品購入

SELECT  avg(C.acct_balance)
 FROM  Customers C, Order O, OrderDetails D, Product P
WHERE  count(DISTINCT  D.item_id) < ( SELECT  count(*)
                                        FROM  Products )
       AND  count(DISTINCT  D.item_id) > 0
       AND  D.item_id = P.item_id
       AND  O.order_id = D.order_id
       AND  C.customer_id = O.customer_id
GROUP  BY  O.customer_id

考え方

  • Customer_idでグループ化し、顧客それぞれについて製品総数より少ないかどうか調べる

kunさんの答え

2008年04月08日12時10分34秒.pdf

すべての製品購入

select avg(acct_balance)
from ( select acct_balance
       from ( select *
              from customers c , orders o , orderdetails od
             　where o.order_id = od.order_id
               and o.customer_id = c.customer_id)i1)i2
group by customer_id
having count(distinct item_id) = (select count(*)
                                 from products);

考え方

  • customers , orders , orderdetailsを自然結合する。
  • customer_idでグループ化し、それぞれのグループのitem_idの数が製品の種類の数と等しければ、すべての製品を購入した顧客が求まる。(製品のすべての種類の数は一番内側のサブクエリで求まる)
  • 以上をサブクエリとして、ずべての製品を購入した顧客のcustomer_idとacct_balanceのテーブルを返す。
  • 一番外側のselect句はそれらのacct_balanceの平均値を出力する。

結果

エラーが出ました！ 
ERROR:  column i2.item_id does not exist
行 8: having count(distinct i2.item_id) = (select count(*)
                           ^

いくつかの製品購入


select avg(acct_balance)
from ( select acct_balance
       from ( select *
              from customers c , orders o , orderdetails od
              where o.order_id = od.order_id
              and o.customer_id = c.customer_id)i1)i2
group by customer_id
having count(distinct item_id) <> (select count(*)
                                  from products);

考え方

  • すべての製品購入のクエリと同じ考え方だが、havingの条件はitem_idの数が製品のすべての種類の数と等しくないものに書き変える。

結果

エラーが出ました！ 

ERROR:  column "item_id" does not exist
行 8: having count(distinct item_id) <> (select count(*)
                           ^

☆おさらい☆

★FUMIKOさんが解説してくれた解2とYUKAさんが考えた解のコストを比較します。「explain」を使ってSQL文の処理を見ます。
postgres使用

  • 解2をEXPLAINした結果
 Aggregate  (cost=108142.67..108142.68 rows=1 width=14) (actual time=0.649..0.64
　9 rows=1 loops=1) 
  InitPlan
     ->  Aggregate  (cost=34.25..34.26 rows=1 width=4) (actual time=0.063..0.063
 rows=1 loops=1)
           ->  Seq Scan on products  (cost=0.00..29.40 rows=1940 width=4) (actua
 l time=0.004..0.006 rows=3 loops=1)
   ->  Seq Scan on customers c1  (cost=0.00..108104.50 rows=1562 width=14) (actu
 al time=0.379..0.606 rows=4 loops=1)
         Filter: ($0 <> (subplan))
         SubPlan
           ->  Aggregate  (cost=68.83..68.84 rows=1 width=4) (actual time=0.082.
 .0.082 rows=1 loops=6)
                 ->  Hash Join  (cost=34.38..68.80 rows=9 width=4) (actual time=
 0.038..0.043 rows=2 loops=6)
                       Hash Cond: (orderdetails.order_id = orders.order_id)
                       ->  Seq Scan on orderdetails  (cost=0.00..27.70 rows=1770
 width=8) (actual time=0.002..0.007 rows=11 loops=4)
                       ->  Hash  (cost=34.25..34.25 rows=10 width=4) (actual tim
 e=0.015..0.015 rows=1 loops=6)
                            ->  Seq Scan on orders  (cost=0.00..34.25 rows=10 w
 idth=4) (actual time=0.005..0.006 rows=1 loops=6)
                                  Filter: (customer_id = $1)
 Total runtime: 20.154 ms

  • YUKAさんの解をEXPLAINした結果
 fumiko=# explain
 fumiko-# select avg(C1.acct1),avg(C2.acct2)
 fumiko-# from(
 fumiko(# select C.acct_balance as acct1
 fumiko(# from Customers as C,Orders as O,OrderDetails as OD,Products as P
 fumiko(# where C.customer_id = O.customer_id
 fumiko(# and O.order_id = OD.order_id
 fumiko(# group by C.customer_id
 fumiko(# having count(DISTINCT OD.item_id) = count(DISTINCT P.item_id)) as C1,
 fumiko-# (
 fumiko(# select AVG(C.acct_balance) as acct2
 fumiko(# from Customers as C,Orders as O,OrderDetails as OD,Products as P
 fumiko(# where C.customer_id = O.customer_id
 fumiko(# and O.order_id = OD.order_id
 fumiko(# group by C.customer_id
 fumiko(# having count(DISTINCT OD.item_id) <> count(DISTINCT P.item_id)) as C2;
 ERROR:  column "c.acct_balance" must appear in the GROUP BY clause or be used in
  an aggregate function
from句内の一つ目の問い合わせでなぞのエラーが出ました。
「Puzzle31」をウィキ内検索
最終更新：2008年04月17日 14:59