database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 7131 게시물 읽기
No. 7131
30분 단위로 평균을 볼려고 합니다
작성자
정영배
작성일
2018-08-03 17:33ⓒ
2018-08-03 17:39ⓜ
조회수
2,258

안녕하세요

cpu 사용률을 30분단위로 평균값을 보고 싶은데 어떻게 할까 고민중입니다.

15분에 한건씩 insert됩니다.

 

table cpu_info

pk HW_ID varchar(20)

pk YMD char(8)

pk HMS char(6)

CPU_USAGE int

번호 일자 시분초 cpu사용률

000000001 20180803 001000 30

000000001 20180803 002500 45

000000001 20180803 004000 15

000000001 20180803 004000 23

000000001 20180803 005500 20

000000001 20180803 001000 30

.

.

.

.

 

SELECT SUM(CASE WHEN HMS BETWEEN '000001' AND '003000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '000001' AND '003000' THEN 1 ELSE 0 END) AS AVG_0030

, SUM(CASE WHEN HMS BETWEEN '003001' AND '010000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '003001' AND '010000' THEN 1 ELSE 0 END) AS AVG_0100

, SUM(CASE WHEN HMS BETWEEN '010001' AND '013000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '010001' AND '013000' THEN 1 ELSE 0 END) AS AVG_0130

, SUM(CASE WHEN HMS BETWEEN '010001' AND '013000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '010001' AND '013000' THEN 1 ELSE 0 END) AS AVG_0200

, SUM(CASE WHEN HMS BETWEEN '020001' AND '023000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '020001' AND '023000' THEN 1 ELSE 0 END) AS AVG_0230

, SUM(CASE WHEN HMS BETWEEN '023001' AND '030000' THEN CPU_USAGE ELSE 0 END) / SUM(CASE WHEN HMS BETWEEN '023001' AND '030000' THEN 1 ELSE 0 END) AS AVG_0300

.

.

.

FROM CPU_INFO

WHERE HW_ID = '000000001'

AND YMD = '20180803'

 

제가 생각한것인데 더 좋은 방법이 있을듯해서 문의합니다.

 

DB는 MS-SQL이면 TABLE HW_ID 번호하나당 하루 96건이 생기며 HW_ID는 몇만개가 정도 있어서 테이블안에 데이타량은 많습니다.

 

읽어주셔서 감사합니다.

이 글에 대한 댓글이 총 2건 있습니다.

-- 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 )
)
;
GO
-- 테스트 데이터 입력
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
)
;
GO
-- 해당 테이블에 48조각으로 나뉜 하루 입력
WITH nums ( num )
AS
(
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
AS
(
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에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
7136쿼리 순서 정렬 2번재 질문 [1]
김지철
2018-08-30
2082
7134쿼리문의 [2]
초보님
2018-08-16
2088
7132비슷한 테이블 두개의 리스트 나열 가능여부 [2]
김지철
2018-08-13
2071
713130분 단위로 평균을 볼려고 합니다 [2]
정영배
2018-08-03
2258
7129mssql 로그인 실패 횟수에 따라 잠금 설정 방법 좀 알려주세요
최키럽
2018-07-05
2276
7128MSSQL 로그인 권한 관련 질문입니다.
권한
2018-07-04
1993
7127MSSQL VS MARIA [1]
DBA
2018-07-02
2150
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다