[프로그래머스 SQL 스터디 2기] 6주 차 TIL/후기

1. [프로그래머스 SQL 스터디 2기] 1주 차 TIL/후기

2. [프로그래머스 SQL 스터디 2기] 2주 차 TIL/후기​

3. [프로그래머스 SQL 스터디 2기] 3주 차 TIL/후기

4. [프로그래머스 SQL 스터디 2기] 4주 차 TIL/후기

5. [프로그래머스 SQL 스터디 2기] 5주 차 TIL/후기

6. [프로그래머스 SQL 스터디 2기] 6주 차 TIL/후기

 

오늘은 마지막 6주 차인 CTE & 통계 쿼리와 윈도우 함수 & 그룹 함수를 학습한다.

 

개발을 진행하면서 통계 쿼리를 짜고 윈도 함수를 사용하는 일이 자주 있었다.

 

일단은 쿼리를 짜고 윈도우 함수도 어떻게 작동이 되는지 모르고 사용을 하고 있었다.

 

이번 스터디를 계기로 통계 쿼리와 윈도우 함수가 조금 덜 무서워졌다!! 

 

CTE  & 통계 쿼리

CTE(Common Table Expression)는 중간 결과인 공통 테이블 식을 만드는 구문이다.

WITH 절을 이용해서 메모리에 데이터를 임시로 적재해서 사용하는 방식이다.

 

CTAS(Create Table As Select)는 가상 테이블인 View를 만드는 구문이다.

CTE와는 다르게 데이터베이스의 스키마에 테이블을 생성하고 데이터를 디스크에 저장한다. 

 

4주 차에 CTE의 기본 내용이 있다.

 

CTE에서는 이해가 조금 어려웠던 재귀 CTE와 통계 쿼리에 대해서 학습한다.

 

1. 재귀 CTE(Recursive CTE)
2. Pivot이란?

 

재귀 CTE(Recursive CTE)란?

재귀 CTE는 CTE를 재귀적으로 연속해서 다음 튜플을 생성하는 방식이다.

 

다음 예제는 2의 8승 이하를 출력시키는 예제이다.

 

WITH RECURSIVE table_name AS 구문으로 재귀 CTE가 시작된다.

첫 번째 SELECT문은 재귀가 아닌 시작이고, UNION ALL로 재귀를 반복할 하단 쿼리를 합병한다.

두 번째 쿼리가 재귀인데 WHERE 조건문에서 종료 조건을 작성한다.

 

WITH RECURSIVE temp AS 
(
   SELECT   1 AS n   -- non-recursive
   UNION ALL
   SELECT   n * 2    -- recursive
   FROM     temp 
   WHERE    n < 256  -- break condition
)
SELECT   *
FROM     temp;

 

 

Pivot이란?

행 데이터를 열로 지정해서 출력하는 방법이 PIVOT이다.

 

Oracle에서는 PIVOT 함수를 지원한다.

지원하지 않는 DBMS에서는 CASE 절과 집단 함수/GROUP BY 절을 이용해서 구현한다.

 

Ex) 고객별 2003년 1~12월의 지출 횟수를 출력한다. - (세로축 고객, 가로축 1월~12월)

(단, 고객의 지출 내역이 없어도 출력이 가능), 정렬은 고객 id 오름차순, 지출 일자 오름차순으로 한다.

 

1. 행 데이터를 확인한다.

하단과 같이 고객은 여러 개의 주문 날짜가 행으로 출력이 된다.

우리가 원하는 데이터는 세로축에 고객이 나오고, 가로축에 1월 ~ 12월이 나와야 한다.

SELECT	customerId, 
        name,
        paymentDate
FROM    customers LEFT JOIN payments USING(customerId)
WHERE	YEAR(paymentDate) = 2003
ORDER   BY customerId, paymentDate

 

 

 

2. CASE문을 이용해서 열로 출력한다.

행 데이터를 CASE문으로 열로 변환하면 월 별로 데이터들이 나온다.

현재는 해당 월일 때 자신의 월을 출력만 한 것이고, 이제 집단 함수로 통계 데이터를 생성한다.

SELECT	customerId, 
        name,
        paymentDate,
        CASE MONTH(paymentDate) WHEN 1 THEN 1 END 1월,
        CASE MONTH(paymentDate) WHEN 2 THEN 2 END 2월,
        CASE MONTH(paymentDate) WHEN 3 THEN 3 END 3월,
        CASE MONTH(paymentDate) WHEN 4 THEN 4 END 4월,
        CASE MONTH(paymentDate) WHEN 5 THEN 5 END 5월,
        CASE MONTH(paymentDate) WHEN 6 THEN 6 END 6월,
        CASE MONTH(paymentDate) WHEN 7 THEN 7 END 7월,
        CASE MONTH(paymentDate) WHEN 8 THEN 8 END 8월,
        CASE MONTH(paymentDate) WHEN 9 THEN 9 END 9월,
        CASE MONTH(paymentDate) WHEN 10 THEN 10 END 10월,
        CASE MONTH(paymentDate) WHEN 11 THEN 11 END 11월,
        CASE MONTH(paymentDate) WHEN 12 THEN 12 END 12월
