[Mysql,MariaDB] 재귀쿼리(With Recursive) 를 이용하여 전체 시간 구하기

돌발적으로 일어나는 이벤트를 하루기준으로 1시간마다 몇건씩 일어나는지 체크해야 하는 쿼리가 필요했습니다.

 

이러한 시계열 데이터를 구하기 위해서 전체시간이 필요하고 그 시계열 기준이 되는 시간과 이벤트 발생시간을 JOIN해야 합니다.

 

 

전체시간을 구하기 위해선 제가 아는 방법으론 두가지가 있습니다.

 

첫째 모든 시간값을 특정 date라는 이름의 테이블을 만들어 모든 시간값을 집어 넣는 것

 

둘째로는 반복쿼리 (재귀쿼리)를 이용하여 전체시간값의 가상 테이블을 만들어 JOIN을 걸어두는 것입니다.

 

저는 1시간 기준으로 오늘날짜를 기점으로 현재시간 까지의 시간값이 필요하여 다음과 같이 작성하였습니다.

 

 

	WITH
	RECURSIVE `CTE` AS(
		SELECT
		# 시작 기준이 되는 날짜
			DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AS `date`
		UNION ALL
		# 반복하는 기준
		SELECT
			DATE_ADD(`date`, INTERVAL 1 HOUR) AS `date`
		FROM
			`CTE`
        # 언제까지 반복할지 조건
		WHERE
			`date` < DATE_FORMAT(NOW(),'%Y-%m-%d %H')
	)SELECT * FROM CTE;

 

예시 쿼리로 작성하게 되면 (현재시각 5시)

 

다음과 같은 결과로 조회가 됩니다.