<TIL> 2023-12-26

내일배움캠프 본 캠프 6일차 

2주차부터는 프로그래밍 기초가 시작되었다.

그리고 이번 주차는 SQL을 배우는 주차이다.

 

사전 캠프 기간에 SQL관련해서 강의를 수강하고 본캠프 들어와서도 SQL 코딩 테스트 문제를 매일 풀었었는데

이번 주차는 그 학습 내용을 복습하는 주차로 가져가야할 것 같다.

---

오늘 복습한 내용

 

엑셀보다 쉽고 빠른 SQL  전체 빠르게 복습했다.

그 중에서 코드가 이해하기 조금 난해했던 2가지 

피벗 테이블과 윈도우함수에 대한 내용을 위주로 집중적으로 다시 강의를 보았다.

---

1. 피벗 테이블(Pivot Table)

SELECT age, 
       MAX(IF(gender='male', cnt_order, 0)) 'male', 
       MAX(IF(gender='female', cnt_order, 0)) 'female'
FROM 
(
SELECT gender, 
       CASE WHEN c.age BETWEEN 10 AND 19 THEN 10 
            WHEN c.age BETWEEN 20 AND 29 THEN 20
            WHEN c.age BETWEEN 30 AND 39 THEN 30
            WHEN c.age BETWEEN 40 AND 49 THEN 40
            WHEN c.age BETWEEN 50 AND 59 THEN 50
            END age, 
       COUNT(1) cnt_order
FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id 
WHERE age BETWEEN 10 AND 59
GROUP BY 1, 2
) a 
GROUP BY 1
ORDER BY 1, 2

 

피벗 테이블에서 피벗은 회전 축이라는 사전적 의미를 갖고 있다고 한다. 

가로축 세로축에 특정한 데이터 컬럼을 지정하여 해당 축의 집계를 보는 테이블로 

데이터를 볼 때 상당히 유용한 테이블이다. 엑셀에서는 간단한 UI로 이를 볼 수 있는데 

SQL에서는 다소 긴 코드가 필요한 편이다(엑셀보다 쉽고 빠르진 않다). 실행 결과는 아래와 같다.

 

위 처럼 쿼리를 작성하면 서브쿼리 내에 들어있는 나이대(age)가 가로축이 되고 메인 쿼리에서 성별이 세로축이 된다. 

GROUP BY 와 MAX()로 집계하는 이유는 실행 결과가 아래 스크린샷처럼  세로축의 범주 별로 따로 나오기 때문

(맨 밑 레퍼런스 링크 참고)

---

2. WINDOW 함수

SELECT cuisine_type, 
       restaurant_name, 
       cnt_order,
       ROUND((cnt_order / sum_cuisine)*100, 2) "rn_rate_per_cuisine", 
       cum_cuisine
FROM
	(
	SELECT cuisine_type,
        	restaurant_name,
        	cnt_order, 
        	SUM(cnt_order) OVER (PARTITION  BY cuisine_type) sum_cuisine,
        	SUM(cnt_order) OVER (PARTITION  BY cuisine_type ORDER BY cnt_order) cum_cuisine
	FROM 
            (
             SELECT cuisine_type, 
                    restaurant_name,
                    COUNT(1) cnt_order
             FROM food_orders
             GROUP BY 1, 2
             ) a
	) b
ORDER BY 1, 5

 

WINDOW함수는 GROUP BY를 사용하지 묶어서 데이터를 볼 수 있게 해주는 함수이다

행과 행간의 관계를 정의하게 위해서 제공되는 함수로 순위, 합계, 평균, 행 위치 등을 조작할 수 있게해준다.

 

위 SQL문을 실행하면 음식타입과 레스토랑 명을 기준으로 그룹을 지어 행의 계수를 집계한 데이터를 서브쿼리로

음식타입별 행 수의 합계와, 음식타입별 누적합계를 구한다.

그리고 다시 이를 서브 쿼리로 하여  음식 타입별 행수 합계에 해당 식당에서 팔린 음식 수를 나누어

음식 타입 카테고리 내에서 해당 식당이 얼마나 팔렸는지의 퍼센트를구한다.

 

강의에서는 윈도우함수에 대해서 그렇게까지 자세하게 설명하지는 않아서 따로 책과 구글링을 통해 

얻은 정보들을 아래에 정리했다.

 

WINDOW함수는 아래의 형식으로 작성한다. 

WINDOW_FUNCTION(ARGUMENT) OVER (PARTITION BY 칼럼 ORDER BY WINDOWING절)

• ARGUMENT : WINDOW함수에 따라서 0~N개의 인수를 설정
• PARTITION BY : 전체의 집합을 기준에 의해 소그룹으로 나눔
• ORDER BY : 항목 정렬
• WINDOWING : 행의 기준의 범위를 정함

1. WINDOWING

WINDOWING 구조	설명
ROWS	부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	논리적인 주소에 의해 행 집합을 지정
BETWEEN ~ AND	윈도우의 시작과 끝의 위치를 지정
UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행임을 의미
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행임을 의미
CURRENT ROW	윈도우의 시작 위치가 현재 행임을 의미

 

1. 윈도우 함수 종류

1)  순위 함수

RANK	설명
RANK	특정항목 및 파티션에 대해서 순위를 계산한다. 동일한 순위는 동일한 값이 부여 된다. ex) 2위가 2명이면 다음 순위는 4등
DENSE_RANK	동일한 순위를 하나의 건수로 계산한다. ex) 2위가 2명이어도 다음 순위는 3등
ROW_NUMBER	동일한 순위에 대해서 고유의 순위를 부여한다.

 

2)  집계 함수

AGGREGATE	설명
SUM	파티션 별로 합계를 계산
AVG	파티션 별로 평균을 계산
COUNT	파티션 별로 행 수를 계산
MAX와 MIN	파티션 별로 최댓값/최솟값을 계산

 

3)  행 순서 함수

ORDER	설명
FIRST_VALUE	파티션에서 가장 처음 나오는 값을 구한다. MIN함수를 사용해서 같은 결과를 구할 수 있다.
LAST_VALUE	파티션에서 가장 마지막에 나오는 값을 구한다 MAX함수를 사용해서 같은 결과를 구할 수 있다.
LAG	이전 행을 가지고 온다.
LEAD	윈도우에서 특정한 위치의 행을 가지고 온다. 기본값은 1이다.

 

4)  비율관련 함수

RATIO	설명
CUME_DIST	파티션 전체의 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회 누적 분포상에서 위치를 0~1 사이 값을 가짐
PERCENT_RANK	파티션에서 제일 먼저 나온 것을 0 제일 늦게 나온 것을 1로 하여 값이 아닌 행 순서에 따른 백분율을 조회함
NTILE	파티션 별로 전체 건수를 ARGUMENT값으로  N등분한 결과를 조회
RATIO_TO_REPORT	파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회함

---

• Refercences
  • https://data-kindergarten.tistory.com/46
  • https://data-kindergarten.tistory.com/44

SQL 고급 : 윈도우 함수(순위 함수) - RANK(), DENSE_RANK(), ROW_NUMBER()

SQL 고급 : PIVOT TABLE