[SQL] Challenge 1회차 - 윈도우함수는 컬럼생성, first_value, last_value, lag, lead

 

# 윈도우 함수의 종류

 

 

# 윈도우 함수는 도대체 왜 사용하는 것일까?

 

A ) 모든 컬럼을 잃고 싶지 않을 때 사용합니다 🥳

 

Q ) 컬럼을 잃는다는게 무슨소리임?

조금 더 자세히 설명해 주세요!!!

 

 

 

예를 들어 보자

CREATE TABLE chat_logs (
    user_id VARCHAR(50),
    chat_text VARCHAR(255),
    review_date DATE
);

 

※ 참고

date형일 경우 YYYY-MM-DD

time형일 경우 HHH-MI-SS

datetime형일 경우 YYYY-MM-DD HH-MI-SS 

형이 된다

 

INSERT INTO chat_logs (user_id, chat_text, review_date) VALUES
('피카츄', '안녕안녕', '2024-01-01'),
('피카츄', '배고파', '2024-01-02'),
('피카츄', '심심해', '2024-01-07'),
('파이리', '불닭최고', '2024-01-01'),
('파이리', '족발최고', '2024-01-02'),
('꼬부기', '엽떡최고', '2024-01-01'),
('꼬부기', '진화하고싶다!', '2024-01-04');

 

select *
from chat_logs

 

 

 

위 표에서, 집계함수를 사용하여 USER_ID 별 가장 큰 날짜를 계산하면, 아래와 같이 나오겠죠?

 

select user_id,
	   max(review_date)
from chat_logs
group by user_id

 

 

원래는 최대 날짜에 대한 text값은 '심심해' 이지만

 

 

max(review_date)에 대한 chat_text가 출력되지는 않습니다

 

select user_id,
	   chat_text,
	   max(review_date)
from chat_logs
group by user_id

 

 

 

따라서 이를 해결하기 위한 방법은 3가지가 있습니다

 

< 1. 서브쿼리 사용하기 >

 

- 원래 결과값의 정답

 

[ 정답 1 ]

select *
from chat_logs
where (user_id, review_date) in 
(
	select user_id,max(review_date)
	from chat_logs
	group by user_id
)

 

 

 

 

< 2. 셀프조인 이용 >

※ 조인시 조건을 여러개 할 수 있다!

 

select *
from chat_logs c1
inner join (
	select user_id, 
		   max(review_date) as max_date
	from chat_logs
	group by user_id
) as c2
on c1.user_id = c2.user_id
and c1.review_date = c2.max_date

 

 

 

[ 정답 2 ]

select c1.user_id,
	   c1.chat_text,
	   c1.review_date
from chat_logs c1
inner join (
	select user_id, 
		   max(review_date) as max_date
	from chat_logs
	group by user_id
) as c2
on c1.user_id = c2.user_id
and c1.review_date = c2.max_date

 

 

 

 

그렇다면 다른 방법은 없을까......?!

 

 

있다! 정답3!!

 

만약 윈도우 함수가 생각나지 않는다면 다음 자료를 참조하자

https://anotherhound.tistory.com/61

[SQL] 윈도우함수 총정리

 

< 3. 윈도우 함수 > 

 

< 순위✅ >

- 형식

윈도우함수() over( partition by 컬럼 order by 컬럼 ) as 별칭

 

 

1) 다른순위

동일한값 - 다른순위

ROW_NUMBER()

ex) 1 2 3 4 1 2

 

2) 같은순위

동일한값 - 같은순위 / 중간순위를 비운다

RANK()

ex) 1 1 3 4 4 6

 

동일한값 - 같은순위 / 중간순위를 비우지 않는다

DENSE_RANK()

ex) 1 1 2 3 3 4

 

[ 정답 3 ]

with ranked_logs as (
	select user_id,
		   chat_text,
		   review_date,
		   row_number() over(partition by user_id 
		   					 order by review_date desc) as review_num 
	from chat_logs
)
select *
from ranked_logs
where review_num = 1

 

 

 

< ✅윈도우 함수의 본질 > 

group by로 그룹화 후 사용할 수는 없다

본래의 윈도우 함수의 성질이 왜곡되기 때문이다

 

윈도우 함수는 원래의 데이터에서 

select *

from 테이블

에서 순위 컬럼을 새로 생성한다

 

따라서 우리는 where을 통해서 순위 컬럼을 조건을 통하여 필터링 후 이용 가능하다

 

 

select user_id, 
	   chat_text,
	   review_date,
	   row_number() over(partition by user_id 
		   			     order by review_date desc) as review_num 
from chat_logs

 

 

 

< 순서✅ >

 

1. FIRST_VALUE

 

파티션별 가장 먼저 나온 값을 구하여 출력합니다. 공동 등수를 인정하지 않고 처음 나온 행만 가져오며 MIN함수를 쓰는 것과 결과가 동일합니다.

 

문법: FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

select user_id,
	   chat_text,
	   review_date,
	   first_value(chat_text) over(partition by user_id order by review_date) as first_chat_text
from chat_logs

 

 

 

 

2. LAST_VALUE

 

파티션별 가장 마지막에 나온 값을 구하여 출력합니다. 공동 등수를 인정하지 않고 나중에 나온 행만 가져오며 MAX함수를 쓰는 것과 결과가 동일합니다. 

 

문법: LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 

 

select user_id,
	   chat_text,
	   review_date,
	   last_value(chat_text) over(partition by user_id order by review_date
	   rows between unbounded preceding and unbounded following) as last_chat_text
from chat_logs

 

 

 

※ 여기서 preceding이란?!

 

PRECEDING 이란 그 전의 라는 뜻입니다.

 

 

3. LAG 

 

이전 N 번째의 행을 가져오는 함수입니다. N 은 최대값으로 3을 가지며, 기본값은 1 입니다.

문법: LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 
         OR
LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 

 

※ 숫자는 최대 3으로 3번째 전 행을 의미

 

select user_id,
	   chat_text,
	   review_date,
	   lag(chat_text,1) over(partition by user_id order by review_date) as prev_chat_text
from chat_logs

 

 

 

[ 다른 예시 ]

select *
from employees

 

 

select *
, LAG(SALARY) OVER (ORDER BY NAME) as PREV_SAL 
from employees

 

 

 

 

4. LEAD  

이후 N행의 값을 가져오는 함수입니다. N 은 최대값으로 3을 가지며, 기본값은 1 입니다.
       

문법: LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 
            OR
 LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 

 

※ 숫자는 최대 3으로 3번째 후 행을 의미

 

select user_id,
	   chat_text,
	   review_date,
	   lead(chat_text,1) over (partition by user_id order by review_date) as next_chat_text
from chat_logs

 

 

 

 

< 중요한 함수들✅ >

 

1. concat

문자열을 병합할때 사용한다

select concat('길쭉한길','쭉이')

 

 

2. substring

문자열을 자를때 사용한다

select substring('니가먼데니까짓게내가한수련에비하며넌아무것도아니다',1,4)

 

 

 

3. substring_index

문자열을 특정 구분기호의 인덱스 전까지 출력한다

select substring_index('맛있는.싸이버거,감자튀김.너도한번먹어봐!,진짜꿀맛.리얼리.참트루','.',1)

 

 

select substring_index('맛있는.싸이버거,감자튀김.너도한번먹어봐!,진짜꿀맛.리얼리.참트루','.',2)

 

 

select substring_index('맛있는.싸이버거,감자튀김.너도한번먹어봐!,진짜꿀맛.리얼리.참트루','.',3)

 

 

4. 그 외 함수들