SQL 고급 : 윈도우 함수(분석 함수) - LAG(), LEAD()

 

LAG(), LEAD() 함수란?

LAG : 특정 로우(행)를 기준으로 이전(before) 로우(행)의 값을 반환
LEAD : 특정 로우(행)를 기준으로 다음(next) 로우(행)의 값을 반환

 

 

윈도우 함수(분석함수)인 LAG, LEAD 함수를 어떻게 활용하는지 사용자의 웹 행동 로그 데이터를 이용해 살펴보도록 하겠습니다. 아래와 같이 특정 사용자의 웹 행동 로그 데이터가 저장되어 있는 가상의 테이블이 있다고 가정해보겠습니다.

 

user_id	occurred_at	event_type	event_name	location	device
4	2014-05-20 9:31:30	engagement	login	India	samsung galaxy s4
4	2014-05-20 9:31:55	engagement	search_autocomplete	India	samsung galaxy s4
4	2014-05-20 9:31:56	engagement	home_page	India	samsung galaxy s4
4	2014-05-20 9:31:59	engagement	search_run	India	samsung galaxy s4
4	2014-05-20 9:32:31	engagement	search_autocomplete	India	samsung galaxy s4
4	2014-05-20 9:33:01	engagement	search_autocomplete	India	samsung galaxy s4
4	2014-05-24 11:39:53	engagement	login	India	samsung galaxy s4
4	2014-05-24 11:40:20	engagement	home_page	India	samsung galaxy s4

데이터 출처 : Mode SQL Tutorial : SQL Analytics Training

 

 

테이블의 event_name 컬럼을 확인해보면 특정 유저가 로그인을 포함한 특정 액션을 취한 로그 기록들이 모두 남아 있습니다. 예를 들어 이 테이블에서 유저는 5/20 9:31:30에 처음 로그인을 하였고 5/20 9:31:55와 9:31:56에 각각 두 번째, 세 번째 액션을 취하였습니다. 여기서 LAG(), LEAD() 함수를 사용하면 이전 로우, 다음 로우의 값을 반환해 발생 시간을 비교해서 볼 수 있는데요. 이를 다음 쿼리를 통해 나타낼 수 있습니다.

 

SELECT user_id,
       event_type,
       event_name,
       occurred_at,
       LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS next_event
FROM events

 

🕒 실행결과

 

user_id	event_type	event_name	occurred_at	last_event	next_event
4	engagement	login	2014-05-20 9:31:30		2014-05-20 9:31:55
4	engagement	search_autocomplete	2014-05-20 9:31:55	2014-05-20 9:31:30	2014-05-20 9:31:56
4	engagement	home_page	2014-05-20 9:31:56	2014-05-20 9:31:55	2014-05-20 9:31:59
4	engagement	search_run	2014-05-20 9:31:59	2014-05-20 9:31:56	2014-05-20 9:32:31
4	engagement	search_autocomplete	2014-05-20 9:32:31	2014-05-20 9:31:59	2014-05-20 9:33:01
4	engagement	search_autocomplete	2014-05-20 9:33:01	2014-05-20 9:32:31	2014-05-24 11:39:53
4	engagement	login	2014-05-24 11:39:53	2014-05-20 9:33:01	2014-05-24 11:40:20
4	engagement	home_page	2014-05-24 11:40:20	2014-05-24 11:39:53

 

보시는 것과 같이 첫 번째 로그인의 경우 이전 로우의 값이 없기 때문에 last_event 컬럼에 null 값이 반환되고, 다음 로우의 값은 있으므로 next_event 컬럼에 다음 로우의 값이 반환되는 것을 확인할 수 있습니다. 이처럼 LAG, LEAD 함수는 시계열 데이터에 많이 사용되곤 합니다.

 

LAG, LEAD 함수를 이용한다면 궁극적으로 유저의 로그 데이터를 통해 어떤 행동(액션)에 대한 리드타임을 계산할 수 있습니다. 여기서 리드타임이란 예를 들어 사용자가 로그인을 한 다음 검색 버튼을 클릭 했을 때 검색 버튼을 누르는데까지 걸린 시간을 말합니다. 즉, 위에 테이블에서는 사용자가 로그인을 하고 자동완성 검색 버튼을 누르기 까지 걸리는 시간을 계산해주려면 next_event 컬럼에서 occurred_at 컬럼을 빼주는 방식으로 그 리드타임을 계산할 수 있을 것입니다.

 

쿼리로는 다음과 같이 나타낼 수 있습니다.

 

 

SELECT user_id,
       event_type,
       event_name,
       occurred_at,
       occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
       LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event
FROM events

 

🕒 실행결과

 

user_id	event_type	event_name	occurred_at	last_event	next_event
4	engagement	login	2014-05-20 9:31:30		0:00:25
4	engagement	search_autocomplete	2014-05-20 9:31:55	0:00:25	0:00:01
4	engagement	home_page	2014-05-20 9:31:56	0:00:01	0:00:03
4	engagement	search_run	2014-05-20 9:31:59	0:00:03	0:00:32
4	engagement	search_autocomplete	2014-05-20 9:32:31	0:00:32	0:00:30
4	engagement	search_autocomplete	2014-05-20 9:33:01	0:00:30	4 days 02:06:52
4	engagement	login	2014-05-24 11:39:53	4 days 02:06:52	0:00:27
4	engagement	home_page	2014-05-24 11:40:20	0:00:27

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.