코멘토_SQL과 Redash를 이용한 데이터분석보고서 및 대시보드 만들기
수업 1주차 학습 내용 및 과제 정리

w3school을 통해 SQL 데이터 추출 문제 풀어보기

문제

1. Country 별로 ContactName이 ‘A’로 시작하는 Customer의 숫자를 세는 쿼리를 작성하세요.
2. Customer 별로 Order한 Product의 총 Quantity를 세는 쿼리를 작성하세요.
3. 년월별, Employee별로 Product를 몇 개씩 판매했는지를 표시하는 쿼리를 작성하세요.
   참고로 JOIN을 하기위해 WITH 구문 필요 없습니다.

• 필요 테이블: Customers, Orders, OrderDetails
  (웹페이지 내의 오른쪽 Your Database 탭에서 테이블 이름을 클릭해 각 테이블 내용을 확인하세요)

풀이

1.

Country 별로 ContactName이 ‘A’로 시작하는 Customer의 숫자를 세는 쿼리를 작성하세요.

SELECT Country, COUNT(*)                -- 갯수를 세라     
FROM Customers                          -- Customer 테이블의 
WHERE ContactName LIKE 'A%'             -- ContactsName이 'A'로 시작하는
GROUP BY Country                        -- Country 별로 

2

Customer 별로 Order한 Product의 총 Quantity를 세는 쿼리를 작성하세요.

quanity는 수량 + 갯수세는 쿼리는 위에 처럼 COUNT(*) + 테이블은 Products

Customers 테이블과 Products 테이블을 한번 봐보자.


연관성은 없는것 같다.
그렇다면 Customer별로 Order한 Product가 의미하는건 뭘까
Products테이블에는 customer컬럼은 없는데... ProductName을 의미하는걸까
ProductName의 중복값이 있는지 확인해봤지만 없는걸로 확인되었다.

그렇다면 다른 테이블을 하나하나 봐야겠다.


OrderDetails테이블과 Orders 테이블을 Join해서
CustomerID별로 OrderID한 Quantity의 총합을 각각 구하면 되지 않을까?

일단 합침

Customer 별로 Order한 Product의 총 Quantity를 세는 쿼리 진행

SELECT CustomerID, Count(Quantity)
FROM Orders JOIN OrderDetails 
ON Orders.OrderID = OrderDetails.OrderID
GROUP BY CustomerID

3

년월별, Employee별로 Product를 몇 개씩 판매했는지를 표시하는 쿼리를 작성하세요.

필요한정보

• 년원별 판매날짜 Orders테이블
• Employee직원별 판매정보 Orders 테이블에는 EmployeeID, OrderID, OrderDate
• 판매 갯수 Quanity OrderDetails 테이블에는 OrderId, ProductID,OrderDetailsID

Orders테이블과 OrderDetails 테이블을 조인

여기서 년월별이니까 년-월-일 ---> 년월 로 단순화 ---> CASE 구문으로 재정렬

날짜포맷

MySQL에서는 DATE_FORMAT() 함수를 사용하여 날짜를 원하는 형식으로 변환할 수 있습니다.

2024-01-27 형태로 값들이 있는 상태에서
2024-01 처럼 년월별로 그룹화하고싶습니다.

DATE_FORMAT(테이블명.컬럼명, '%Y-%m') 으로 날짜데이터를 그룹화할 수 있습니다.

SELECT DATE_FORMAT(Orders.OrderDate, '%Y-%m'), -- 년월별로  
       Orders.EmployeeID,					   -- Employee별로 
       COUNT(*) 							   -- 몇개씩 판매했는지 
FROM Orders JOIN OrderDetails 				   -- 두 테이블을 JOIN 
ON Orders.OrderID = OrderDetails.OrderID	   
GROUP BY 1 , 2								   -- 년월별, Employee별 

count(*),count(컬럼),sum()

sum은 수치의 합
count는 데이터 갯수

COUNT(*), COUNT(1) 은 NULL값도 모두 셈 = 걍 rows갯수 확인
COUNT(컬럼명) 은 NULL값 제외 = 유의미한 수치만 더함

판매량을 계산하기 위해서 이 경우에는 count() 보다는 sum()이 적절합니다.

쿼리정리

1. Country 별로 ContactName이 ‘A’로 시작하는 Customer의 숫자를 세는 쿼리를 작성하세요.

select Country, count(1) cnt
from Customers
where ContactName like 'A%'
group by Country;

2. Customer 별로 Order한 Product의 총 Quantity를 세는 쿼리를 작성하세요.

select a.CustomerID, sum(b.Quantity)
from Orders a 
left join OrderDetails b on a.OrderId = b.OrderId
group by a.CustomerID;

3. 년월별, Employee별로 Product를 몇 개씩 판매했는지를 표시하는 쿼리를 작성하세요.

select year(a.OrderDate) y, month(a.OrderDate) m, a.EmployeeID, sum(b.Quantity) sumOfQuantity
from Orders a
	left join OrderDetails b on a.OrderID = b.OrderID
group by y, m, a.EmployeeID;