[SQL] 시간대별 가장 최근 데이터 조회 쿼리

MariaDB를 기준으로 작성되었습니다.

---

내가 원한 데이터는 다음과 같다. 

11	서비스 1	데이터 1
11	서비스 2	데이터 2
11	서비스 3	데이터 3
12	서비스 1	데이터 1
12	서비스 2	데이터 2
12	서비스 3	데이터 3

 

이렇게 시간대별로 서비스의 데이터를 조회하고 싶었다. 해당 테이블은 log성으로 5분마다 데이터가 적재된다.

그러니까 각 시간대별 가장 최근 데이터를 뽑아야 한다. 

 

그래서 쿼리는 다음과 같다.

SELECT TIME, SERVICE_NAME, IFNULL(CPU_USAGE,0) AS CPU_USAGE, IFNULL(MEMORY_TOTAL,0) AS MEMORY_TOTAL, IFNULL(MEMORY_USAGE,0) AS MEMORY_USAGE
FROM (
  SELECT @N := @N + 1 AS TIME
  FROM 테이블명, (SELECT @N:=0 FROM DUAL) NN -- 1부터 24까지의 값이 있는 컬럼 생성
  LIMIT 24
) AS T
LEFT JOIN (
        SELECT  *
        FROM 테이블명
        WHERE CREATE_DATE IN (
                SELECT MAX(CREATE_DATE) ## 가장 최근 적재된 데이터 조회
                FROM 테이블명
                WHERE SUBSTR(CREATE_DATE,1,6) = DATE_FORMAT(NOW(),'%y%m%d') ## 오늘 날짜만 조회
                GROUP BY SUBSTR(CREATE_DATE, 1, 8), SERVICE_NAME ## 시간 + 서비스명으로 grouping
        )
) AS RESULT ON T.TIME = SUBSTR(CREATE_DATE, 7, 2)
ORDER BY T.TIME, LOG_NO;

 

 

하나씩 분석하자면 다음과 같다. 

SELECT MAX(CREATE_DATE) ## 가장 최근 적재된 데이터 조회
FROM 테이블명
WHERE SUBSTR(CREATE_DATE,1,6) = DATE_FORMAT(NOW(),'%y%m%d') ## 오늘 날짜만 조회
GROUP BY SUBSTR(CREATE_DATE, 1, 8), SERVICE_NAME ## 시간 + 서비스명으로 grouping

 

먼저 오늘 날짜 데이터만 조회한다. 그다음으로 연월일시 + 서비스명을 기준으로 그룹핑한다.

그럼 다음과 같은 형식으로 데이터가 출력된다. 예를 들어 초록색 데이터가 12시 00분에 적재된 데이터면, 보라색은 12시 5분에 적재된 데이터이다. 

서비스 1	데이터 1
서비스 2	데이터 2
서비스 3	데이터 3
서비스 1	데이터 1
서비스 2	데이터 2
서비스 3	데이터 3

 

그래서 MAX(CREATE_DATE)를 조회하여 최신 데이터를 뽑아왔다.

서비스 1	데이터 1
서비스 2	데이터 2
서비스 3	데이터 3

 

실행되는 쿼리는 다음과 같다.

SELECT  *
FROM 테이블명
WHERE CREATE_DATE IN ([위 결과])

 

CREATE_DATE 중 위 결과를 포함하는 데이터만 출력한다.

---

 

1부터 24까지 값이 있는 칼럼을 만든다. IFNULL처리는 해줘도 되고 안 해줘도 된다. 

내가 한 이유는 다음과 같다.

 

NamedNativeQuery 의 결괏값을 result에 맵핑해줘야 하는데 이때 IFNULL처리가 되어있지 않으면 조회된 데이터가 NULL일 경우

@ColumnResult의 타입을 String.class로 선언해야한다. (= vo도 String으로 선언) 그래서 데이터 타입을 맞추기 위해 IFNULL처리를 하였다. 

 

쉽게 말하면 NamedNativeQuery에서 NULL인 데이터는 String 타입으로 적용된다. 그래서 CPU_USAGE와 같은 INT형 데이터를  String으로 처리해야 한다. DB 데이터 타입은 INT인데 굳이 자바단에서 String으로 변환할 필요는 없을 거 같았다. 그래서 NULL일 경우 명시적으로 0으로 바꿔줬다. 사실 해당 테이블은 로그 형식으로 null인 값이 들어가지는 않겠지만, 그래도 처리해주고 싶었다.

 

SELECT TIME, SERVICE_NAME, IFNULL(CPU_USAGE,0) AS CPU_USAGE, IFNULL(MEMORY_TOTAL,0) AS MEMORY_TOTAL, IFNULL(MEMORY_USAGE,0) AS MEMORY_USAGE
FROM (
  SELECT @N := @N + 1 AS TIME
  FROM 테이블명, (SELECT @N:=0 FROM DUAL) NN -- 1부터 24까지의 값이 있는 컬럼 생성
  LIMIT 24
) AS T
LEFT JOIN (
		[조회문]
) AS RESULT ON T.TIME = SUBSTR(CREATE_DATE, 7, 2)
ORDER BY T.TIME, LOG_NO;

 

여기까지 했다면, 조회되는 데이터는 다음과 같다. 

서비스의 시간대별 가장 최근 데이터가 보임! 

11	서비스 1	데이터 1
11	서비스 2	데이터 2
11	서비스 3	데이터 3
12	서비스 1	데이터 1
12	서비스 2	데이터 2
12	서비스 3	데이터 3

 

---

 

오랜만에 쿼리 짜서 재미있었다. 하지만 이게 과연 최적의 코드인가? 에 대한 의문은 늘 따라다니는 거 같다.

나에게는 최선의 코드였기 때문에 조금이나마 도움이 되는 분들이 있었으면 좋겠다. 

 

코드 리뷰나 부족한 설명에 대한 댓글은 언제나 환영입니다.