[인프런 스터디 빅쿼리 2주차] SQL 스터디 윈도우 함수(WINODW FUNCTION), FRAME, QUALIFY

인프런에서 강의도 듣고 스터디 신청시 30% 할인 쿠폰을 통해 강의도 수강하고, 스터디도 참여할 수 있습니다. 카일스쿨님의 인프런 빅쿼리 SQL 스터디 2주차에서 학습한 SQL의 꽃, 윈도우 함수와 FRAME, QUALIFY 활용법을 요약해보았습니다. 

 

 

<잠깐🫸 질문> 

다음과 같은 질문에 SQL(쿼리)로 바로 떠올라서, 쓸 수 있다면 스크롤을 내리지 않아도 돼요! 
Q1. 상품 데이터에서, 상품별 누적 매출값을 표에 추가해주세요. 
Q2. 유저/고객별로  다음/이전에 방문한 날짜는?
Q3. 날짜별로 현재부터 5일전까지 평균매출값은? 

 

 

 

SQL에서 윈도우~ 함수 모르면 윈도우~  없는 집 처럼 데이터 분석에 빈틈이 생기는...! 
그럼 스크롤을 내려볼까요~ let's go ~

 

 

 

 

🔑 중요 키워드: 윈도우 함수, 함수종류, 활용법, FRAME, QUALIFY



📄 1. 윈도우 함수 개념 정의 

윈도우 함수란?

윈도우 함수 (Window Function)는 SQL에서 데이터를 창문(window)처럼 특정 범위 내에서 계산하는 데 사용하는 함수로, 각 행을 기준으로 누적합, 순위 등을 계산할 수 있습니다. 집계 함수와 비슷하지만, 윈도우 함수는 행 단위로 계산을 수행하여 결과를 출력하며, 그룹별로 데이터를 집계하는 동시에 원래의 행 구조를 유지합니다.

 

💻 윈도우 함수 예시와 주요 기능들

1. 순위 함수 (Ranking Functions)

• RANK(): 동일한 값이 있으면 같은 순위를 부여하지만, 다음 순위는 건너뜁니다.
• ROW_NUMBER(): 고유한 순위를 부여하며, 중복 순위 없이 고유 순서를 매깁니다.
• DENSE_RANK(): 동일 순위가 있어도 다음 순위가 연속됩니다.

 

예시 테이블: product_sales 

*예시 테이블은 모두 직접 임의로 제작한 테이블입니다. 

product_type	product_name	revenue
A	Product 1	500
A	Product 2	450
A	Product 3	450
A	Product 4	400
B	Product 5	700
B	Product 6	600
B	Product 7	600
B	Product 8	550

 

SQL 쿼리 예제코드

SELECT *, 
RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS rank,
ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS row_num, 
DENSE_RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS dense_rank
FROM product_sales;

쿼리 결과 

product_type	product_name	revenue	rank	row_num	dense_rank
A	Product 1	500	1	1	1
A	Product 2	450	2	2	2
A	Product 3	450	2	3	2
A	Product 4	400	4	4	3
B	Product 5	700	1	1	1
B	Product 6	600	2	2	2
B	Product 7	2	3	2
B	Product 8	550	4	4	3

 

 

 

2. 탐색 함수 (Navigation Functions)

LEAD와 LAG는 이전 혹은 다음 행의 값을 참조할 때 유용합니다. 이를 통해 사용자별 이전 또는 이후 행동을 쉽게 분석할 수 있습니다.

 

예시 테이블: 방문기록

user_id	visit_month
1	2023-01
1	2023-02
1	2023-04
1	2023-05
2	2023-03
2	2023-04
2	2023-06

 

 

 

탐색 함수 예시설명

 

LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month)	다음 방문 월을 가져옵니다.
LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month)	이전 방문 월을 가져옵니다.

 

SQL 쿼리 예제코드

SELECT user_id, visit_month, 
LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS 다음_방문,
LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS 이전_방문
FROM 방문기록;

 

 

쿼리 결과 

user_id	visit_month	다음_방문	이전_방문
1	2023-01	2023-02	NULL
1	2023-02	2023-04	2023-01
1	2023-04	2023-05	2023-02
1	2023-05	NULL	2023-04
2	2023-03	2023-04	NULL
2	2023-04	2023-06	2023-03
2	2023-06	NULL	2023-04

3. 집계 함수 (Aggregation Functions)

윈도우 집계 함수는 여러 행의 값을 누적하거나 평균, 최대/최소 값을 구하는 데 사용됩니다.
SUM, AVG, COUNT 등이 있으며, 각 행을 기준으로 누적 합계나 평균 등을 계산하여 추가 정보를 제공할 수 있습니다.

 

예시 테이블: Sales_data

store	sales_date	sales
A	1/1/23	100
A	1/2/23	150
A	1/3/23	200
B	1/1/23	120
B	1/2/23	180
B	1/3/23	140

 

 

SQL 쿼리 예제코드

SELECT store, sales_date, sales, 
SUM(sales) OVER (PARTITION BY store ORDER BY sales_date) AS 누적_매출 
FROM sales_data;

 

 

결과 테이블 

store	sales_date	sales	총_매출	누적_매출
A	1/1/23	100	450	100
A	1/2/23	150	450	250
A	1/3/23	200	450	450
B	1/1/23	120	440	120
B	1/2/23	180	440	300
B	1/3/23	140	440	440

 

 

 

🖽 윈도우 함수의 다양한 Frame 지정

윈도우 함수를 사용할 때 ROWS 또는 RANGE 키워드를 사용해 특정 범위 내에서 계산을 제한할 수 있습니다.
이를 Frame 지정이라고 하며, 특히 시간 데이터에서 과거와 미래 데이터를 참조할 때 유용합니다.

