재귀 쿼리를 이용한 전년도 같은 주차 조회

여러 해 단위의 데이터가 있을 때 에 가끔 전년도의 데이터와 해당 년도의 데이터를 비교하기도 한다.

해당 쿼리는 MariaDB에서 지원하는 재귀를 이용하여 날짜를 생성하고 전년도에 같은 주차에 날짜를 JOIN하여 알 수 있도록 하였다.

 

재귀 쿼리란 무엇이길래 해당 방법을 사용했는지 해당 문법과 적용 논리를 무엇일까?

재귀 쿼리란? 해당 쿼리에서 자기 자신을 재 참조하는 쿼리이다.

어떤 방식으로 자기 자신을 자기 자신을 재 참조하는 것일까?

그것은 CTE(Common Table Expression)을 이용하는 방식이다.

CTE의 문법은 다음과 같다.

WITH <Table Name> AS

(

  <SELECT 쿼리문>

)SELECT * FROM <Table Name>

 

위와 같은 방식으로는 비 재귀적으로 사용하여 임시테이블과 같은 효과를 낼 수 있다.

재귀적인 문법 방식은 다음과 같다.

WITH RECURSIVE <Table Name> AS

(

  <SELECT 쿼리문>

  UNION ALL

  SELECT * FROM <Table Name>

)SELECT * FROM <Table Name>

 

위와 같이 CTE 구문 내에 자신을 재 참조함으로써 반복적으로 처리될 수 있다.

자신을 참조하는 SELECT 쿼리에는 반드시 해당 재귀가 종료되는 조건이 서술되어야 한다.

가장 많이 하는 방법은 첫 번째 SELECT 되는 데이터의 한계를 통해 조절할 수 있으며 해당 방법은 다음과 같은 방법으로 처리가 된다.

WITH RECURSIVE <Table Name> AS

(

  <SELECT 쿼리문>

  UNION ALL

  SELECT * FROM <SELECT 쿼리문의 테이블> JOIN <Table Name>

)SELECT * FROM <Table Name>

 

 

해당 구문을 이용하여 날짜를 생성하고 전년도의 같은 주차의 정보를 JOIN 하는 쿼리이다.

drop temporary table if exists tmp01;

 

SET @dtm_start = cast('2018-01-01' as date);

SET @dtm_end = date_add(date_add(@dtm_start, interval 3 year), interval -1 day);

 

create temporary table tmp01

with recursive tmp02 as (

  select @dtm_start as dtm

    , week(@dtm_start,1) as num_week

    , weekday(@dtm_start) as num_weekday

  union all

  select date_add(_before.dtm, interval 1 day) as dtm

    , week(date_add(_before.dtm, interval 1 day),1) as num_week

    , weekday(date_add(_before.dtm, interval 1 day)) as num_weekday 

  from tmp02 as _before 

  where _before.dtm < @dtm_end

)select dtm,num_week,num_weekday from tmp02;

 

select a.dtm, a.num_week, a.num_weekday, b.dtm as dtm_prev, b.num_week as num_week_prev, b.num_weekday as num_weekday_prev

from tmp01 as a

left outer join tmp01 as b

on b.dtm between date_add(a.dtm, interval -1 year) and date_add(date_add(a.dtm, interval -1 year), interval 6 day)

and a.num_weekday = b.num_weekday

order by a.dtm;

 

dtm_start ~ dtm_end 까지의 날짜를 생성하고 해당 데이터를 임시테이블에 넣어 데이터를 join해서 전년도 정보를 연결한다.

 

week 함수의 특성 상 0~53 숫자가 혼재하여 해당 주차가 0으로 반횐되는 값이 전년도의 53으로 반환되는 값의 연속성일 수 있기 때문에 week의 반환값이 아닌 날짜값으로 JOIN 한다.

 

join 시 between date를 6일로 가지는 이유는 주차에 해당 요일은 Unique하게 존재하여 join 되어야 하기 때문에 범위가 7개 이상되면 안된다.