FROM    customers LEFT JOIN payments USING(customerId)
WHERE	YEAR(paymentDate) = 2003
ORDER   BY customerId, paymentDate;

 

 

3. 집단 함수 SUM()를 이용해 통계 데이터를 출력한다.

집단 함수를 통해 유의미한 통계 데이터를 출력한다.

GROUP BY를 통해서 고객별 월의 지출 횟수를 튜플로 각각 묶어준다.

SUM() 함수를 통해서 지출 횟수를 전부 더해준다.

SELECT	customerId, 
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 1 THEN 1 END),0) 1월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 2 THEN 1 END),0) 2월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 3 THEN 1 END),0) 3월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 4 THEN 1 END),0) 4월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 5 THEN 1 END),0) 5월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 6 THEN 1 END),0) 6월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 7 THEN 1 END),0) 7월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 8 THEN 1 END),0) 8월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 9 THEN 1 END),0) 9월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 10 THEN 1 END),0) 10월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 11 THEN 1 END),0) 11월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 12 THEN 1 END),0) 12월
FROM	customers LEFT JOIN payments USING(customerId)
WHERE	YEAR(paymentDate) = 2003
GROUP	BY customerId
ORDER	BY customerId, paymentDate;

 

 

+ WITH 절로 튜플을 필터링해서 조인 연산 수행을 감소시키는 방법

현재 고객(customers) 테이블과 지출(payment) 테이블의 튜플 개수를 확인한다.

고객 테이블에는 122개, 지출 테이블에는 273개의 튜플이 존재한다.

 

고객 테이블과 주문 테이블 LEFT JOIN을 하면 연산 횟수는 122 X 273 = 33,306번이다.

현재는 데이터의 양이 많지 않아서 성능에 영향을 미치지 않지만, 튜플이 점점 늘어난다면 성능에 영향을 준다.

 

고객이 10,000명이고 지출이 100,000건이라면 10,000 X 100,000 = 1,000,000,000으로 10억 번이다.

인기가 있는 웹 사이트의 경우 고객이 10,000명을 훨씬 뛰어넘을 것이다.

 

SELECT   COUNT(*)
FROM     customers

 

SELECT   COUNT(*)
FROM	 payments

 

그래서 우리가 지금 해보려는 것은 튜플을 필터링해서 조인의 연산을 최소화하는 것이다.

지출 날짜가 2003년인 테이블의 횟수를 출력한다. 총 100개의 튜플이 나온다.

기존에 조인 횟수는 33,306번인데 현재는 122 x 100 = 12,200번의 연산의 결과가 나온다.

약 3분의 1로 조인 횟수가 줄어들었다.

SELECT	COUNT(*)
FROM    payments
WHERE   YEAR(paymentDate) = 2003;

 

 

 

최종 쿼리는 지출 테이블에서 2003년의 지출 날짜의 튜플을 먼저 필터링을 진행한다. 

WITH 구문으로 paymentsOf2003 이름으로 CTE를 만들고 customers 테이블과 조인시킨다.

나머지 통계 쿼리는 동일하다.

WITH	paymentsOf2003 AS 
(
        SELECT   customerId,
                 paymentDate
        FROM	 payments
        WHERE	 YEAR(paymentDate) = 2003
)
SELECT	customerId, 
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 1 THEN 1 END),0) 1월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 2 THEN 1 END),0) 2월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 3 THEN 1 END),0) 3월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 4 THEN 1 END),0) 4월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 5 THEN 1 END),0) 5월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 6 THEN 1 END),0) 6월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 7 THEN 1 END),0) 7월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 8 THEN 1 END),0) 8월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 9 THEN 1 END),0) 9월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 10 THEN 1 END),0) 10월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 11 THEN 1 END),0) 11월,
        COALESCE(SUM(CASE MONTH(paymentDate) WHEN 12 THEN 1 END),0) 12월
FROM	customers LEFT JOIN paymentsOf2003 USING(customerId)
GROUP	BY customerId
ORDER	BY customerId, paymentDate;

 

CTE 통계 쿼리를 진행할 때는 단계별로 데이터를 확인해보면서 하는 게 이해하기 더 쉽다.

 

