[빅쿼리] LEAD와 LAG로 이전값 이후값을 동시에 분석하기

SQL에서 종종 이전 행 또는 이후 행을 \가지고 와서 1개의 Row에서 비교 분석을 해야할 일이 생긴다. 오늘은 Oracle문법에 기반한 Bigquery 환경에서 LEAD함수와 LAG함수를 통해 이것을 어떻게 제어할 수 있는지 기본문법과 함께 예시를 통해서 소개하고자 한다. 

목차

1. 빅쿼리에서 LEAD와 LAG함수의 기본 문법

2. LEAD 활용 방법 소개

3. LAG 활용 방법

 

1. Bigquery에서 LEAD와 LAG 함수 기본 문법

 

[ 탐색함수의 기본 문법 소개 ]

 

• 기본 문법 소개
  • LEAD | LAG ( 원하는 Column,  N번째 행)
    • 첫 번째 변수는 원하는 열을 지정해야 한다. 예를 들어 이전 혹은 이후 사람의 수학점수가 궁금하다면 점수에 해당하는 열을 삽입하면 된다. 
    • N번째 행은 말 그래도 앞 혹은 뒤에 몇 번째 값을 가져올 수 있는 값이다. 이 부분은 입력하지 않으면 Default로 1로 지정이 되니 크게 신경 쓸 필요는 없다.
  • OVER 절
    : OVER 절은 순서를 어떠한 기준으로 나누거나 정렬할지 결정하는 아주 중요한 부분이다. 
    
    • PARTITION BY 
      : 이전 혹은 이후 행을 조회할 때, 일정한 기준을 갖고 자르는 것을 의미한다. 예를 들어서, 수학 점수를 나열할 때 전교생이 아닌 반을 기준으로 나열을 하고 싶다면 반드시 이 구문을 삽입해야 한다. 
    • ORDER BY 
      : 이전 이후 행을 가져올 때, 오름차순으로 할지 혹은 내림차순으로 할지 결정하는 변수이다. 이것도 왠만하면 반드시 필수로 지정하여 데이터를 다루는 것이 가장 좋다. 

 

이렇게만 하면 어떻게 써야할지 감이 안 올 수 있으니 아주 쉬운 예시를 통해 실습을 해보도록 하겠다. 아래의 예시 데이터셋 코드를 참조하길 바란다. 

 

 

 

 

[ 연습용 미니 데이터 셋 소개]

 

여기서는 아주 단순한 데이터로 연습해보는 것을 기초로 한다. 1 ~ 3반으로 구성된 학교에서 수학 시험을 봤을 때 나온 점수이다. 아래의 실습을 할 때는 이 예시 코드를 그냥 복붙해서 사용하기를 바란다. 

WITH math_score AS
 (
  SELECT '원장님' as name, 50 as math_score,'1반' as class
  UNION ALL SELECT '단비', 99, '2반'
  UNION ALL SELECT '영웅', 40, '3반'
  UNION ALL SELECT '철수', 90, '1반'
  UNION ALL SELECT '영희', 80, '2반'
  UNION ALL SELECT '맹구', 70, '3반'
  UNION ALL SELECT '흰둥', 95, '1반'
  UNION ALL SELECT '훈이', 87, '2반'
  UNION ALL SELECT '짱아', 20, '3반'
  UNION ALL SELECT '짱구', 40, '1반'
  UNION ALL SELECT '영수', 45, '2반'
  UNION ALL SELECT '토끼', 73, '3반'
  )

빅쿼리 Lead와 Lag에 활용할 기초 예제

 

 

 

 

2. LEAD 활용 방법 소개

• 실전 Question1. 반별로 수학 점수가 2등이 누구인지 알 수 있도록 코드를 구성해보자.

select a.name
  , a.math_score
  , a.class
  , LEAD(a.name) OVER (PARTITION BY a.class ORDER BY a.math_score DESC) as lead_value
from math_score as a
 

빅쿼리 Lead 예시 결과 조회

 

여기서 핵심은 순서를 나열 할 때, 반을 기준으로 나누고 순서를 정렬하는데 있었다. 그래서 PARTITION BY a.class 코드가 들어간 것이다. 또한 수학점수가 높은 것을 기준으로 정렬해야 했기 때문에 DESC를 썼다. 

 

 

 

 

3. LAG 활용 방법 소개

 

• 실전 Question2. 전교로 봤을 때, 단비보다 2등 아래인 학생은 누구인가?

 
select a.name
  , a.math_score
  , a.class
  , LAG(a.name,2) over (ORDER BY a.math_score ASC) as LAG_value
from math_score as a
where 1 = 1
ORDER BY a.math_score desc

 

빅쿼리 LAG함수 예시화면 결과

이번에는 전교생을 대상으로 했기 때문에 PARTITION BY 구절을 제외되었다. 결과 단비보다 2등 아래에 있는 사람은 철수였던 것으로 나타났다.