실습 | JOIN/ GROUP BY/ 서브쿼리/ 순번 지정 윈도우 함수

📂SQL 실전 문제풀이 (LV4. 단골 고객 찾기)

문제1 : 고객별 주문 건수와 총 주문 금액 조회 하기 (주문 안한 고객도 포함) 

▶ POINT.  그룹바이 하고 집계 & 테이블 조인후 매칭 컬럼 출력   

 

[테이블 정보]

[풀이 과정]

• 고객별 집계 :  고객id 그룹바이 후 주문수 카운팅 / 주문 금액 합 집계   (users 테이블 내에서 집계 가능) 
• 서브쿼리 : 가독성을 위해 with 문으로 뺴줌 
• Left join  : orders 테이블에 없는 customerID & customername 출력에 포함 (만족한 조건에 대해 orders 테이블에 있는 컬럼 추가 반환) 
• <주의> null 처리 : 주문내역이 없는 customer 의 주문건수/주문계 는 null 로 연결 → coalesce함수로 0으로 반환 함.

WITH o AS (
	SELECT CustomerID
		   ,COUNT(DISTINCT OrderID) AS cnts -- 고객별 주문 건수 카운팅 (order id는 pk 지만 중복오류 감안 distinct 사용)
		   ,SUM(TotalAmount) AS total -- 고객별 주문 금액 합 
	FROM Orders
	GROUP BY CustomerID
) -- with 문 서브쿼리 생성 

SELECT c.CustomerName
	   ,COALESCE(o.cnts,0) AS OrderCount -- 주문내역이 없는 user 주문수는 0 처리
	   ,COALESCE(o.total,0) AS TotalSpent -- 주문내역이 없는 user 주문금액은 0 처리
FROM Customers c 
	LEFT JOIN o ON c.CustomerID=o.CustomerID -- 주문 내역이 없는 고객도 포함 해야함= left join 선택

 

[정답 쿼리]

• JOIN으로 테이블 연결 후 그룹바이 & 집계 처리 

SELECT 
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    COALESCE(SUM(o.TotalAmount), 0) AS TotalSpent
FROM 
    Customers c
LEFT JOIN 
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY 
    c.CustomerName;

 

[피드백]

• 쿼리 길이  : 내쿼리 < 정답 쿼리
• 가독성 : 내쿼리 > 정답 쿼리

➡️ FEEDBACK & INSIGHT

문제의 조건을 한 테이블내에서 만족시킬 수 있고 & 그룹바이 된 결과 테이블 기준으로 (=그룹핑 이전 테이블 값 불러오는게 아닌)  다른 테이블의 컬럼을 추가 할 때,

→ 바로 조인해주고 그룹바이 + 집계로 처리. 간단한 로직에서는 굳이 서브쿼리 사용 안하는 게 간결하다.

 

문제2 : 국가별 총 주문 금액이 가장 높은 고객 이름과 그 고객의 총 주문 금액 조회 (주문내역 없는 고객도 포함)

▶  POINT. 국가별 주문 금액 집계를 위해 조인 & 집계값 null → 0처리 & 조건별 최대값 윈도우 함수  

 

[풀이 과정]

• 고객별(&국가별) 총 주문 금액 : 각 기준별 그룹바이를 위해 먼저 테이블 조인 필요, 주문내역이 없는 고객도 포함해야 하므로 left join → 국가별/고객별 그룹바이 후 총 주문 금액 출력
• null 처리 : coalesce 함수로 주문 내역이 없는 고객의 null 값을 0처리 
• 국가별 총 주문 금액이 가장 높은 고객 명  : rank 윈도우 함수 사용하여 국가별 총 주문 금액이 큰 순으로 넘버링, rank 함수를 적용하여 최대값이 동일한 고객 전부 출력 ( 임의의 한명이라면 row_number 사용 해도 무방)  → rk= 1 일때 국가,고객명,총 주문 금액 출력  

WITH rk_t(
    SELECT c.Country
          ,c.CustomerName AS top_customer
          ,COALESCE(SUM(o.totalamount),0) AS top_spent
          ,rank() OVER (PARTITION BY c.country ORDER BY COALESCE(SUM(o.totalamount),0) DESC) AS rk
    FROM customers c
      LEFT JOIN orders o ON c.customerID=o.customerID
    GROUP BY c.Country,c.CustomerName
)

SELECT Country 
      ,top_customer
      ,top_spent
FROM rk_t
WHERE rk=1

 

 

[정답 쿼리& 피드백]

• rank 윈도우 함수 없이, join & having 절 단일행 서브쿼리 & lnline 서브쿼리 중첩하여 쿼리 작성

SELECT 
    c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent 
FROM Customers c
  JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY  c.Country, c.CustomerName
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
                             )

 

➡️ FEEDBACK & INSIGHT

다른 테이블애 그룹핑 조건이 있고 & 그룹핑 된 기준으로 최종 컬럼 출력 한다면 → 선 join그룹별 특정 정렬 순번으로 필터링 할때  → 순번 윈도우 함수 사용 & 서브쿼리로 감싸고 → 최종 필터링 진행

 

🔥순번 부여 윈도우 함수 ( ROW_NUMBER/ RANK / DENSE_RANK) 🔥

* 동일 값 처리 기준 비교

1. ROW_NUMBER : 정렬 순으로 넘버링 & 동일 값은 임의대로 넘버링 ( 즉, 겹치는 순번 없음)

2. RANK : 정렬 순으로 넘버링 & 동일값은 중복하여 넘버링( 중복되는 만큼 그 다음 순번 건너 뜀)

3. DENSE_RANK :  정렬 순으로 넘버링 & 동일값은 중복하여 넘버링(그 다음 순번 차례대로 넘버링)

 

EX) 컬럼내 (50,50,30,10) 값이 저장 되어있다면,

• ROW NUMBER 윈도우 함수 출력 값 : 1,2,3,4 (단, 어떤 행의 50 값이 1번이 될지는 모름)
• RANK  윈도우 함수 출력 값 : 1,1,3,4 (단, 1이 두번 중복 됐으니 다음 값은 3이 됨)
• DENSE_RANK 윈도우 함수 출력 값 : 1,1,2,3 (단, 1이 두번 중복 되었으나 다음 값은 다음 순번 차례)