제목을 뭐라고 써야 좋을까 😂
날짜의 시간별 데이터를 가져와 그리드를 만들때
시간컬럼이 필요한데 이 시간컬럼의 데이터를 만드는 방법이다
원래 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을 응용하면 다양한 시간 더미데이터를 만들 수 있을 것이다
나도 익숙해질때까지는 자주자주 게시글을 복습하러 와야겠다