데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

-- pk가 clustered index로 동일하다는 전제하에 아래 쿼리도 속도상 큰 이점은 없음. 다만 쿼리가 너무 지저분해서(유지보수 불리)... ㅋㅋ

-- YMD와 HMS를 DATETIME 형식으로 변경 <- byte수 감소(14 ->8) & 연산 편리

-- 테이블 생성

CREATE table cpu_info(

HW_ID varchar(20),

DT DATETIME,

CPU_USAGE INT,

PRIMARY KEY ( HW_ID, DT )

)

;

-- 테스트 데이터 입력

INSERT INTO cpu_info( HW_ID, DT, CPU_USAGE )

VALUES( '0001', '2018-08-04T00:10:00', 30 )

, ( '0001', '2018-08-04T00:25:00', 45 )

, ( '0001', '2018-08-04T00:40:00', 15 )

, ( '0001', '2018-08-04T00:55:00', 20 )

, ( '0001', '2018-08-04T01:10:00', 30 )

, ( '0001', '2018-08-05T00:25:00', 1 )

, ( '0001', '2018-08-05T00:40:00', 2 )

, ( '0001', '2018-08-05T00:55:00', 1 )

, ( '0001', '2018-08-05T01:10:00', 2 )

;

-- 24시간을 30분 단위로 잘라서 반복 사용을 위한 테이블 생성 / 더 유연하게 하려면 아예 SELECT시 WITH로 처리해서 50분 단위로 평균구하기 등을 사용해도 무방

CREATE TABLE timeInterval(

bgnMinute INT

, endMinute INT

)

;

-- 해당 테이블에 48조각으로 나뉜 하루 입력

WITH nums ( num )

(

SELECT 0

UNION ALL SELECT num + 1 FROM nums WHERE num + 1 < 48

)

INSERT INTO timeInterval( bgnMinute, endMinute )

SELECT (num)*30, (num+1)*30

FROM nums

;

-- '2018-08-04'일에 해당하는 평균값 SELECT

DECLARE @date DATETIME

;

SET @date = '2018-08-04'

;

WITH ti( bgnTime, endTime ) -- number and time interval

(

SELECT DATEADD( MINUTE, bgnMinute, @date ), DATEADD( MINUTE, endMinute, @date )

FROM timeInterval

)

SELECT ti.bgnTime, REPLACE( CONVERT( CHAR(8), ti.bgnTime, 114 ), ':', '' ) AS 굳이_이_형식을_원한다면, CAST( SUM( CPU_USAGE ) AS REAL ) / COUNT(*) AS 평균 --, SUM( CPU_USAGE ) , COUNT(*)

FROM cpu_info AS ci

INNER JOIN ti

ON ti.bgnTime < ci.DT AND ci.DT <= ti.endTime

WHERE HW_ID = '0001'

GROUP BY ti.bgnTime

;

우욱님이 2018-08-04 13:35에 작성한 댓글입니다.
이 댓글은 2018-08-04 13:38에 마지막으로 수정되었습니다. Edit Ｘ

나누는 기준이 모호합니다.
000001 ~ 003000 이 첫번째 단위인데
이러면 000000 이 누락이 되며
맨 마지막 단위는 233001 ~ 235959 가 됩니다.
앞의 단위(30분)와 마지막 단위(2분59초)가 기준이 다르네요.
차라리 000000 ~ 002959 로 나누는 것이 바람직해 보이네요.

WITH cpu_info(hw_id, ymd, hms, cpu_usage) AS
(
          SELECT '000000001', '20180803', '001000', 30
UNION ALL SELECT '000000001', '20180803', '002500', 45
UNION ALL SELECT '000000001', '20180803', '004000', 15
UNION ALL SELECT '000000001', '20180803', '004000', 23
UNION ALL SELECT '000000001', '20180803', '005500', 20
UNION ALL SELECT '000000001', '20180803', '001000', 30
)
SELECT *
FROM (SELECT hw_id, ymd, cpu_usage
             , ( SUBSTRING(hms, 1, 2) *60*60
               + SUBSTRING(hms, 3, 2) *60
               + SUBSTRING(hms, 5, 2)
               ) / (30*60) + 1 gb
          FROM cpu_info
         WHERE hw_id = '000000001'
           AND ymd   = '20180803'
        ) a
PIVOT (AVG(cpu_usage)
        FOR gb IN
             ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
             , [11], [12], [13], [14], [15], [16], [17], [18], [19], [20]
             , [21], [22], [23], [24], [25], [26], [27], [28], [29], [30]
             , [31], [32], [33], [34], [35], [36], [37], [38], [39], [40]
             , [41], [42], [43], [44], [45], [46], [47], [48]
             ) ) a
;

마농(manon94)님이 2018-08-06 13:31에 작성한 댓글입니다.