問題
- 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使用
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
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句内の一つ目の問い合わせでなぞのエラーが出ました。
最終更新:2008年04月17日 14:59