스파르타 코딩클럽 데이터 분석 DAY 13

어제는 사전캠프 과제를 수행하느라 깜박하고 블로그를 쓰지 못했다.... 

지금 SQL을 하고 있는데 

사실 내가 배우지 않은 것들도 있고 

이해가 안되는 부분들이 너무 많아서 그부분이 좀 힘든 것 같다....

그래도 해봐야지

 

일단 오늘은 어제 풀면서 이해가 안되는 부분들 , 

힌트를 보고 참고한 부분들을 최대한 혼자 힘으로 풀어보는게 목표다! 

 

1 번 문제 

문제에서 주문을 한적 없는 고객 = orders 테이블에 없는 고객도 포함되어야 한다고 하니, 

customers를 기준으로 left join 쿼리를 사용해야 할 것 같다. 

select c.CustomerName,
       count(*) ordercount,
       coalesce(sum(TotalAmount),0) totalspnet
from customers c left join orders o on c.CustomerID = o.CustomerID
group by 1
order by 1

이 문제는 어떤 join을 사용할지, 

어느 컬럼을 기준으로 할지가 중요한 것 같다. 

결과값에는 null 값이 없지만, 

문제 자체에 null 값을 포함해야 하는걸 보니, 

coalesce 함수를 사용해 null 값을 0으로 바꿔줬다. 

 

 

문제 2

이문제는,,, 진짜 이해가 절대 안되는 문제다 ㅜㅜㅜ 

챗지피티한테도 물어보고, 

혼자서 정리한 sqld정리본도 찾아보는데 쉽게 이해가 되지 않는 문제다... 

select c.Country,
       c.CustomerName top_customer,
       sum(o.TotalAmount ) top_spent
from orders o join customers c on o.CustomerID = c.CustomerID
group by 1,2
having sum(o.TotalAmount)=
(select max(total_price)
from
(
select sum(o2.TotalAmount) total_price
from orders o2 join customers c2 on o2.CustomerID =c2.CustomerID 
where c2.Country = c.Country 
group by c2.CustomerID 
))

join 과 서브쿼리 두개가 합쳐진 문장이다 보니 사실상 많이 어려웠다....

한 3번째 풀고 있는 지금도 사실 어제 푼 것들을 거의 암기하듯이 풀었는데 

대략적으로 내가 이해한걸 풀어보자면 

 1) 첫번째 서브쿼리에서 총 주문금액을 구한다 

 다만 여기서 나라별로 그룹을 나눠야 하기 때문에 전체 구문에서의 나라 ex  usa 라면 서브쿼리 내에서 나온 결과도 usa 여야만 한다는 의미인것 같다. 

 그리고 첫번째 서브쿼리의 나라와 전체 쿼리의 나라가 같아야 하기 때문에 

 customers 에 잇는 모든 나라의 총금액 중 가장 높은 금액을 구하는 것이 아니라 , 

 각각의 나라를 지정하고, 그 나라에서 총 금액 중 가장 높은 금액을 각각 구해야 하기 때문에 

 c2.country = c.country 라는 구문이 나오는 것 같다. 

 2) 그다음에 그 나라에서 가장 높은 주문금액을 구하고, 

 3) 전체 쿼리에서 나라, 이름, 그리고 총 주문금액을 구한 다음에 

 having 절을 사용해 max 즉 가장 높은 주문금액에 대한  조건을 제시해주는 것이다. 

라고 이해를 했는데...

다시 풀어봐야징

- 다시 푸니까 조금씩 이해가 되는 것 같다......

적는 순서를 

1) select c.country~~ 이구문을 먼저 적어서 쿼리에 어떤 내용이 들어올지를 먼저 설정하고, 

첫번재 select 문에서 having 절을 사용해 sum(totalamount) 에 대한 조건을 설정하는데, 

sum에 대한 max를 구해야 하니까 서브쿼리를 두개 넣고, 

2) 마지막 서브쿼리에서 sum(totalamount)를 구한 뒤, 나라는 각 나라별로 구해야 하니 셀프조인으로 생각하고 

만약 where 절에 단순히 country 만 넣는다면 뭐 맞지도 않겠지만 

전세계에서 가장 높은 총 주문금액을 구하는 것이 된다. 

그래서 country = country 쿼리를 넣어서 각각의 나라별로 구하는 구문을 적는다

3) 두번째 서브쿼리에서 마지막 서브쿼리 sumd의 최대값 max를 구한다. 

 

이렇게 하면 위의 서브쿼리를 완성할 수 있다...

어렵당

 

문제 3

하나의 테이블을 사용해서 이렇게 길게 내용이 나오게 하는거 보니까 대략적으로 self join 이 아닐까...?

생각하게 된다. 

select e.name,
       e.department, 
       e.Salary ,
       e2.Name top_earner,
       e2.Salary top_salary
from employees e join employees e2 on e.Department = e2.Department 
where e2.Salary = (select max(salary)
                   from employees e3
                   where e3.Department = e.Department )

1) 첫번째 셀프 조인을 이용해 같은 테이블에서 새로운 컬럼들을 꺼낼 수 있게 준비한다. 

2) select 문에서 사용할 컬럼들을 꺼내주는데 

  top_earner, top_salary 같은 경우 셀프 조인한 테이블 안에서 따로 함수를 사용해서 결과값을 내야 하는 것이기 때문에 

  e2 테이블에서 꺼내오고, 

