[My SQL] LAG 함수 / 현재값 vs 이전값 증감율(차이) 계산하기

LAG 함수는 현재 행을 기준으로 이전 값을 출력한다.
 
날짜, 매출, 수량 등의 차이 및 증감율을 계산할 때 사용하기 좋은 함수이므로,
전년 / 전월 / 전일 매출을 비교하거나, 유저의 첫 구매일과 마지막 구매일의 차이를 계산하여 구매주기를 파악하는 등 다양한 마케팅 지표로 활용될 수 있다 ☺️
 
! LAG 함수의 기본적인 뼈대는 아래와 같다 !
 

 LAG ( [컬럼명] , {N번째 행} ) OVER ( PARTITION BY [컬럼명] ORDER BY [컬럼명] )

 
- LAG([컬럼명],{N번째 행} ): [컬럼명] 기준으로 N번째 행 이전 값을 출력

- PARTITION BY [컬럼명] : [컬럼명] 기준으로 그룹핑

    > PARTITION BY는 GROUP BY와 유사한 기능이다.

 

- ORDER BY [컬럼명] : [컬럼명]기준으로 정렬 

    > PARTITION BY의 그룹핑 기능이 필요 없을 경우 생략 가능, 그러나 ORDER BY 쿼리는 반드시 포함해야 한다. 

 

빠른 이해를 위해서, 샘플 쿼리문과 출력된 데이터를 보면서 LAG 함수 쿼리문을 뜯어보겠다-!
 
아래 쿼리문은 특정 월의 일별 active_user를 수치와 함께
LAG 함수를 활용하여 당일 vs 전일 active_user 증감율까지 볼 수 있도록 하였다.
 

 

SELECT *, 
       ROUND((active_user - prev_active_user) / prev_active_user * 100, 2)
       AS vs_prev
FROM ( 
       SELECT * , 
       LAG(active_user , 1) OVER (ORDER BY date_7) 
       AS prev_active_user
       FROM (
                  SELECT DATE_FORMAT(visited_at, '%Y-%m-%d') 
                  AS date_7, 
                 COUNT(DISTINCT customer_id) 
                 AS active_user
                 FROM visit
                 WHERE visited_at >= '2020-07-01' 
                 AND visited_at < '2020-08-01'
                 GROUP BY 1  
                 ) AS A1
           ) AS A2;

 

 
서브 쿼리문이 2개나 사용되어 넘나 복잡해보이지만..
이번 포스팅에서는 딱 2가지 쿼리문에 집중 - ✨ 
LAG 쿼리문과 백분율 산출 연산에만 집중한다면 써먹을 일이 많을 것이다!
 

 

⇩ 먼저 LAG 함수가 사용된 쿼리문을 살펴보자!

 

LAG (active_user , 1) OVER (ORDER BY date_7) AS prev_active_user 

 
- LAG (active_user , 1)  : active_user 컬럼의 값을 1행 이전 값으로 출력

- OVER (ORDER BY date_7)  : date_7 컬럼 기준으로 정렬

- AS prev_active_user : 컬럼명은 prev_active_user 로 지정
 

 

아래 출력된 데이터를 보면 active_user 컬럼의 값을 1행 이전 값으로 출력한 prev_active_user 컬럼이 생성된 것을 확인할 수 있다.
 

 

active_user 출력 값이 1행 씩 밀려난 것을 확인 (보라색 화살표 참조) / 백분위 연산을 통해 전일대비 증감율 산출 (vs_prev(%), 4번째 열)

 

  
active_user 컬럼의 값을 1행 이전 값으로 출력한 prev_active_user 컬럼이 생성되었다.
두 컬럼의 값의 차이를 백분율로 환산하면 증감율을 구할 수 있으므로 아래와 같은 쿼리문을 추가한다.
 

 

ROUND ( (active_user - prev_active_user) / prev_active_user * 100 , 2 ) AS vs_prev

 

 
- ROUND ( (active_user - prev_active_user) / prev_active_user * 100 , 2 )
: 소수점이 2자리 값을 반올림하여 환산한다.   ex) ROUND (0.1234 , 1) = 0.1 로 출력 
   ㄴ (active_user - prev_active_user) / prev_active_user * 100
    : 백분율 계산 공식을 적용해준다 (현재값 - 이전값) / 이전값 * 100 
 
 

오늘 My sql 쿼리문은 여기까지 🥱
다음 포스팅에서 유익한 쿼리문 또 공유해볼게요 !