SQL 라이브 세션 7회차 - WINDOW FUNCTION, WITH구문

오늘은 SQL의 WINDOW FUNCTION과 WITH 구문을 배웠다. SAS에서 다뤄본적이 없는 함수와 구문들이어서 생소하고 어렵게 느껴졌다. 하지만 실무에서 이런 기능을 모르면 쿼리가 복잡해지고 비효율적으로 작성될 수 있다고한다.

 

---

Ⅰ. WINDOW FUNCTION

윈도우 함수는 여러 행의 관계를 파악하기 위해 즉, 행과 행 간의 관계를 정의하기 위해 사용되는 함수이다. 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다. 윈도우 함수의 종류는 다음과 같다:

종류	함수
순위	RANK, DENSE_RANK, ROW_NUMBER
집계	SUM, MAX, MIN, AVG, COUNT
순서	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율	RATIO_TO_REPROT, PERCENT_RANK, CUME_DIST, NTILE

 

볼드체로 표시한 함수들이 실무에서 자주 사용되는 함수라고 한다. 이 중 집계함수를 제외한 모든 윈도우 함수에서는 GROUP BY절을 사용할 수 없고, 대신 PARTITION BY 라는 절을 사용해서 GROUP BY와 유사한 기능을 하게 할 수 있다.

 

1. 문법

윈도우 함수는 SELECT절에서 사용되며, 기본 문법은 아래와 같다:

SELECT WINDOW_FUNCTION () OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
FROM 테이블명

 

 

2. 종류

위에서 윈도우 함수의 종류를 전체적으로 살펴보았는데, 여기서는 실무에서 자주 사용되는 집계 함수를 제외한 함수들 위주로 좀 더 구체적으로 살펴보겠다.

 

1) RANK

RANK 함수는 ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수이다. PARTITION 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있다. 동일한 값에 대해서는 같은 순위를 부여하여 중간 순위를 비운 값이 출력된다. RANK함수의 기본 문법은 다음과 같다:

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

 

※ 예제

# 윈도우 함수 - RANK 함수 예제 
select *,
rank() over(partition by JOB order by SALARY)
from basic.window1

 

 

2) DENSE_RANK

DENSE_RANK 함수는 RANK 함수와 작동법은 동일하지만, 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않는다는 차이가 있다. DENSE_RANK 함수는 동점자를 구하고 싶을 때, 동일한 값에 대해서 같은 순위를 부여하고, 나머지 순위를 비우지 않는 방식으로 사용된다. DENSE_RANK 함수의 기본 문법은 다음과 같다:

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

 

※ 예제

# 윈도우 함수 - DENSE_RANK 함수 예제
select *,
dense_rank() over(partition by JOB order by SALARY)
from basic.window1

 

 

3) ROW_NUMBER

RANK, DENSE_RANK는 동일한 값에 대해 동일 순위를 부여하는 반면, ROW_NUMBER 함수는 동일한 값이어도 고유한 순위를 부여한다. 다만, 순위가 부여되는 기준은 사용자가 지정하지 않는 한 프로그램이 임의로 처리하기 때문에 그 기준을 알 수 없다. 실무 적용 예시로, 결제 금액같은 경우에는 10원, 1원 단위로도 결제를 많이 하기 때문에 같은 순위를 부여받는 경우가 적다고 한다. ROW_NUMBER 함수의 기본 문법은 다음과 같다:

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

 

※ 예제

# 윈도우 함수 - ROW_NUMBER 함수 예제
select *,
ROW_NUMBER() over(partition by JOB order by SALARY)
from basic.window1

 

 

4) LAG

LAG 함수는 이전 N 번째의 행을 가져오는 함수이다. 별도 명시가 없을 경우, 기본값은 1이다. 가져올 행이 없을 경우, NULL 값을 가지게 된다. LAG 함수의 기본 문법은 다음과 같다:

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

 

※ 예제

# 윈도우 함수 - LAG 함수 예제: 2번째 전 값 구하기 
select *
, LAG(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1

 

 

 

5) LEAD

LEAD 함수는 이후 N행의 값을 가져오는 함수이다. LAG 함수의 반대 개념으로 이해하면 쉽다. LEAD 함수도 LAG 함수와 마찬가지로 별도 명시가 없는 경우, 기본값은 1이다. LEAD 함수의 기본 문법은 다음과 같다:

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

 

※ 예제

