JOIN*(3개 테이블) and GROUP BY*(보이지않더라도 key칼럼으로) and ORDER BY
정리해보기
- JOIN한 상태에서 ~별 ~집계를 할때, 기준테이블의 id로 GROUP BY 하되, 기준테이블의 칼럼들은 SELECT할 수 있으나, 집계시에도 COUNT()는 집계테이블의 id로 COUNT()하되, 집계되는 테이블은 1 row로 접혀서 숫자계산되기 때문에 SELECT문에 칼럼명을 못넣는다.
- 만약, 집계테이블의 칼럼을 groupby의 기준에 넣는다면, 집계테이블 칼럼을 SELECT할 수 있다. 그러나 기준테이블은 안해두 가능!!
JOIN 3개 테이블 해보기
SELECT * FROM purchases;
SELECT * FROM items;
SELECT * FROM customers;
-- 1) item들 중에 구매한 items 목록을 원하는 경우
SELECT * FROM items
JOIN purchases ON items.id = purchases.id;
-- left조인을 쓴다면, 구매하지 않은 아이템도 블랭크로 붙는다.
SELECT * FROM items
LEFT JOIN purchases ON items.id = purchases.id;
-- 2) 이제 어느 고객이 어느 아이템을 샀는지 join해보자.
SELECT * FROM customers
JOIN purchases ON customers.id = purchases.customer_id;
--left join
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;
-- 고객 중에 구매를 한 고객 join데이터 중 이름만 select하기
SELECT customers.first_name, customers.last_name FROM customers
JOIN purchases ON customers.id = purchases.customer_id;
-- 3) ***3개 테이블 한꺼번에 join해보기***
---- (1) items중에 구매한 것들만 item만 나오도록 inner join한다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
---- (2) 여기서 보이는 customer_id로 고객들을 연결할 수 있을 것이다. *** JOIN한것에 바로 JOIN을 해버린다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
---- (3) 필요한 칼럼만 챙겨보자.
SELECT customers.first_name, customers.last_name, items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY
-- GROUP BY --
-- JOIN의 결과는 duplicated된 row들로 나열되서 나타나니 GROUP BY <사람테이블의 id>를 활용해야한다.
SELECT * FROM customers;
-- ***left join***의 결과 ***모든*** 고객들 중에, 구매한 고객들 + 구매안한 고객들의 정보들이 다 나온다
-- left join을 하는 이유는 group by 로 count할 때, *** 구매안한사람도 0으로 count하기위해 ***
-- 하지만, 나는 구매info(item_id) + 연결키(customer_id)가 중요한게 아니라, 얼마나 많이 구매했는지 알고 싶은 상황이다.
-- 각 구매info가 따로따로 row로 나뉘어서 나오므로 이 때 필요한 것이 groupby이다.
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;
-- 1) 이름정보와, purchases테이블에서는 ***primary key인 id만 가져온다. for COUNT ***
-- 하지만, 여기까지도 row가 나뉘어져있다.
SELECT customers.first_name, customers.last_name, purchases.id FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;
-- 2) 이제 count하고 싶은 칼럼인 puchases의 primary key id칼럼에 count함수를 넣어준다.
-- 이대로는 에러가 난다. 그래서 필요한 것이 GROUP BY 이다.
-- *** 이때, 보이지 않더라도 LEFT테이블인 customers의 identity & primary key인 id칼럼으로 groupby 한다***
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id;
-- 3) ***SUM() - GROUP BY를 이용하여,
-- 아이템들 중 구매된 아이템만* 골라내는 INNER JOIN을 한 뒤,
-- 구매된 아이템 중 고객들customers들을 다시 INNER JOIN시켜붙혀보자.
SELECT * FROM items;
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id;
-- 4) 여기에 *** 고객별 의 id로 groupby -> 집계함수 SUM()*** 시켜보자.
-- 만약 GROUP BY 만 쓸때, *로 모든 칼럼들에 접근한 상태라면, 데이터 접근 중이므로 GROUP BY로 합칠 수가 없다.
-- 필요칼럼선택(GROUP BY 기준 테이블안의) + (GROUP BY기준아닌 테이블의 칼럼을 합칠)집계함수를 사용해야한다.
-- my) customers의 id로 GROUP BY를 한다면, customers테이블은 칼럼 정보 살아 있어서 가져올 수 있다.
-- *********GROUP BY기준이 아닌 테이블의 칼럼들은 개별접근x COUNT(), SUM() 등으로 접혀들어가야한다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;
-- 5) 숙제 = 모든 고객이 구매한 가격의 합 ( customers 테이블 연결없이 해봤다 )
-- GROUP BY 의 기준을 items.id로 잡았다면 items테이블 칼럼은 접근가능 -> purchases테이블은 접히니까, 집계함수
SELECT items.name, SUM(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id
GROUP BY items.id;
SELECT SUM(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id
-- 5-정답)
-- 1) 전체 고객들이 얼마나 많은 돈을 소비했는지 알아보기
-- (1) 구매 정보 테이블 가져오기
SELECT * FROM purchases;
-- (2) 구매정보에 items정보 붙히기 innerjoin
SELECT * FROM purchases
INNER JOIN items ON purchases.item_id = items.id;
-- (3) SUM()만 해주기
SELECT SUM(items.price) FROM purchases
INNER JOIN items ON purchases.item_id = items.id;
ORDER BY
-- Order by --
-- 이전에 했던대로, *각각의 고객들이 얼마나 많은 *돈을 소비했는지 다시 찾아보자
-- 1) 3개 테이블 다 조인하기
SELECT * FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;
-- 2) 필요칼럼 셀렉트하기
SELECT customers.first_name, customers.last_name, items.price FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;
-- 3) 보이진 않지만, 고객의 primary key인 id칼럼으로 groupby -> SUM()하기
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;
-- 4) price순으로 정렬하기 ( alis " 띄워쓰기alias시 따옴표 필수 " -> ORDER BY에 alias적어주기)
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent";
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC;
-- 5) TOP 1만 표시해주기
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC
LIMIT 1;
[출처] https://nittaku.tistory.com/431
