목적
빅쿼리의 탐색함수 LAG를 사용해서 행의 이전 값을 가져와서 차이를 계산하는 방법을 이해합니다.
구글 빅쿼리 공식 설명 •
LAG
활용 예시
미국 코로나19 확진자 일별 차이 계산하기
공개되어 있는 미국 코로나19 코로나 확진자 통계를 활용해서 LAG를 어떻게 사용할 수 있는지 설명하겠습니다.
우선 빅쿼리 탐색기에서 covid19_usa 로 공개 데이터셋을 검색해서 데이터를 살펴보겠습니다.
summary 테이블을 선택하고, 미리보기를 해서 가장 마지막 페이지를 선택하면 다음과 같이 데이터를 볼 수 있습니다.
데이터가 어떻게 저장되어 있는지를 먼저 파악하겠습니다.
•
데이터는 state와 county_name, 그리고 date 별로 저장되어 있습니다.
•
confirmed cases와 deaths의 수가 증가는 하지만 동일한 날도 있는 것을 보면, 일별 신규 데이터가 아니라, 일별 누적 데이터임을 알 수 있습니다.
이제 미국 전체의 코로나19 확진자의 일별 누적 데이터를 뽑아내고 그 차이를 계산하려면 어떻게 해야할까요?
데이터를 보면, 일별 county 의 누적 확진자를 합하면, state의 일별 누적 확진자가 되고, 이를 합하면 미국 전체의 일별 누적 확진자가 되는 것을 알 수 있습니다.
단계별로 확인해보겠습니다.
1.
WY 주의 Weston County 의 일별 누적 확진자를 체크
소스 데이터가 일자별로 누적 확진자 수를 알려주고 있기 때문에 GROUP이나 SUM 없이도 원하는 결과를 얻을 수 있습니다.
SELECT
date
,confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
WHERE county_name LIKE '%Weston County%'
ORDER BY date ASC
SQL
복사
2.
WY 주의 일별 누적 확진자를 체크
이제부터는 GROUP 과 SUM 을 사용해야하는데요.
만약에 아래와 같이 쿼리하면 원하지 않는 결과를 얻게 됩니다.
일자별로 다른 누적 확진자 수가 표시되는데, county 가 생략되어 있는 것입니다. SELECT에 county_name을 넣어보면 바로 알 수 있지요.
SELECT
date
,confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
WHERE state = 'WY'
ORDER BY date ASC
SQL
복사
SELECT
date
,county_name
,confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
WHERE state = 'WY'
ORDER BY date ASC
SQL
복사
WY 주의 일별 누적 확진자수를 알기 위해서는 date로 그룹핑하면서 각 county 의 해당일의 누적 확진자 수를 SUM 해주어야 합니다.
SELECT
date
,SUM(confirmed_cases) as confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
WHERE state = 'WY'
GROUP BY date
ORDER BY date ASC
SQL
복사
우선 결과가 맞는지 확인하기 위해서 위에서 쿼리한 값을 CSV로 저장해서 2022-02-14 의 WY 주 누적 확진자를 계산해두겠습니다. 153,095명입니다.
새로 수정한 쿼리의 결과를 보면 2022-02-14 의 확진자 수가 153,095로 잘 맞음을 볼 수 있습니다.
3.
미국 전체의 일별 누적 확진자를 체크
이제 WHERE 문을 제거하면, 모든 데이터를 일자별로 GROUP 하여 미국 전체의 일별 누적 확진자 값을 얻을 수 있습니다.
SELECT
date
,SUM(confirmed_cases) as confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
GROUP BY date
ORDER BY date ASC
SQL
복사
4.
LAG를 활용해 현재 행 누적 확진자 수 옆에 어제 누적 확진자 수 가져오기
LAG 는 빅쿼리에서 윈도우 함수 (또는 분석 함수) 중 탐색 함수에 속합니다.
지정하는 윈도우 (유저가 지정하는 행의 그룹)에서 행의 위치를 나타내는 단일 값을 가져오는 함수를 탐색함수라고 하는데, LAG는 뒤쳐지다라는 뜻 그대로, 현재 행을 기준으로 이전 행의 값을 가져옵니다.
기본적으로는 다음과 같이 표현할 수 있습니다.
오프셋은 생략할 수 있으며 기본으로는 이전 행을 값을 가져오는 1입니다.
(만약, 2행 앞의 값을 가져오기 위해서는 2로 오프셋을 설정하면 됩니다.)
LAG(불러올 항목, 오프셋) OVER (유저가 지정하는 행의 그룹 조건)
SQL
복사
이제 다음과 같이 LAG 함수를 적용해보겠습니다.
새롭게 생성된 열의 값이 바로 이전 행의 값임을 확인할 수 있습니다.
SELECT
date
,SUM(confirmed_cases) as confirmed_cases
,LAG(SUM(confirmed_cases)) OVER (ORDER BY date ASC) as prev_confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
GROUP BY date
ORDER BY date ASC
SQL
복사
5.
새로운 쿼리로 차이값 계산하기
이제 데이터는 준비 되었기 때문에 다음과 같이 쿼리를 작성하면 차이값과 어제 대비 증가율을 구할 수 있습니다.
WITH source_table AS (
SELECT
date
,SUM(confirmed_cases) as confirmed_cases
,LAG(SUM(confirmed_cases)) OVER (ORDER BY date ASC) as prev_confirmed_cases
FROM `bigquery-public-data.covid19_usafacts.summary`
GROUP BY date
ORDER BY date ASC
)
SELECT
date
,confirmed_cases
,prev_confirmed_cases
,(confirmed_cases - prev_confirmed_cases) as changes
,(confirmed_cases - prev_confirmed_cases) / prev_confirmed_cases * 100 as change_percentage
FROM source_table
SQL
복사
관련 포스팅
다음의 포스팅을 준비 중에 있습니다.
•
[빅쿼리] LEAD 다음 행 값 가져오기, 행 간 차이 계산하기
