데이터베이스/MySQL MariaDB

[MySQL] 1분 간격 시간 더미데이터 만들기 (Oracle LEVEL 같은)

지이구 2021. 7. 9. 09:14

제목을 뭐라고 써야 좋을까 😂

​

날짜의 시간별 데이터를 가져와 그리드를 만들때

시간컬럼이 필요한데 이 시간컬럼의 데이터를 만드는 방법이다

​

원래 Oracle을 주로 사용했어서 LEVEL 같은 문법을 찾고있었는데

재귀함수를 사용하면 된다는 것을 보고 바로 테스트!

​

​

예를 들어서 VALUETAB 테이블의 데이터를 가져와 수치 통계표를 만든다고 생각해보자

시간(YMDHM) 수치(VALUE)
2021-07-08 16:00 5
2021-07-08 16:01 6
2021-07-08 16:02 7
2021-07-08 16:03 10
2021-07-08 16:04 11

​

물론 데이터에 시간과 수치가 전부 들어가있으면

 

SELECT YMDHM, VALUE FROM VALUETAB WHERE YMDHM >= '202107081600' AND YMDHM <= '202107081604'

 

 

이렇게 쉽게 데이터를 불러와 그리드를 만들 수 있지만

중간중간 데이터 누락이 있다면?

​

만약 16시 2분에 수치데이터가 안들어왔다고 생각해보자

시간(YMDHM) 수치(VALUE)
2021-07-08 16:00 5
2021-07-08 16:01 6
2021-07-08 16:03 10
2021-07-08 16:04 11

​

그렇다면 이렇게 빵꾸가 난 테이블이 되버린다

자세히 보지 않는다면 어느 시각에 누락이 있었는지 확인할 수 없게된다

그래서 미리 시간 더미데이터를 만들어놓고 JOIN을 해서 아래와 같은 테이블을 만드는 것이다

​

시간(YMDHM) 수치(VALUE)
2021-07-08 16:00 5
2021-07-08 16:01 6
2021-07-08 16:02 -
2021-07-08 16:03 10
2021-07-08 16:04 11

​

자 그럼 내용의 핵심인 시간 더미 데이터를 만들어보자

1분 간격 데이터를 만들건데 방법만 응용하면 5분, 30분, 1시간, 3시간 간격 등등 다양하게 사용할 수 있다

​

​

원리는 WITH RECURSIVE를 사용해서 재귀함수처럼 증가값을 주어 더미데이터를 만드는것이다

 

WITH RECURSIVE A AS
(
	SELECT STR_TO_DATE('202107081600', '%Y%m%d%H%i') AS YMDHM
	UNION ALL
	SELECT DATE_ADD(A.YMDHM, INTERVAL 1 MINUTE) AS YMDHM FROM A
    WHERE A.YMDHM < STR_TO_DATE('202107081604', '%Y%m%d%H%i')
)
SELECT DATE_FORMAT(YMDHM, '%Y-%m-%d %H:%i') AS YMDHM FROM A

 

 

한 줄씩 보자

​

1. SELECT STR_TO_DATE('202107070306', '%Y%m%d%H%i') AS YMDHM

String으로 되어있는 날짜를 원하는 형식으로 변경해주고 YMDHM 을 SELECT 한다

YMDHM
2021-07-08 16:00

​

2. UNION ALL

SELECT DATE_ADD(A.YMDHM, INTERVAL 1 MINUTE) AS YMDHM FROM A

위에 만든 테이블과 합치는데 방금 만든 YMDHM에서 1분을 증가한 데이터를 만들고 넣는다

YMDHM
2021-07-08 16:00
2021-07-08 16:01

​

3. WHERE A.YMDHM < STR_TO_DATE('202107081604', '%Y%m%d%H%i')

위의 SELECT문의 WHERE절이다 어디까지 증가를 시킬 것인지 조건을 걸어주면 된다

주의할 점은 1이 증가되어서 나오기 때문에 <= 가아닌 < 로 주던지 202107081605로 주던지 해야한다

​

​

4. WITH RECURSIVE A AS

방금 했던 것을 WHERE절에 해당될때까지 재귀한다(반복한다)

​

 

5. SELECT DATE_FORMAT(YMDHM, '%Y-%m-%d %H:%i') AS YMDHM FROM A

그리고 원하는 형식대로 YMDHM을 SELECT 해주면 원하는 더미데이터가 나온다

시간(YMDHM)
2021-07-08 16:00
2021-07-08 16:01
2021-07-08 16:02
2021-07-08 16:03
2021-07-08 16:04

​

​

이렇게 INTERVAL을 응용하면 다양한 시간 더미데이터를 만들 수 있을 것이다

​

​

 

 

나도 익숙해질때까지는 자주자주 게시글을 복습하러 와야겠다

728x90
반응형