1. 행 데이터를 확인해서 어떤 데이터들이 있는지 확인한다.
2. CASE문을 이용해서 행 데이터를 열로(PIVOT) 출력시켜 확인한다.
3. 집계 함수를 사용해서 유의미한 통계 결과를 낸다(COALESCE() 함수를 통해서 NULL이 나왔을 때를 처리한다.)
4. WITH 절로 튜플을 필터링한다.

 

TIL : CTE

6주차 TIL : CTE

 

윈도우 함수 && 그룹함수

윈도우 함수는 내장 함수의 종류로 다중행 내장 함수이다.

 

다중행 내장 함수의 종류는 집단 함수, 윈도우 함수, 그룹 함수가 있다.

• 집단 함수(COUNT(), SUM(), AVG()...)
  • 그룹별 통계치
• 윈도우 함수(집단 함수, 순위 함수, 비율 함수, 행 순서 함수)
  • 파티션으로 나누어서, 파티션 내에서 튜플 별 통계치
• 그룹 함수(ROLLUP(), GROUPING(), CUBE())
  • 소계, 중계, 합계, 총계 등 레별 별 통계치 제공

 

1. 윈도우 함수 구문
2. SELECT ORDER BY 절과 윈도우 함수 ORDER BY 절

 

윈도우 함수 구문

윈도우 함수 구문은 3가지 영역으로 나뉜다.

 

• PARTIION BY 절
  
  • 기준 컬럼의 테이블에 여러 개 파티션을 나누는 역할
• ORDER BY 절
  • 파티션 내 컬럼 정렬
• ROWS / RANGE 절
  • 파티션 내 프레임 정의

3가지 절은 모두 생략이 가능하다. 모두 생략을 해도 OVER () 괄호는 생략하면 안 된다.

SELECT window_function(컬럼) OVER
(
       [PARTITION BY 컬럼_리스트]                    /* PARTITION 절 */ 
       [ORDER BY 컬럼_리스트]                        /* ORDER 절 */
       [ROWS | RANGE frame_start | frame_between ]   /* FRAME 절 */
)
FROM 테이블
[ORDER BY 컬럼_리스트];

 

SELECT ORDER BY 절과 윈도우 함수 ORDER BY 절

SELECT ORDER BY 절은 출력되는 테이블 전체의 컬럼들을 정렬하는 역할이다.

 

윈도우 함수의 ORDER BY 절은 윈도우 함수 계산할 때와 파티션 내에서 행을 정렬할 때 사용하는 역할이다.

SELECT 문의 ORDER BY 절을 생략할 때는 윈도우 함수는 PARTITION BY 절 기준 속성 값은 오름차순 정렬되고, 파티션 내에서는 윈도우 함수 ORDER BY 절에 따라 출력된다.

 

TIL : 윈도우 함수 && 그룹함수

6주차 TIL : 윈도우 함수 && 그룹함수

 

6주 차 && 최종 SQL 코딩 테스트 후기

최종 SQL 코딩 테스트는 6주 차를 모두 진행하고 나서 1주 정도 시간을 주고 문제를 풀게 한다.

문제는 총 20문제이고, 총 3시간의 시간을 준다.

단테(멘토)님이 강의자료는 보지 않고 치는 게 좋다고 해서 보지 않고 진행했다. 

 

최종 스코어는 20문제 중에 17문제를 성공했다. 날짜를 다루는 문제에서 시간을 허비했고 결국은 풀지 못했다. 마지막 19번, 20번 문제는 제한시간 3시간을 넘겨서 풀지 못했다ㅠㅠ

 

마지막 최종 코딩 테스트를 진행하고 나서 내가 어떤 점이 더 부족한지 알게 돼서 좋았다.

 

프로그래머스 스터디의 좋은 점이 문제들을 다시 풀 수 있어서 좋은 거 같다.

이번 주 토요일까지 다시 풀 시간을 주니 다시 한번 풀어보도록 하자.

 

6주 동안의 시간이 빠르게 지나갔다. 단테님이 주말에 미리 하는 게 좋다고 하셨는데 1 ~ 2번 정도는 약속이 생기기도 해서 주말에 논 적도 있다. 주말에 놀고 나서 평일 새벽까지 문제를 풀고 다음 날 일을 가니 집에 와서 녹초가 된 적도 있었다ㅋㅋㅋㅋㅋㅋ 혹시나 글을 보시는 다음 기수 분들은 미리미리 주말에 하시는 것을 권장합니다...ㅎ

 

6주 동안 토요일 오전 10시에 다른 멘티 분들과 학습을 하면서 잃어버렸던 학습의지를 되찾게 되었고, 주말에 늦잠을 자는 습관도 조금 고치게 되었다. 

 

이번 스터디를 통해서 데이터베이스의 기본, 학습의지, 약간의 부지런함을 얻어가게 되었다 :)

 

이상으로 프로그래머스 1 ~ 6주 차 SQL 리뷰를 마칩니다~