[코멘토] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시보드 개발 1차 과제

w3schools.com 예시 database를 활용하여 문제를 해결한다.

사이트는 아래와 같다.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all 

SQL Tryit Editor v1.6

이 곳에서 실질적으로 MySql에서 쓰이는 문법을 통하여 데이터를 추출해보자.

1차 과제에서 문제는 총 3개였다.

 

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

[ 내 답변]

SELECT count(ContactName)
FROM customers
WHERE ContactName LIKE 'A%';

[실행 결과]

 

- 멘토님 피드백 : Country 별로 Customer의 숫자를 세기 위해 County를 기준으로 group by를 하고 count를 해주셔야 합니다. 이때 Country 와 같이 group by 기준으로 사용한 컬럼은 보통 select 절에서도 맨 앞에서 써줘서 집계한 기준을 나타내주는게 좋습니다.

 

[예시 답안]

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

[실행 결과]

 

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

[ 내 답변]

SELECT c.CustomerName, o.CustomerID, count(od.Quantity)
FROM Orders o, OrderDetails od, Customers c
WHERE od.OrderID = o.OrderID AND c.CustomerID = o.CustomerID
GROUP BY CustomerName;

[실행 결과]

 

- 멘토님 피드백 : join을 구현하는 문법에는 두 가지 방식이 있는데, 하나는 지금 하신 것 처럼 콤마를 이용해 조인을 하는 것이고 하나는 join과 on을 이용하는 것입니다. 첫 번째 방법은 join할 테이블을 from 절에 콤마를 이용해 적어주고 결합키가 되는 컬럼에 대한 조건은 where 절에 적어주시면 됩니다. 두 번째 방법은 from 절에서 테이블을 join으로 연결해 준 뒤 결합키가 되는 컬럼에 대한 조건은 on 다음에 적어주면 됩니다.

 

- 두 번째 방법이 inner join 혹은 left join을 명시적으로 구분해 적어줄 수 있고, 결합키에 대한 조건과 일반 조건이 where 절에서 섞이지 않아 개인적으로 더 선호합니다. 또 이 방법이 표준 SQL 문법이기도 합니다.

- CustomerID 별로 주문 수량을 세야 하는데 CustomerID는 Orders 테이블 에도 있으므로 굳이 Customer 테이블 까지는 조인할 필요가 없습니다만, 하신 것 처럼 CustomerName 별로 결과를 보여주려면 Customer 테이블까지 가져와서 쿼리를 작성하시면 됩니다.

- Group by의 기준으로는 name처럼 혹시 중복이 될 수 있는 것 보다는 id처럼 중복 될 수 없는 기준을 사용해주시는 것이 더 적절합니다.

- 총 Quantity를 계산하기 위해 이 경우에 count()보다는 sum()이 적절합니다.

 

[ 예시 답안 ]

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 substr(o.OrderDate, 1, 7) ym, o.EmployeeID, e.LastName, e.FirstName, sum(od.Quantity) TotalQuantity
from  Employees e, Orders o left join OrderDetails od ON o.OrderID = od.OrderID
WHERE e.EmployeeID = o.EmployeeID
group by substr(o.OrderDate, 1, 7), o.EmployeeID, e.LastName, e.FirstName;

[실행 결과]

 

- 멘토님 피드백 : 날짜를 다루는 것이 핵심인 문제였습니다. MySQL에서는 datetime 변수를 다루기 위해 date_format() 등의 함수를 많이 쓰는데 저 실습환경에서는 그런 함수들이 작동을 안하더라구요. 이럴때는 좀더 원시적(?)이지만 날짜를 문자열로 보고 substr() 함수를 사용하셔도 됩니다.

그리고 이 경우처럼 MySQL, Postgres, Oracle 등등 DBMS 마다 쿼리 문법이 약간씩 다르고 사용 가능한 함수들도 조금 차이가 있습니다. 그래서 구글링을 하실 때에도 어떤 환경에서 사용가능한 문법인지를 같이 확인해보시면 좋을 것 같습니다. 참고로 2주차부터는 MySQL 5.7 환경에서 실습을 하시게 될 예정입니다.

 

[예시 답안]

select substr(a.OrderDate,1,7) ym, a.EmployeeID, sum(b.Quantity) sumOfQuantity
from Orders a
	left join OrderDetails b on a.OrderID = b.OrderID
group by substr(a.OrderDate,1,7), a.EmployeeID;

[실행 결과]

 

정리 :  

1. ~별로 항목을 나눌 때 group by를 쓰고 count를 해준다. 이때 group by 기준으로 사용한 column은 보통 select 절에서도 맨 앞에 써줘서 집계한 기준을 나타내주는 것이 좋다.

2. join을 구현하는 문법에는 inner join을 from문에서 해주거나, from문에서 두 개의 테이블을 언급 후 where문에서 결합키가 되는 column에 대한 조건을 적어준다.

3. Group by의 기준으로는 중복이 될 수 있는 값보다는 중복될 수 없는 값을 기준으로 사용해주는 것이 더 적절하다.

4. 수량을 셀 때는 count()보다 sum()이 더 적절하다.

5. MySQL에서는 datetime 변수를 다루기 위해 date_format()등의 함수를 많이 쓴다. 여기서는 일부 날짜 문자열을 추출하기 위해 substring()을 사용하였다.

6. DBMS마다 쿼리 문법이 약간씩 다르고 사용 가능한 함수들도 차이가 있으므로 구글링을 할 때 어떤 환경에서 사용 가능한 문법인지를 같이 찾아보자.