SQLパズル　p131 31 すべての製品を購入した顧客　その１の学習メモ

# 概要
SQLパズル　p131 31 すべての製品を購入した顧客　その１の学習メモです。

Exists述語とNOT INの組み合わせですね。

過程を追うのに意外と時間がかかりましたね。


# 要件
いくつかの製品を買うユーザの取得の平均値を取得したい。
今回の場合は、customer_id=3,4,5,6の残高の平均を求めたい。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/62405a90-a5bd-cb70-7fd4-3cae76d85234.png)


# SQL

## DDL
```sql
DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers
(customer_id INTEGER NOT NULL PRIMARY KEY,
 acct_balance DECIMAL (12, 2) NOT NULL);

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders
(customer_id INTEGER NOT NULL,
 order_id INTEGER NOT NULL PRIMARY KEY);

DROP TABLE IF EXISTS OrderDetails;
CREATE TABLE OrderDetails
(order_id INTEGER NOT NULL,
 item_id INTEGER NOT NULL,
 item_qty INTEGER NOT NULL,
 PRIMARY KEY(order_id, item_id));

DROP TABLE IF EXISTS Products;
CREATE TABLE Products
(item_id INTEGER NOT NULL PRIMARY KEY,
 item_qty_on_hand INTEGER NOT NULL);

--顧客１と２は全部の製品を買っている。顧客ｂRと４は一部の製品だけ買っている。顧客５と６は一つも注文していない。
DELETE FROM Customers;
INSERT INTO Customers VALUES(1, 10);
INSERT INTO Customers VALUES(2, 20);
INSERT INTO Customers VALUES(3, 30);
INSERT INTO Customers VALUES(4, 40);
INSERT INTO Customers VALUES(5, 50);
INSERT INTO Customers VALUES(6, 60);

--注文テーブル
DELETE FROM Orders;
INSERT INTO Orders VALUES(1, 1);
INSERT INTO Orders VALUES(1, 2);
INSERT INTO Orders VALUES(2, 3);
INSERT INTO Orders VALUES(3, 4);
INSERT INTO Orders VALUES(3, 5);
INSERT INTO Orders VALUES(3, 6);
INSERT INTO Orders VALUES(4, 7);

--注文明細テーブル
DELETE FROM OrderDetails;
INSERT INTO OrderDetails VALUES(1, 1, 1);

INSERT INTO OrderDetails VALUES(2, 1, 1);
INSERT INTO OrderDetails VALUES(2, 2, 1);
INSERT INTO OrderDetails VALUES(2, 3, 1);

INSERT INTO OrderDetails VALUES(3, 1, 1);
INSERT INTO OrderDetails VALUES(3, 2, 1);
INSERT INTO OrderDetails VALUES(3, 3, 1);

INSERT INTO OrderDetails VALUES(4, 1, 1);
INSERT INTO OrderDetails VALUES(5, 1, 1);
INSERT INTO OrderDetails VALUES(6, 1, 1);
INSERT INTO OrderDetails VALUES(7, 1, 1);

--製品テーブル
DELETE FROM Products;
INSERT INTO Products VALUES(1, 1);
INSERT INTO Products VALUES(2, 1);
INSERT INTO Products VALUES(3, 1);


```



## 答えのSQL

```sql
-- p131 31 すべての製品を購入した顧客　その1
--  いくつかの製品を買ったユーザーの残高値の平均値を取得したい。
--  ※全ての製品を買ったユーザを含めない
SELECT AVG(acct_balance)
FROM Customers AS C1
WHERE EXISTS
    (SELECT *
        FROM Products AS P1
        WHERE P1.item_id
        NOT IN (SELECT D1.item_id
                FROM Orders AS O1, OrderDetails AS D1
                WHERE O1.customer_id = C1.customer_id
                AND O1.order_id = D1.order_id
        ));

-- NOT IN以下で取得するレコードについて確認する
-- OrderとOrderDetailを結合するのが目的
-- O1.customer_id = C1.customer_id　のC1.customer_id部分には1,2,3,4が入る
-- O1.order_id = D1.order_idでOrderDetailsとOrdersの結合条件
SELECT O1.customer_id,O1.order_id,D1.item_id
                FROM Orders AS O1, OrderDetails AS D1
                WHERE O1.customer_id = 6
                AND O1.order_id = D1.order_id
	;

-- WHERE EXISTS句以下のSQLについて確認する
-- 全ての商品を買っていないcustomerのitemの情報を取得できる
-- 全ての商品を買っているcustomerのレコードは0件となる
SELECT *
        FROM Products AS P1
        WHERE P1.item_id
        NOT IN (SELECT D1.item_id
                FROM Orders AS O1, OrderDetails AS D1
                WHERE O1.customer_id = 1
                AND O1.order_id = D1.order_id
        )
;

-- 該当するcustomer_id一覧を確認したいため、答え部分のSELECT句を*にする。
-- WHERE EXISTS句以下のレコードが１件以上有るときのcustomer_idの値を取得する
-- 全ての商品を買っていないcustomerのレコードは１件以上あるので取得する（customer_id=3,4,5,6）
-- 全ての商品を買っているcustomerのレコードは0件なので取得しない（customer_id=1,2）
SELECT *
FROM Customers AS C1
WHERE EXISTS
    (SELECT *
        FROM Products AS P1
        WHERE P1.item_id
        NOT IN (SELECT D1.item_id
                FROM Orders AS O1, OrderDetails AS D1
                WHERE O1.customer_id = C1.customer_id
                AND O1.order_id = D1.order_id
        ));
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/4818280f-21d2-5f99-bde8-5179dfd1131c.png)

# 参考

SQLパズル　p131 31 すべての製品を購入した顧客　その１

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p31

達人に学ぶSQL徹底指南書第２版 p69 NOT IN NOT EXISTSは同値ではない

達人に学ぶSQL徹底指南書第２版 p87 Exists 述語の使い方　Exists意義あの述語は一行を入力とする。Existsは行の集合を入力とする

postgres NOT IN述語
https://www.postgresql.jp/document/7.3/user/functions-subquery.html

postgres　Exists述語
https://www.postgresql.jp/document/7.3/user/functions-subquery.html

github 差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/6c1efb3308490a3035fbacf326bdd694b54281a1