P131 SQLパズルすべての製品を購入した人


クエリの番号が画像のSELECT出力と同じはず。

/*
P131 SQLパズル

すべての製品を購入した人
いくつかの製品を購入した人を探す
*/

CREATE TABLE Customers(
	customer_id int NOT NULL,
	acct_balance DECIMAL(12,2) NOT NULL

)

CREATE TABLE Orders(
	customer_id int NOT NULL,
	order_id int NOT NULL
)

CREATE TABLE OrderDetails(
	order_id int NOT NULL,
	item_id int NOT NULL,
	item_qty int NOT NULL,--注文数量
	primary KEY (order_id,item_id)
)

CREATE TABLE Products(
	item_id int NOT NULL Primary key,
	item_qty_on_hand int NOT NULL--在庫数量
)


INSERT INTO Customers VALUES(1,100),
(2,200)

INSERT INTO Orders VALUES(1,1),
(2,2)

INSERT INTO OrderDetails VALUES(1,1,10),
(2,1,5)

INSERT INTO Products VALUES(1,100),
(2,200)


INSERT INTO Orders(customer_id,order_id) VALUES(1,3)
INSERT INTO OrderDetails(order_id,item_id,item_qty) VALUES(3,2,20)

INSERT INTO Orders(customer_id,order_id) VALUES(1,4)
INSERT INTO OrderDetails(order_id,item_id,item_qty) VALUES(4,1,12)


SELECT 'Customers' as tableName,* FROM Customers
SELECT 'Orders' as tableName, * FROM Orders
SELECT 'OrderDetails' as tableName,* FROM OrderDetails
SELECT 'Products' as tableName,* FROM Products


--
--すべての製品を購入したユーザは?
--製品の総数は 2つ
--ユーザが購入した製品の種類は2種類
--あるユーザが何種類の製品を購入したかを調べる
--OrderDetails.order_idで Orders.order_id,customer_idがわかる
--製品数が2種類というのはわかる。
--では全種類買ったというのはどこでわかる
--(A)結合 (1)
SELECT 
*
FROM Orders LEFT OUTER JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id 

(2)
--item_idでグループにして製品をそれぞれいくつ買ったかを調べる。製品数が行数になる
SELECT OrderDetails.item_id,COUNT(OrderDetails.item_id) as buy_count_item
 FROM Orders LEFT OUTER JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id 
GROUP BY OrderDetails.item_id
--この行数とProductsの数が同じであれば全製品が購入されたことがわかる
--一人で全製品が購入されたかを調べる

(3)
--(B)ユーザ単位で、製品をまとめる
SELECT 
	Orders.customer_id,
	OrderDetails.item_id,
	COUNT(OrderDetails.item_id) as count_item_id
FROM Orders LEFT OUTER JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id 
GROUP BY OrderDetails.item_id,Orders.customer_id

(4)
--BをもとにCustomer_idをGROUPにしてカウントして製品数が同じであれば全製品を購入した
SELECT customer_id,
COUNT(Customer_id) as count_item_Num
FROM (
	SELECT --(B)
		Orders.customer_id,
		OrderDetails.item_id,
		COUNT(OrderDetails.item_id) as count_item_id
	FROM Orders LEFT OUTER JOIN OrderDetails
	ON Orders.order_id = OrderDetails.order_id 
	GROUP BY OrderDetails.item_id,Orders.customer_id
) as ALLTable
GROUP BY customer_id

--これでcustomer_id:1の人は2製品を購入したことがわかる

/*
正解
*/
--(これから)
SELECT AVG(acct_balance) FROM Customers as c1
Where
	(
	SELECT COUNT(DISTINCT item_id)
	FROM Products
	)<>
	(
	SELECT COUNT(DISTINCT item_id)
	FROM ORders,OrderDetails
	WHERE
		orders.customer_id = c1.customer_id
		AND Orders.order_id = OrderDetails.order_id
	)

--(検討1)
	SELECT * FROM Customers as c1
