[MYSQL] 5분간격의 데이터를 id와 timetable로 구분짓기

데이터베이스 커뮤니티에 가장 핫한 문제인 연속되는 날짜를 하나의 그룹으로 표현해보자.

라는 문제가 있다.

 

예를들면

 

철수가 수학 시험공부를 하는데

1일~ 4일까지 1단원을 공부했다.

7일~12일까지 2단원을 공부했다.

13일~17일까지 3단원을 공부했다. 

이를 sql문으로 나타내라. 

 

CREATE TABLE T

AS

  SELECT 1 no, '20090101' dt FROM dual

  UNION ALL SELECT 1, '20090102' FROM dual

  UNION ALL SELECT 1, '20090103' FROM dual

  UNION ALL SELECT 1, '20090104' FROM dual

 

  UNION ALL SELECT 2, '20090107' FROM dual

  UNION ALL SELECT 2, '20090108' FROM dual

  UNION ALL SELECT 2, '20090109' FROM dual

  UNION ALL SELECT 2, '20090110' FROM dual

  UNION ALL SELECT 2, '20090111' FROM dual

  UNION ALL SELECT 2, '20090112' FROM dual

 

  UNION ALL SELECT 3, '20090113' FROM dual

  UNION ALL SELECT 3, '20090114' FROM dual

  UNION ALL SELECT 3, '20090115' FROM dual

  UNION ALL SELECT 3, '20090116' FROM dual

  UNION ALL SELECT 3, '20090117' FROM dual

;

 

 

라는 데이터가 있는데

다음과 같이 생겼다.

이 표를 다음과 같이 나타내는 법을 구하시오.

다음이 MSSQL의 문법이다.

SELECT no
     , MIN(dt)  from_dt
     , MAX(dt)  to_dt
     , COUNT(*) cnt
  FROM (SELECT no, dt
             , CAST(dt AS DATE) dt1
             , ROW_NUMBER() OVER(ORDER BY no, dt) rn
          FROM T
        ) a
 GROUP BY no, DATEADD(day, -rn, dt1)
 ORDER BY no, from_dt
;

여기서

 

DATEADD(day,-rn, dt1)

이부분을 @rownum:=@rownum+1로 rn을 먼저 서브쿼리로 정의하고,

dt1을 CAST 하는부분을 

 

 

 

 

로 구분짓는 sql문을 풀어보고 그 응용으로 회사 대쉬보드에 연월일 / 시분초 까지 

적용을 해서 sql문을 작성했다.

 

 

      SET @rownum:=0;
      
        SELECT sq.vmName,
                sq.rn,
                DAY((sq.timestamp)) AS dr,
                HOUR((sq.timestamp))AS hr,
                MINUTE((sq.timestamp)) AS mr,
                min(sq.timestamp),
                MAX(sq.timestamp),
                COUNT(*) ctn

        FROM

        (
              select 
                  d.vm_name vmName,
                  @rownum:=@rownum+5 rn,
                  p.cpu,
                  p.memory,
                  p.disk,
                  p.network,
                  p.timestamp
              from perfVMDay p

                  join vm_data_info d on d.vm_ID = p.id
                  join vm_host_info h on d.vm_host = h.vm_Hhostname
                  join clusterInfo c on h.hostParent = c.clusterName
                  and p.timestamp >= date_add(now(), interval -1 day) 
                  and (p.memory > 30 OR p.cpu> 30)
		) AS sq
			
			GROUP BY sq.vmName,dr*60*24+60*hr +mr-rn
			ORDER BY sq.vmName,min(sq.timestamp)