• ROWS: 물리적인 행 수를 기준으로 범위 지정
• RANGE: 논리적인 값 범위로 지정 (ex: 날짜 간격)
  
  

주요 Frame 경계 설정 예시

1. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행을 기준으로 이전과 다음 행을 포함
2. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 시작부터 현재 행까지 범위 지정

 

 

예시 테이블: Sales_data

product_type	sales	sales_date
A	100	1/1/24
A	200	1/2/24
A	150	1/3/24
B	300	1/1/24
B	400	1/2/24
B	350	1/3/24

 

SQL 쿼리 예제코드 

SELECT sales, sales_date,
AVG(sales) OVER (PARTITION BY product_type ORDER BY sales_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적평균
FROM sales_data;

 

결과 테이블 

sales	sales_date	누적평균
100	1/1/24	100
200	1/2/24	150
150	1/3/24	150
300	1/1/24	300
400	1/2/24	350
350	1/3/24	350

 

 

윈도우 함수 연습문제 예시

 

1. 다음 접속 월과 다다음 접속 월 구하기

 

예시 테이블: user_visits

user_id	visit_month
1	2024-01
1	2024-02
1	2024-03
2	2024-01
2	2024-03
2	2024-04
3	2024-02
3	2024-05

 

SELECT user_id, visit_month,
LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS 다음_방문,
LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS 다다음_방문
FROM user_visits;

user_id	visit_month	다음_방문	다다음_방문
1	2024-01	2024-02	2024-03
1	2024-02	2024-03	NULL
1	2024-03	NULL	NULL
2	2024-01	2024-03	2024-04
2	2024-03	2024-04	NULL
2	2024-04	NULL	NULL
3	2024-02	2024-05	NULL
3	2024-05	NULL	NULL

 

 

 

2. 특정 시점 누적 합계 및 평균 주문량 구하기

 

예시 테이블: user_visits

user_id	visit_month
1	2024-01
1	2024-02
1	2024-03
2	2024-01
2	2024-03
2	2024-04
3	2024-02
3	2024-05

SELECT *, 
SUM(amount) OVER () AS 총_주문량,
SUM(amount) OVER (PARTITION BY order_date ORDER BY order_date) AS 누적_주문량,
SUM(amount) OVER (PARTITION BY user_id ORDER BY user_id) AS 사용자별_누적_주문량,
AVG(amount) OVER (ORDER BY order_date
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS 최근_5개_주문량_평균
FROM orders;

 

 

 

쿼리 결과 테이블

 

user_id	visit_month	다음_방문	다다음_방문
1	2024-01	2024-02	2024-03
1	2024-02	2024-03	NULL
1	2024-03	NULL	NULL
2	2024-01	2024-03	2024-04
2	2024-03	2024-04	NULL
2	2024-04	NULL	NULL
3	2024-02	2024-05	NULL
3	2024-05	NULL	NULL

 

 

윈도우 함수 활용 TIP! 

1. Frame 설정으로 세부 분석: 현재 기준으로 이전 데이터와의 비교가 필요할 때는 ROWS와 RANGE를 적절히 활용해보세요.
2. Subquery 없이 분석 가능: QUALIFY 절을 사용하면 서브쿼리 없이 바로 조건을 설정할 수 있어 쿼리 속도를 높일 수 있습니다.
3. 정확한 순위 부여: 공동 순위나 고유 순서가 필요할 때는 RANK()와 ROW_NUMBER()를 상황에 맞게 사용하여 안정적인 결과를 얻을 수 있습니다.

 

과제 해결 과정, 느낀점 (카일스쿨님 인프런 강의 중간 후기)  

 

일단, 명쾌하십니다.
카일스쿨님 강의는 처음 들어도 쉽게 이해할 수 있고, 또 내용이 쉬운편 ~ 활용편까지 궁금했던 부분을 긁어주는 명강의입니다. 

가장 좋았던 부분은 이론~실습 적용할떄, 오류가 난 부분을 과감없이 보여주시면서 왜 오류가 났고, 어떻게 해석해야 되는지 등
수강생의 입장을 많이 헤아려 주십니다. 

또한, 쏟아지는 질문에도 답변을 진짜 열심히 해주십니다. 
강의를 수강하면 거기서 끝나는 게 아니라 슬랙, 디스코드 채널 등에서 수강생분들과의 커뮤니티에 열정이 느껴집니다.. 

특히나 커리어적으로도 PM, 마케터, 데이터 분석, 사이언티스트, 개발 등 모든 분야를 섭렵하고 계시는 분이 아닐지..
여러가지 질문에 대한 답변 퀄리티가 👍  개인적으로 너무 멋진분..이라는 생각이 ㅎㅎ 듭니다. 

 

 

 이번 주차를 통해서는

 

 FRAME, IGNORE NULLS,QUALIFY 와 같은 새로운 함수 및 기능들을 알게 되었습니다. 

❗️FRAME의 default값은 UNBOUNDED PRECEDING AND CURRENT ROW

❗️LAST_VALUE에서 IGNORE NULLS 안하면  값은 NULL

---

 

 

인프런 빅쿼리 스터디에서 제공하는 이 함수들은 SQL 데이터 분석에 매우 유용하며, 정해진 프레임 내에서 데이터를 분석하는 데 특히 큰 도움이 됩니다.

 

📊   SQL 윈도우 함수  쿼리테스트 문제풀이 보러가기 

 

 

🙌 더 자세한 내용은 카일스쿨님 블로그 및 인프런 Bigquery(SQL) 활용편 강의에서 만나볼 수 있습니다. 

 

 

 

인스타그램 : @dataviz_easy

이메일 : dataviz.easy@gmail.com