Sparta/Theory

[250818] 스파르타코딩 본캠프 10일차 (1) - window function, with

junecho 2025. 8. 18. 20:59

🟡 SQL 5차 강의 🟡

/ chat.csv 인코딩 utf-8

/ 나머지는 인코딩 euc-kr

 

 

✅ WINDOW FUNCTION                                                  

  • 행을 그대로 유지하면서 추가적인 값을 보여주는 기능
  • 모든 컬럼을 잃고 싶지 않을 때 사용
  • 행과 행의 관계를 알기 쉽게 해줌 (여러 행의 관계를 파악하기 위해 사용)

 

 

 

✅ 윈도우 함수 종류                                                            

  대표 함수 설명 GROUP BY 병행 여부
집계 함수 SUM(), MAX(), MIN(), AVG(), COUNT() 윈도우 범위 내 합계, 최대/최소, 평균, 개수 계산 가능
순위 함수 RANK(), DENSE_RANK(),
ROW_NUMBER() 		행에 순위를 매기거나 행 번호 부여 불가능
순서 함수 FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD() 파티션 내 첫 값/마지막 값, 이전/다음 행 값 참조 불가능
비율/분석 함수 RATIO_TO_REPORT(), PERCENT_RANK(), CUME_DIST(), NTILE(n) 비율, 누적 백분율, 분위수 계산 불가능

 

❓ 왜 집계 함수만 GROUP BY와 병행 가능할까 ❓

  • SUM, COUNT, AVG, MAX, MIN 같은 집계 함수는 여러 행을 묶어서 → 하나의 요약값을 만듦
  • 즉, 입력이 10행이든 100행이든, 결과는 한 값만 도출
  • GROUP BY의 역할
    • GROUP BY는 특정 기준(예: 부서, 서버, 지역 등)으로 행들을 그룹(묶음)으로 나눔
    • 각 그룹별로 집계 함수를 적용하면, 그룹 단위의 요약값을 얻음
SELECT dept, SUM(salary)
FROM basic.emp
GROUP BY dept;

 

 

 

 

✅ 윈도우 함수 문법                                                           

SELECT 절에서 사용 ! ! !

-- 윈도우 함수 기본 문법

SELECT 
    WINDOW_FUNCTION(컬럼명) OVER ( -- OVER는 윈도우 함수가 계산될 범위를 정하는 키워드
        PARTITION BY 컬럼명   -- 파티션 기준 그룹화
        ORDER BY 컬럼명       -- 파티션 내 정렬 기준
    ) AS 별칭
FROM 
    테이블명;
    
    
SELECT WINDOW_FUNCTION(컬럼명) OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명) AS 별칭
FROM 테이블명;

예시) ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)
-> ROW_NUMBER 라는 윈도우 함수를 사용하고, 그 기준을 컬럼1로 , 정렬은 컬럼2로 지정

 

  • OVER : 윈도우 함수 범위를 지정하는 핵심 키워드
  • PARTITION BY : 그룹핑 기준 (마치 GROUP BY처럼 작동하지만, 행은 그대로 유지)

 

 

🔰 윈도우 함수 괄호 ()                                                                                          

  • () 만 사용 : 순위 부여 함수
  • ( > 컬럼 < ) : 특정 값을 기준으로 비교, 계산하는 함수

/* 집계 함수(Group Function), 
윈도우 함수(Window Function), 
GROUP BY, HAVING 등을 모두 사용하는 복합 쿼리 기본 구조 예시 */

SELECT 
    컬럼명, 
    그룹함수(컬럼명), 
    윈도우함수 OVER (
        PARTITION BY 컬럼명
        ORDER BY 컬럼명
    ) AS 별칭
FROM 
    테이블명
WHERE 
    조건식  
GROUP BY 
    컬럼명
HAVING 
    조건식   -- GROUP BY에 대한 조건
ORDER BY 
    컬럼명 DESC, 
    컬럼명 ASC;

 

 

 

▼ 순위

🔰 RANK()                                                                                                              

  • ORDER BY 절 기준으로 순위를 부여
  • OVER 절에서 PARTITION BY를 사용하면 그룹 별로 순위를 계산할 수 있습니다. → 사용하지 않으면 전체 데이터 기준 순위가 계산
  • 동일한 값(동률)이 있으면 같은 순위를 부여하며, 그만큼 다음 순위는 건너뛰고 부여
RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

🔰 DENSE_RANK()                                                                                             

RANK와 작동법은 동일하나, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않음

DENSE_RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

🔰 ⭐ ROW_NUMBER() ⭐                                                                              

  • 동일한 값이어도 고유한 순위를 부여
  • 즉, 중복 값이 있어도 무조건 1, 2, 3, ... 순서대로 고유한 번호가 붙음
ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

 


▼ 순서

🔰 FIRST_VALUE                                                                                                

  • 지정한 PARTITION BY 그룹 내에서 ORDER BY 기준으로 가장 먼저 등장하는 값을 반환
FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

 

 

 

🔰 LAST_VALUE                                                                                                  

  • PARTITION BY 그룹 내에서 ORDER BY로 정렬된 결과에서 마지막 값을 반환
LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

 

 

 

🔰 ⭐ LAG ⭐                                                                                                      

  • 현재 행을 기준으로, 이전 N번째 행의 값을 가져옴
  • 별도 명시가 없는 경우, 기본값은 1
-- 내가 갖고 오고 싶은 컬럼명을 LAG() 안에 작성함.
LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)   


LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

 

 

 

🔰 ⭐ LEAD ⭐                                                                                                    

  • 현재 행을 기준으로 아래쪽으로 N번째 행의 값을 가져옴
  • 별도의 명시가 없다면 기본값은 1. 즉, 다음 행의 값을 가져옴
LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)
 
 LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

 

 

 

✨ LAG  VS  LEAD  VS  ROW_NUMBER()                                                    

함수 이름 정의 및 역할 기본값 대표 사용 예시 비고
LAG() 현재 행 기준으로 이전(N번째) 행의 값을 가져옴 N = 1 LAG(salary) OVER (ORDER BY hire_date) 과거값과의 비교
LEAD() 현재 행 기준으로 다음(N번째) 행의 값을 가져옴 N = 1 LEAD(salary, 2) OVER (PARTITION BY dept ORDER BY hire_date) 미래값 예측 등
ROW_NUMBER() 각 행에 고유한 순번을 부여함 순차적 번호 1부터 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 중복값 있어도 순번은 다름

 

 

 

▼ 비율

🔰 PERCENT_RANK                                                                                           

파티션 별로 백분율을 출력. 구간을 나누어 백분율로 출력

PERCENT_RANK() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

🔰 CUME_DIST                                                                                                     

  • 현재 행이 파티션 내에서 어느 정도 위치에 있는지" 누적 비율로 보여주는 함수
  • Ex) CUME_DIST() 결과가 0.75면 → 현재 행보다 작거나 같은 값이 75%라는 의미
CUME_DIST() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

 

🔰 NTILE                                                                                                                 

파티션 별 전체 건수를 계산한 값으로 N등분하고, 각 행에 해당 구간 번호(1~N)를 부여

NTILE(숫자) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

 

 

🔰 RATIO_TO_REPORT                                                                                     

  • 파티션 내 전체 SUM 값에 대한 행별 백분율을 소수점으로 출력
  • 결과값은 0~1 사이이며, 비율의 합은 1
  • MySQL 지원 X
-- 컬럼1의 값이 해당 파티션(그룹)의 전체 합계에서 어느 정도 비중(%)을 차지하는지 계산

RATIO_TO_REPORT(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

 

 

 

 

 

 

✅ WITH                                                                               

  • SQL 구문에서 사용되는 임시 테이블(가상 테이블)
  • 쿼리의 가독성 향상 및 쿼리 성능 최적화
  • 임시 테이블처럼 사용되며, 작성한 쿼리 내에서만 유효
  • 여러 개의 WITH 문 선언 가능
  • 한 테이블을 여러 번 조회해야 하는 경우, 1회만 조회해 성능 향상(한 번만 저장해놓으면 계속 쓸 수 있음)
  • 복잡한 JOIN, UNION 등 연산을 효율적으로 처리

Ex) 어떤 테이블에서 평균 급여보다 높은 사람을 구하기

       1) WITH ❌ ⇒ employees 테이블을 2번 읽어야 함

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

   

        2) WITH ⭕ ⇒ 평균 급여를 미리 구해놓고 저장해둔 걸 아래에서 재사용

                             → 가독성도 좋아지고, 성능도 빨라지고, 유지보수도 편함

WITH salary_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT *
FROM employees, salary_avg
WHERE employees.salary > salary_avg.avg_salary;

 

 

 

-- WITH 구문 기초 작성 방법 
WITH 임시테이블명 AS (
    SELECT 컬럼1, 컬럼2, ...
    FROM 원본테이블명
    WHERE 조건
)
SELECT 원하는컬럼1, 원하는컬럼2, ...
FROM 임시테이블명;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

'Sparta > Theory' 카테고리의 다른 글

[250820] 스파르타코딩 본캠프 12일차 (1) - Python 02 - if, for  (0) 2025.08.20
[250819] 스파르타코딩 본캠프 11일차 (1) - Python 01 - input, str  (3) 2025.08.19
[250814] 스파르타코딩 본캠프 9일차 (1) - union, join  (5) 2025.08.14
[250812] 스파르타코딩 본캠프 7일차 - group by, having, subquery  (4) 2025.08.12
[250806] 스파르타코딩 본캠프 3일차 - 데이터 리터러시  (3) 2025.08.06

'Sparta/Theory'의 다른글

  • 현재글[250818] 스파르타코딩 본캠프 10일차 (1) - window function, with

관련글

티스토리툴바