# 윈도우 함수 - LAG 함수 예제: 2번째 전 값 구하기 
select *
, LAG(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL 
from basic.window1

 

 

Ⅱ. WITH

WITH 구문은 SQL 구문에서 사용되는 임시테이블(가상테이블)을 의미하며, 작성한 쿼리 내에서만 실행가능하다. 하나의 SQL 구문에서 여러개의 WITH 문을 선언할 수 있으며, WITH 구문을  사용하면 쿼리의 가독성을 높이고 복잡한 연산을 보다 효율적으로 처리할 수 있다는 장점이 있다. WITH 구문의 기본 문법은 다음과 같다:

WITH 임시테이블명 AS
(	SELECT 컬럼1, 컬럼2, ...
	FROM 테이블명
)
SELECT 임시테이블에서 불러온 컬럼 중 필요한 컬럼
FROM 임시테이블명

 

 

※ 예제1

# with 구문 활용 예시1 
with soso as  # with 뒤쪽에 임시테이블명 지정
(	select etc_str2, etc_str1, count(distinct game_actor_id)as actor_cnt 
	from basic.users
	group by etc_str2, etc_str1 # 임시테이블을 만들 때 활용할 테이블 
)
select *
from soso # WITH절에서 지정한 임시테이블명 
;

 

※ 예제2

# with 구문 활용 예시2 - 경험치가 가장 많은 캐릭터 정보 조회하기 
with dodo as # with 뒤쪽에 임시테이블명 지정
(	select * 
	from basic.users
)
select *
from(	select max(exp)as maxexp
		from dodo
	)as a 
inner join
	(	select *
		from dodo
	)as b 
on a.maxexp=b.exp
;

 

※ 예제3

# with 구문 활용 예시3 - 다중 with 구문  
with gogo as # 첫번째 with 절
(	select game_account_id, exp
	from basic.users 
	where `level` >50
),
hoho as # 두번째 with 절, with 구문은 처음 한번만 작성합니다. 
(	select distinct game_account_id, pay_amount, approved_at
	from basic.payment 
	where pay_type='CARD'
) # 이 부분에서 with 구문이 종료됩니다. 
select case when b.game_account_id is null then '결제x' else '결제o' end as gb
, count(distinct a.game_account_id)as accnt 
from gogo as a
left join hoho as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제x' else '결제o' end
;

 

 

 

Ⅲ. 그 외 중요한 함수

함수 이름	정의	문법	결과
CONCAT	문자열을 병합할 때 사용하는 함수	CONCAT('피카츄','라이츄')	피카츄라이츄
SUBSTRING	문자열을 자를 때 사용하는 함수	SUBSTRING('피카츄라이츄',2,4)	카츄라
SUBSTRING_INDEX	문자열을 특정 구분기호를 통해 출력할 때 사용하는 함수	SUBSTRING_INDEX('피카츄.라이츄', '.', 1)	피카츄
ABS	절대값을 출력하는 함수	ABS(-1)	1
ROUND	숫자를 소숫점 이하 자릿수에서 올림하여 출력하는 함수	ROUND(2.77, 1)	2.8
NOW SYSDATE CURRENT_TIMESTAMP	현재시간과 날짜를 출력하는 함수	NOW() SYSDATE() CURRENT_TIMESTAMP	현재시간출력
DATE_ADD	날짜에서 기준값 만큼 덧셈하여 출력하는 함수	DATE_ADD('2025-11-04', INTERVAL 1 DAY) 기준값: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND	2025-11-05
DATE_SUB	날짜에서 기준값 만큼 뺄셈하여 출력하는 함수	DATE_SUB('2025-11-04', INTERVAL 1 DAY) 기준값: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND	2025-11-03
DATEDIFF	두 날짜를 뺄셈하여 출력하는 함수	DATEDIFF('2025-11-04','2025-11-01')	3
DATE_FORMAT	날짜를 형식에 맞게 출력하는 함수	DATE_FORMAT(now(), '%Y-%m-%d') * 대소문자 구분 중요!	현재 시간이 yyyy-mm-dd로 출력
UNIX_TIMESTAMP	현재시간을 UNIXTIME으로 구하는 함수 (현재 시간을 정수 형태로 변환하는 함수)	UNIX_TIMESTAMP()	정수출력

 

 

---

마무리

오늘은 WINDOW FUNCTION과 WITH구문, 그리고 그 외 자주 사용되는 함수들을 배웠다. 윈도우 함수를 몰랐을 때는 JOIN을 여러번 사용해서 쿼리가 불필요하게 길고 복잡해졌었는데, 윈도우 함수를 활용하면 좀 더 직관적으로 쿼리를 작성할 수 있을 것 같았다. 그리고 전에 SAS를 사용할 때는 데이터셋을 중간중간 WORK 라이브러리에 저장해 두고, 이를 다시 불러와 사용할 수 있어 편리했는데, MySQL에서는 그런 방식이 어려워 매번 서브쿼리를 사용해야 해서 쿼리가 길어지고 불편했다. 그런데 WITH 구문을 배우고 나니 쿼리를 단계별로 나누어 더 깔끔하게 작성할 수 있어 훨씬 편리하게 느껴졌다. 

오늘은 SQL 마지막 라이브 세션이었다. SQL을 활용하기 위한 모든 문법은 다 배웠다고 한다. 아직 익숙하지 않은 함수들과 구문이지만 최대한 자주 사용하며 손에 익힐 수 있도록 복습을 많이 해야겠다.