[SQL/Clickhouse] 시계열 데이터 지수index로 변환하기

오늘은 물가지수, 주가지수와 같은 지수로 변환하는 법이다.

아이디어는 간단하다. 시계열 순으로 가장 최초의 값을 가져오고, 그 값을 모든 행에 나눠서 지수로 표현하는 방법이다.

거래액 등 단위 자체가 다른 2개의 지표에 대해서, 시작점을 100으로 놓고 어떤게 더 성장했는지 비교할 때 유용하다. 

As-is → To-be

예를 들면, 통계청에서 발표한 이커머스 거래 현황과 작고 소중한 우리 회사 거래액을 비교한다고 가정해보자. 시장지배력이 높은 기업이라도 아마 단위 자체가 다를 가능성이 높다. 이럴 때 시장의 성장에 비해 우리의 성장이 잘 이뤄지고 있는지 등을 볼 때, 지수로 변환해서 확인하면 유용하다.

 

우선 코드는 아래와 같다.

SELECT stat_date, 
round(company_gmv/sum(company_gmv_r1)over(order by r)*100,0) as company_index, 
round(market_gmv/sum(market_gmv_r1)over(order by r)*100,0) as market_index
FROM(
    SELECT
        row_number() OVER (ORDER BY stat_date) AS r,
        stat_date,
        company_gmv,
        (case when r=1 then company_gmv else null end) as company_gmv_r1,
        market_gmv,
        (case when r=1 then market_gmv else null end) as market_gmv_r1

    FROM table_03
    WHERE $timeFilter
    GROUP BY stat_date
    ORDER BY stat_date)
ORDER BY stat_date

 

우선 FROM절 안을 살펴보면 다음과 같다. 기준이 되는 날짜가 있고, 그 날짜의 시작점을 찾기 위해 row_number 기능을 사용한다. 그런 다음 case when을 활용하면 가장 최초 날짜에 해당하는 수치를 가져온다. company_gmv_r1은 기간 중 최초 날짜의 회사 거래액, market_gmv_r1은 기간 중 최초 날짜의 시장 거래액이다.

 

그 다음에 sum over 기능을 활용해준다. 이렇게 설정하면 모든 company_gmv_r1과 market_gmv_r1의 모든 row에 최초값이 적용된다. 왜냐하면 최초값을 제외한 모든 row는 값이 없기 때문이다. 그 다음 각각의 값을 나눠주면 산정 날짜 중 최초 수치가 1이 되도록 설정할 수 있다.

 

간단한 기능인데 쓰임은 꽤 많은 것 같아서 블로그에 투척.