3) where 절에서 salary 에 대한 조건을 부여한다.

  부서별 가장 많은 월급을 받는 직원의 이름과 월급을 조회해야 하기 때문에 

  max 함수를 사용하고,

  where 절에서 부서별. 이라는 조건을 주기 위해  

  문제 2번의 country 와 같이 하나의 부서를 넣으면, 그 부서에 맞는 가장 높은 월급을 추출할 수 있는 조건을 넣는다. 

 

그래도 where 절이 가장 이해하기 힘든 부분이었는데 

문제 2번에서 여러번 쿼리를 풀어내면서 조금은 이해가 되서 이문제는 비교적으로 이해가 쉬웠다. 

 

문제 4 

select department,
       avg(salary) avg_salary
from employees
group by Department 
having avg(salary) = (select max(avg_salary)
                      from (select avg(salary) avg_salary
                            from employees
                            group by department))

이 쿼리로 했을 때 문제에 나와있는 기대 결과값과는 다르게 나오지만, 

답으로 나온 쿼리를 적용핻도 내가 한 쿼리결과와 동일하게 나오니 되었다...

 이 쿼리는 

1) 첫번째 select 문에서 부서와, 평균 급여 - (가장 높은 월급은 서브쿼리에서 설정할 예정) 를 먼저 선택한다. 

'부서별' 이라고 했으니 group by 를 부서로 묶어 주고

2) 서브쿼리문에서 일단 평균 급여를 먼저 구하고, 그 다음 서브쿼리문에서 이 평균 월급 중 가장 높은 월급을 선택한다. 

 

문제 5

이 문제의 경우 테이블이 3개나 있다보니 조인에 조인을 해야 하는 경우이다. 

select c.CustomerName,
       sum(p.Price*o.Quantity) total_amount,
       count(o.OrderID) order_count
from customers c join orders o on c.CustomerID = o.CustomerID join products p on o.ProductID = p.ProductID
group by 1

각각의 테이블에서 서로 조인할 수 있는 키를 찾는다.

customers 와 orders 에서는 customerid로 서로 조인을 할 수 있고, 

orders 와 products 는 productid 로 서로 조인할 수 있다. 

이렇게 조인만 하는 방법을 알면 쉽게 풀 수 있는 문제였다.

 

문제 6

이 문제는 ... 각 제품 카테고리별로 max와 price * quantity를 구해야 하는거라 서브쿼리문을 사용할 것 같다... 

select p.Category,
       p.ProductName top_product,
       sum(o.Quantity) top_sold
from products p join orders o on p.ProductID = o.ProductID
group by 1,2
having sum(o.quantity)= (select max(total_count)
                         from (select p2.Category,
                                      sum(o2.Quantity) total_count
                               from products p2 join orders o2 on p2.ProductID = o2.ProductID
                               group by p2.Category, p2.ProductID ) a
                         where a.Category = p.category)

서브쿼리 안에 서브쿼리가 있는 구조이지만, 

위에 문제들과 비슷하다.

 1) 추출해야 하는 컬럼들을 먼저 나열한다. 

 2) having 절을 사용해서 top_sold 컬럼에 들어갈 조건을 명시한다. 

 3) 가장 많이 팔린 제품의 총 판매량이니 max, sum 함수를 사용해야 하고,

 4) 첫번째 서브쿼리에서 기본적으로 카테고리와, 총 판매량을 구한 후,

 5) 두번째 서브쿼리에서 max함수로 총 판매량 = 가장 많이 팔린 제품의 총 판매량 으로 설정한 뒤,

    where 절을 사용해 각각의 카테고리 별로 결과가 나올 수 있도록 설정한다. 

 

문제 7

select name,
       Department ,
       salary
from employees e
where salary = (select max(salary) max_salary
                      from employees e2
                      where e.Department = e2.Department )

셀프 조인같은 느낌의 문제였다. 

위에 문제들보다는 비교적으로 쉽다 보니까 수월하게 풀 수 있었다. 

 

문제 8

select e.Name,
       p.ProjectName,
       p.Budget
from employees e join employeeprojects ep on e.EmployeeID = ep.EmployeeID join projects p on ep.ProjectID = p.ProjectID
where p.Budget >=10000

..? 이 문제는 내가 뭘 빠트렸나 싶을 정도 쉬운 문제였다.

아마 조인 키를 설정하는 부분이 중요해서 그 부분을 강조한 것 같은데, 

employees 테이블과 projects 테이블을 조인하고 싶어도 

둘이 동시에 갖고 있는 키가 없으니, 

employeeprojects 를 사용해서 조인을 한 것 같은데 

cross join (곱집합) 또는 full outer join 을 활용해보려고 했는데 그렇게 하니 

조건에서 budget >=10000 조건이 일치하지가 않는다 

아마 조인을 여러개 해야만 가능한 것 같다 

그래서 테이블에도 employeeprojects 가 따로 나와있는 것 같고... 

 

그래도 앞에서 한 쿼리문들이 꽤나 난이도가 높았기 때문에 

마지막 7,8 번 문제들은 훠어어얼씬 수월하게 풀수 있었다. 

 

오늘부로 사전캠프가 끝이 났는데 

2-6시까지 하는 사전 캠프도 가끔 허덕일 때가 있어서 

본캠프 들어가는게 살짝 무섭기도 하다. 

하지만, 더 많은 데이터들을 사용해서 실질적인 데이터 분석을 할 생각을 하면 설레기도 하다 ><

헿 이번주도 끝!!!