[ 쿼리 ] Advanced SQL for Data Professionals (4) - Filling Gaps in Data Using SQL

이 포스트는 Medium에 올라온 다음 아티클을 참고하여 정리한 글입니다.

Advanced SQL for Data Professionals

---

이번 포스트에서 정리할 Advanced SQL 케이스는 Filling Gaps in Data Using SQL(누락값 처리) 이다.

 

뒤에 굳이 Using SQL을 붙인 건 SQL이 아닌 다양한 도구로 Filling Gaps in Data를 할 수 있어서 그런 걸까 싶다.

BI 업무에서는 해당 케이스를 활용할 일이 없는 것 같다. 그래서 공부할 때를 생각해보면 누락값 처리에 SQL보다는 Python의 도구들을 더 많이 썼던 것 같다.

---

[ Filling Gaps in Data Using SQL ]

 

누락값 처리가 필요한 경우로 Medium 아티클에서는 오른쪽의 이미지와 같이 주말의 데이터를 예시로 들었다.

근무일이 아닌 날에는 데이터 집계를 하지 않는 서비스들이 있을 수 있기 때문이다.

노란색으로 표시된 데이터를 보면 2006-01-06 날짜의 다음 데이터가 2006-01-09다.

2006-01-07, 2006-01-08 이 주말이어서 데이터가 없는 것.

 

누락값 처리의 방법은 다양하지만 이번 SQL를 활용한 누락값 처리 방법은 인접한 바로 앞의 값을 그대로 가져다 쓰는 Forward Fill 방법을 구현한다.

 

 

 

 

 

 

WITH RECURSIVE calendar (Date) AS (
    SELECT CAST('2006-01-01' AS date) AS Date
    UNION ALL
    SELECT date_add(Date, INTERVAL 1 DAY)
    FROM calendar
    WHERE Date <= '2006-01-31' -- Put the end date here 
)
, currency (date, price, currency) AS (
SELECT CAST('2006-01-02' AS date) ,3.2582, 'USD'
UNION SELECT CAST('2006-01-03' AS date) ,3.2488  , 'USD'
UNION SELECT CAST('2006-01-04' AS date) ,3.1858  , 'USD'
UNION SELECT CAST('2006-01-05' AS date) ,3.1416  , 'USD'
UNION SELECT CAST('2006-01-06' AS date) ,3.1507  , 'USD'
# missing data
UNION SELECT CAST('2006-01-09' AS date) ,3.1228  , 'USD'
UNION SELECT CAST('2006-01-10' AS date) ,3.128   , 'USD'
UNION SELECT CAST('2006-01-11' AS date) ,3.1353  , 'USD'
UNION SELECT CAST('2006-01-12' AS date) ,3.1229  , 'USD'
UNION SELECT CAST('2006-01-13' AS date) ,3.1542  , 'USD'
# missing data
UNION SELECT CAST('2006-01-16' AS date) ,3.1321  , 'USD'
UNION SELECT CAST('2006-01-17' AS date) ,3.1521  , 'USD'
UNION SELECT CAST('2006-01-18' AS date) ,3.1887  , 'USD'
UNION SELECT CAST('2006-01-19' AS date) ,3.1772  , 'USD'
UNION SELECT CAST('2006-01-20' AS date) ,3.1868  , 'USD'
# missing data
UNION SELECT CAST('2006-01-23' AS date) ,3.1397  , 'USD'
UNION SELECT CAST('2006-01-24' AS date) ,3.1333  , 'USD'
UNION SELECT CAST('2006-01-25' AS date) ,3.095   , 'USD'
UNION SELECT CAST('2006-01-26' AS date) ,3.1253  , 'USD'
UNION SELECT CAST('2006-01-27' AS date) ,3.1379  , 'USD'
# missing data
UNION SELECT CAST('2006-01-30' AS date) ,3.1559  , 'USD'
UNION SELECT CAST('2006-01-31' AS date) ,3.163   , 'USD'
)

SELECT date, price, currency
FROM (
	SELECT ROW_NUMBER() OVER (PARTITION BY currency, a.date ORDER BY b.date DESC) lp
		, a.date
		, b.date org_date
		, b.price    
		, currency
	FROM calendar a 
	LEFT JOIN currency b 
	ON b.date BETWEEN a.date - INTERVAL 3 DAY AND a.date 
) a
WHERE lp = 1
ORDER BY date;

 

데이터를 미리 입력해두지 않고 CTE를 활용하는데 CTE의 개념은 이 포스트에서 따로 다루지는 않는다.

이전에 CTE에 대해 다루었던 블로그 포스트는 다음 링크를 참조

[ 쿼리 ] Advanced SQL for Data Professionals (1)

 

쿼리 결과는 오른쪽과 같다.

기존에 없던 날짜인 2006-01-07, 2006-01-08에 2006-01-06 날짜 데이터가 그대로 사용되었다.

 

그 방법은 calendar와 currency 데이터를  JOIN 할 때 calendar의 날짜가 currency의 날짜보다 3일 전까지 매칭되도록 하고 매칭된 날짜 중에 currency의 날짜가 가장 나중인 데이터 만 필터한다.

가장 나중인 데이터를 필터하는 방법이 ROW_NUMBER() OVER 절을 활용해 b.date DESC 조건으로 정렬하고 행번호인 lp=1 조건의 데이터만 조회하는 것이다.

 

 

 

 

 

 

 

lp=1 조건을 빼고 모든 필드를 보면 오른쪽과 같은 결과가 나온다.

데이터가 있는 2006-01-06, 2006-01-09 같은 경우에는 lp=1 데이터에 동일한 날짜가 매칭되어있지만 2006-01-07, 2006-01-08 의 경우에는 lp=1 데이터에 2006-01-06 날짜가 매칭되어있다.

 

 

 

 

 

---

 

CTE와 ROW_NUMBER() 함수 활용 SQL로 누락값 처리를 구현한 케이스를 소개 해봤다.

 

다음 포스트에서 정리할 순서는 Finding Employees with the Highest Salary인데 그냥 ORDER BY에 LIMIT만 하면 될걸 이상하게 설명하는 것 같아서 넘어가고 UNPIVOT과 PIVOT을 정리한다.