Where
	(
	SELECT COUNT(DISTINCT item_id)
	FROM Products
	)<>
	(
	SELECT COUNT(DISTINCT item_id)
	FROM ORders,OrderDetails
	WHERE
		orders.customer_id = c1.customer_id
		AND Orders.order_id = OrderDetails.order_id
	)

--=
	SELECT * FROM Customers as c1
	Where
		(
		SELECT COUNT(DISTINCT item_id)
		FROM Products
		)=
		(
		SELECT COUNT(DISTINCT item_id)
		FROM ORders,OrderDetails
		WHERE
			orders.customer_id = c1.customer_id
			AND Orders.order_id = OrderDetails.order_id
		)

SELECT * FROM Orders LEFT OUTER JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id LEFT OUTER JOIN Customers
ON Customers.customer_id = Orders.customer_id

--購入した製品数
SELECT COUNT(DISTINCT item_id) FROM Orders LEFT OUTER JOIN OrderDetails
ON Orders.order_id = OrderDetails.order_id LEFT OUTER JOIN Customers
ON Customers.customer_id = Orders.customer_id





–(検討1)
–(KB1)のところが理解できない。なぜ、COUNTした数が一致するとCustomerがわかるのか?
–数値でCustomerが取得できる理屈がわからない。

/*以下は検討*/
--(検討1)
--(KB1)のところが理解できない。なぜ、COUNTした数が一致するとCustomerがわかるのか?
--数値でCustomerが取得できる理屈がわからない。
	SELECT * FROM Customers as c1
Where
	(
	SELECT COUNT(DISTINCT item_id)
	FROM Products
	)<>
	(--(KB1)
	SELECT COUNT(DISTINCT item_id)
	FROM ORders,OrderDetails
	WHERE
		orders.customer_id = c1.customer_id
		AND Orders.order_id = OrderDetails.order_id
	)

等号の場合は全製品2つを購入したcustomer_id1が出力される。以下は不等号なので全製品を購入していない2が出力された

(KB1)のところを調べる。そもそも等号不等号で行のレコードが表示される仕組みが理解できていない。

--(KB1)のところを調べる。そもそも等号不等号で行のレコードが表示される仕組みが理解できていない。

	--SELECT * FROM Customers as c1
--Where
	--(--(KB1)WHERE以下を組み直すと以下のようになる。結果は同じ
	SELECT *--COUNT(DISTINCT item_id)
	FROM ORders INNER JOIN OrderDetails
	ON
		--orders.customer_id = c1.customer_id
		--AND 
		Orders.order_id = OrderDetails.order_id INNER JOIN Customers C1
		ON orders.customer_id = c1.customer_id
	--)

INNER JOINの結合表

次にitem_idでCOUNT(DISTINCT item_id)を求める

	--次にitem_idでCOUNT(DISTINCT item_id)を求める
	--そうすると重複を除く購入した製品数がユーザごとにでてくる。
	--全製品は2つでこのときの購入者のデータも存在する。
/* customer_id1が製品数2で全部買った
customer_id	  COUNT_item_id(製品数)
1           	2
2           	1
だから、製品数が同じときとそうでないときで誰が購入したかがわかる。
つまり、正解は数で調べているが、実際は見えないデータ(と自分が思い込んでる)が存在している。
	*/

	SELECT orders.customer_id,COUNT(DISTINCT item_id) as COUNT_item_id
	FROM ORders INNER JOIN OrderDetails
	ON
		--orders.customer_id = c1.customer_id
		--AND 
		Orders.order_id = OrderDetails.order_id INNER JOIN Customers C1
		ON orders.customer_id = c1.customer_id
	GROUP BY orders.customer_id--COUNT(DISTINCT item_id)だけをだすなら不要

--KB1の検討ここまで

結論
COUNTという数字だけで見ていると製品数しか情報を持っていないと思っていたようだ。
実際は、製品数に付随するCustomer_id情報もあるので、出力されたと思う
あくまでもWhereでの<>条件を求めているに過ぎず、SELECTでは必要な情報が出力されている。
このような考え方に慣れるしかない。

こちらの記事もどうぞ