지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서
w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다.
주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블, 결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다.
ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다.
[ 유통업체 ERD (Entity Relationship Diagram) ]
* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임. SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨.
테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다.
"제품 카테고리 중 'Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments' 카테고리에 대해서 카테고리별로 판매가 일어난(주문이 발생한) 제품들의 가격의 합계, 총 주문 발생 회수, 제품들의 가격의 평균을 구하시오. 단, 카테고리별 제품 가격의 합계가 1,100 이상인 경우만 집계 결과를 제시하되, 가격의 합계를 기준으로 내림차순으로 정렬하여 제시하시오." |
SELECT e.CategoryName AS CategoryName, SUM(e.Price) AS Price_sum, COUNT(*) AS Order_cnt, AVG(e.Price) AS Price_avg FROM (SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName -- sub query 2 FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID -- sub query 1 FROM OrderDetails a INNER JOIN Products b ON a.ProductID = b.ProductID) c LEFT JOIN Categories d ON c.CategoryID = d.CategoryID) e WHERE e.CategoryName IN ('Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments') GROUP BY e.CategoryName HAVING Price_sum > 1100 ORDER BY Price_sum DESC;
|
Number of Records: 3 ==================================================== CategoryName Price_sum Order_cnt Price_avg ==================================================== Dairy Products 2863.2 100 28.63 Seafood 1345.17 67 20.07 Condiments 1121.5 49 22.88 |
위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다.
Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다.
예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다.
=========================================================================
[ sub query 1]
OrderDetails 테이블(a)과 Products 테이블(b)을 ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다) 상위 5개만 예시로 가져오겠습니다.
SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID FROM OrderDetails a INNER JOIN Products b ON a.ProductID = b.ProductID LIMIT 5;
|
OrderID ProductID Price CategoryID 10248 11 21 4 10248 42 14 5 10248 72 34.8 4 10249 14 23.25 7 10249 51 53 7 |
=========================================================================
[sub query 2]
위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다. CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?
SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID FROM OrderDetails a INNER JOIN Products b ON a.ProductID = b.ProductID) c LEFT JOIN Categories d ON c.CategoryID = d.CategoryID LIMIT 5;
|
c.OrderID c.ProductID c.Price c.CategoryID d.CategoryName 10248 11 21 4 Dairy Products 10248 42 14 5 Grains/Cereals 10248 72 34.8 4 Dairy Products 10249 14 23.25 7 Produce 10249 51 53 7 Produce
|
========================================================================
아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다. 아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다.
SELECT a.OrderID, a.ProductID, b.Price, CASE WHEN b.Price >= 40 THEN '1_over_40' WHEN b.Price < 40 AND b.Price >= 20 THEN '2_20_40' ELSE '3_under_20' END Price_grp FROM OrderDetails a INNER JOIN Products b ON a.ProductID = b.ProductID LIMIT 10;
|
OrderID ProductID Price Price_grp 10248 11 21 2_20_40 10248 42 14 3_under_20 10248 72 34.8 2_20_40 10249 14 23.25 2_20_40 10249 51 53 1_over_40 10250 41 9.65 3_under_20 10250 51 53 1_over_40 10250 65 21.05 2_20_40 10251 22 21 2_20_40 10251 57 19.5 3_under_20 |
많은 도움이 되었기를 바랍니다.
이번 포스팅이 도움이 되었다면 아래의 '공감~
'를 꾸욱 눌러주세요. ^^
