달리기반 SQL Lv4 (1)

Lv4 단골 고객님 찾기

 

두 테이블이 주어졌으니 join함수를 이용하는 문제일 것이다.

 

일단 조건에 서브쿼리문과 having 함수를 활용하라고 했으니 이들에 대해 알아보자.

 

💡subquery문이란 쿼리문 안에 서브로 들어간 구문으로, 서브쿼리 결과를 메인쿼리에 다시 활용하는 것이다.

여러 번의 연산을 수행해야 할 때, 조건문에 연산결과를 사용해야 할 때, 조건에 쿼리 결과를 사용하고 싶을 때 사용한다.

 

예시를 보자.

select order_id, restaurant_name, 
       if(over_time >= 0, over_time, 0) as 'overtime'
from (select order_id,
             restaurant_name,
             food_preperation_time -25 as 'over_time'
      from food_orders) a

 

서브쿼리문에는 이름을 붙여줘야 하기 때문에 a라는 이름을 서브쿼리를 감싸는 괄호 뒤에 붙였다.

 

서브쿼리에서 food_orders 테이블에서 order_id 컬럼, restaurant_name 컬럼,

그리고 food_preperation_time 컬럼에서 25를 빼준 값을 over_time이라는 별칭을 붙여 가져온다.

그리고 메인쿼리에서 서브쿼리에서 가져온 컬럼들을 조회하고

그 중 over_time을 활용해 조건을 붙이고 그것을 overtime이라는 별칭을 붙여 조회한다.

 

💡having 함수는 group by 절로 그룹화된 데이터에서 조건을 설정하는 데 사용된다.

 

✔️having 절은 그룹화가 된 후에 그룹 별로 조건을 설정한다. 그룹화된 결과에 조건을 걸 때 사용한다.

 

예를 들어, 각 카테고리 별로 매출이 1000 이상인 제품을 찾으려면

SELECT category, SUM(sales) AS total_sales
FROM products
GROUP BY category
HAVING total_sales >= 1000

 

카테고리 별로 그룹화하여 sales의 합을 구해 total_sales 라고 이름 붙인 뒤

그룹화의 결과인 total_sales가 1000이상인 값만 반환한다.

 

다음과 같이 사용할 수도 있다.

SELECT month, SUM(revenue) AS total_revenue
FROM monthly_sales
GROUP BY month
HAVING SUM(revenue) = (
    SELECT MAX(SUM(revenue))
    FROM monthly_sales
    GROUP BY month
);

 

having 절에서 월별로 그룹화해서 총매출을 가져온 다음에 그 결과에 조건을 건다. 해당 총매출은 해당 그룹월에서 가장 높은 값이어야 한다고.

 

 

✔️where 절은 그룹화가 되기 전에 각 행에 대한 조건을 설정한다. 그래서 주로 행 별 조건을 걸 때 사용한다.

SELECT *
FROM products
WHERE price > 100

 

price 컬럼의 데이터가 100보다 큰 행의 개수를 반환한다. 

 

 

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

    a. 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함          되어야 합니다.

조회해야 하는 컬럼명 : CustomerName / OrderCount(new!) / TotalSpent(new!)

 

조건 후반부를 보니 left join을 사용해야 할 것 같은 느낌이 온다. 공통컬럼인 CustomerID로 묶자.

주문자명이 있는 행을 세주고(해당 주문자의 주문건수) 주문자명 별로 총주문량을 구하기 위해 주문량을 sum 함수로 집계해준다. 

만약 총주문량이 null이거나 0이라면 주문을 한 적이 없다는 뜻이므로 총주문량을 0이라고 지정해준다. 

select c.CustomerName, count(c.CustomerName) as 'OrderCount', 
       case when sum(o.TotalAmount) = 0 or sum(o.TotalAmount) is null then 0
       else sum(o.TotalAmount) 
       end 'TotalSpent'
from orders o left join customers c on o.CustomerID = c.CustomerID
group by 1

 

여기서는 주문을 한 적 없는 고객이 없어서 0으로 처리되는 값은 없다. 

 

그리고 위에서는 case 문을 통해서 0이나 null 값을 처리했는데,

앞에서 사용한 coalesce 문을 통해서 컬럼의 데이터가 null 값인 경우 다른 원하는 값으로 대체 해줄 수 있다.

select c.CustomerName,count(o.OrderID) as OrderCount,
       coalesce(sum(o.TotalAmount), 0) as TotalSpent
from orders o left join customers c on o.CustomerID = c.CustomerID
group by 1

 

해당 고객이 주문한 게 아예 없어서 sum(TotalAmount)의 값이 null이라면 0으로 대체해주고 조회하라는 것이다.

 

2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

 

일단 고객별로 나라와 총 주문금액까지 조회하는 쿼리는 다음과 같다.

select c.Country, c.CustomerName as 'Top_Customer', sum(o.TotalAmount) as 'Top_Spent'
from orders o left join customers c on o.CustomerID = c.CustomerID
group by 1,2

 

 

이제 서브쿼리와 having 절을 사용해 각 나라에서 금액이 가장 높은 고객만 뽑아보자.

select c.Country, c.CustomerName as Top_Customer, sum(o.TotalAmount) as Top_Spent
from orders o left join customers c on o.CustomerID = c.CustomerID
group by 1,2
having sum(o.TotalAmount) = 
       (select max(SumSpent)
        from 
            (select sum(o2.TotalAmount) as SumSpent
             from customers c2 join orders o2 on c2.CustomerID = o2.CustomerID
             where c2.Country = c.Country
             group by c2.CustomerID) as Subquery
        )

 

처음에 작성한 코드로 모든 고객과 그 고객의 나라, 총 주문금액을 구했다.

그렇게 그룹화가 된 후에 having 절로 그룹별로 조건을 걸어준다.

 

각 그룹의 총 지출액 sum(o.TotalAmount) 이 서브쿼리의 최대 지출액 max(SumSpent)과 같아야 한다고 having 절을 설정한다.

 

최소(제일 안쪽) 서브쿼리는 고객별로 총 지출액을 계산하고, where 절로 각 국가의 최대 지출액을 찾는다.

• where c2.Country = c.Country 조건을 통해, 현재 국가(c.Country)에 속한 고객만 선택한다.
• 즉, 각 국가별로 데이터를 분리해준다.
• group by c2.CustomerID를 통해 각 고객별로 데이터를 그룹화하여 각 고객의 총 지출 금액을 계산한다.
• select sum(o2.TotalAmount) as SumSpent는 각 고객의 총 지출 금액을 계산하여 SumSpent로 이름 붙인다.

이제 이 SumSpent 값들 중에서 최대값을 찾기 위해서 또 다른 쿼리를 사용한다.

 

max(SumSpent)

• 서브쿼리로 나온 결과 (SumSpent) 중에서 가장 큰 값을 선택한다.
• 이 값이 바로 각 국가에서 가장 많이 지출한 금액이 된다.

 

 

having 절 사용하지 않고 구할 수 있는 방법은 없을까? 이 방법은 너무 복잡한 것 같다..