SQL 6일차. 탐색함수

 

3. 탐색함수 (그룹 내 행 순서 관련 함수)

 

3-1. LAG, LEAD

• LAG : 이전 행의 필드를 읽음
• LEAD : 다음 행의 필드를 읽음

예시 1) 이전행, 다음행의 ID값 출력하기

select 
  id,
  first_name,
  last_name,
  lag(id) over(order by id) as id_prev,
  lead(id) over(order by id) as id_next,
 from `thelook_ecommerce.users`
 where id in (1,2,3,4,5)
 order by id

 

예시 2) 이벤트 테이블에서 얼마만의 재방문인지 확인하기

원본 테이블 (BEFORE)	재방문 확인 테이블 (AFTER)

 

예시 3) 코비드 데이터세트에서 2020년 5월 1일~10일의

국가별 전날 확진자 수와 다음날 확진자수 표시하기

원본 테이블 (BEFORE)	전날, 다음날 확진자수 표시 테이블 (AFTER)

 

연습문제 <11-1>

-dataflix_covid_dataset 데이터세트, world_covid 테이블

-전날 대비 확진자수(confirmed_prior_day), 전날 대비 사망자수(deaths_prior_day) 조회

• 조회항목 : country_name, state_name, refresh_date, confirmed, diff_confirmed_prior_day,
                    deaths, diff_deaths_prior_day
• 정렬조건 : 국가명 오름차순, 시도명 오름차순, 발생날짜 오름차순

▼ (풀이 예시) 클릭!

select
  country_name,
  state_name,
  refresh_date,
  confirmed,
  (confirmed - lag(confirmed) over 
    (partition by country_name, state_name order by refresh_date))
    as diff_confirmed_prior_day,
  deaths,
  (deaths - lag(deaths) over 
  	(partition by country_name, state_name order by refresh_date)) 
    as diff_deaths_prior_day
from `radiant-cycle-374600.dataflix_covid_dataset.world_covid`
order by country_name, state_name, refresh_date

 

-출력 결과

 

 

 

 

3-2. FIRST_VALUE, LAST_VALUE

• FIRST_VALUE : 그룹 내의 첫 값을 구함
• LAST_VALUE : 그룹 내의 마지막 값을 구함 (항상 자기 자신임)
• 전체 그룹에 대한 마지막 값을 구하려면 'ROWS' 옵션 필요.

select 
  id,
  email,
  created_at,
  FIRST_VALUE(id) OVER ( ORDER BY id ) as first_id,
  FIRST_VALUE(email) OVER ( ORDER BY id ) as first_email, 
  LAST_VALUE(id) OVER ( ORDER BY id ) as last_id,
  LAST_VALUE(email) OVER ( ORDER BY id ) as last_email
from `thelook_ecommerce.users`
where id between 1 and 10

 

select 
  country_name,
  refresh_date, 
  FIRST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed) as first,
  LAST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed) as mid,
  LAST_VALUE(confirmed) OVER (PARTITION BY country_name ORDER BY confirmed ROWS 
    BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last
from `dataflix_covid_dataset.world_covid`
ORDER BY country_name

• ROW : 부분집합인 윈도우 크기를 물리적인 단위로 행 집합을 지정
• UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫 번째 ROW
• UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 ROW

 

 

3-3. NTH_VALUE

• 현재 윈도우 프레임에 있는 N번째 행의 값 반환.
• 없으면 NULL을 반환.

select 
  id,
  email,
  created_at,
  NTH_VALUE(email, 5) OVER ( ORDER BY id ) as second_signup_user_email, 
  NTH_VALUE(created_at, 5) OVER ( ORDER BY id ) as second_signup_user_created_at
from `thelook_ecommerce.users`

 

 

 

3-4. PERCENT_RANK()

• 현재 행의 상대적 순위 반환
• 계산에 따라 0과 1 사이의 범위에서 행의 백분율 순위를 계산.

select 
  brand,
  cost,
  PERCENT_RANK() OVER (PARTITION BY brand ORDER BY cost )
from `thelook_ecommerce.products`