JOIN*(3개 테이블) and GROUP BY*(보이지않더라도 key칼럼으로) and ORDER BY
2019. 1. 29. 13:46
* 정리해보기
- 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;
'한의대 생활 > └ PostgreSQL' 카테고리의 다른 글
SEQUENCE* ( autoincrement대체 ) and INDEX* ( WHERE서치를 문자열에서 빠르게 내부binary파일생성) (0) | 2019.01.30 |
---|---|
CREAT TABLE*(primary key, references) and INSERT INTO (0) | 2019.01.29 |
4. DB 조작해보기(sp_help과 같은코드, JOIN, *JOIN에 JOIN example) (0) | 2019.01.24 |
3. DB 조작해보기(UPDATE, DELETE, LIKE %_FILTERING*) (0) | 2019.01.23 |
2. 쿼리창 및 간단한 DB조작(SELECT, WHERE, LIMIT*) (1) | 2019.01.23 |