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 3906 게시물 읽기
No. 3906
매분마다의 평균을 구하려고 합니다.
작성자
곽성택
작성일
2007-12-04 14:56
조회수
3,582

데이터가 1초마다 한번씩 인서트 되는게 있습니다.


하루치 데이터를 가져와서 그래프를 그려줘야 되는데...적당한 시간간격으로 평균을 내려 합니다..


제가 원하는건 5분마다의 평균인데요...어떻게 해야 될지 모르겠씁니다..아래는 테이블 구조입니다.


issue_date var1 var2 var3
2007-11-19 12:12:13 18.1 2.0 45.7
2007-11-19 12:12:14 18.3 1.9 45.6
2007-11-19 12:12:15 18.2 2.1 45.8
2007-11-19 12:12:16 18.3 2.0 45.7


미리 감사드립니다.

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

1. 분으로 GROUP BY를 할 수 있게 CASE문 사용하여 기준값  지정 또는 날짜 지정
2. 위 지정값으로 GROUP BY

SELECT issue_date
   , SUM(var1) / COUNT(Var1)
   , SUM(var2) / COUNT(Var2)
   , SUM(var3) / COUNT(Var3)
FROM (
   SELECT
      /*
      grouptarget = case
            when datepart(mi,issue_date) >= 0 AND datepart(mi,issue_date) <= 5 then 1
            when datepart(mi,issue_date) >= 6 AND datepart(mi,issue_date) <= 10 then 2
            when datepart(mi,issue_date) >= 11 AND datepart(mi,issue_date) <= 15 then 3
            when datepart(mi,issue_date) >= 16 AND datepart(mi,issue_date) <= 20 then 4
            when datepart(mi,issue_date) >= 21 AND datepart(mi,issue_date) <= 25 then 5
            when datepart(mi,issue_date) >= 26 AND datepart(mi,issue_date) <= 30 then 6
            when datepart(mi,issue_date) >= 31 AND datepart(mi,issue_date) <= 35 then 7
            when datepart(mi,issue_date) >= 36 AND datepart(mi,issue_date) <= 40 then 8
            when datepart(mi,issue_date) >= 41 AND datepart(mi,issue_date) <= 45 then 9
            when datepart(mi,issue_date) >= 46 AND datepart(mi,issue_date) <= 50 then 10
            when datepart(mi,issue_date) >= 51 AND datepart(mi,issue_date) <= 55 then 11
            when datepart(mi,issue_date) >= 56 AND datepart(mi,issue_date) <= 60 then 12
           end
      */
      issue_date = case
            when datepart(mi,issue_date) >= 0 AND datepart(mi,issue_date) <= 5 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':00:00')
            when datepart(mi,issue_date) >= 6 AND datepart(mi,issue_date) <= 10 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':06:00')
            when datepart(mi,issue_date) >= 11 AND datepart(mi,issue_date) <= 15 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':11:00')
            when datepart(mi,issue_date) >= 16 AND datepart(mi,issue_date) <= 20 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':16:00')
            when datepart(mi,issue_date) >= 21 AND datepart(mi,issue_date) <= 25 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':21:00')
            when datepart(mi,issue_date) >= 26 AND datepart(mi,issue_date) <= 30 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':26:00')
            when datepart(mi,issue_date) >= 31 AND datepart(mi,issue_date) <= 35 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':31:00')
            when datepart(mi,issue_date) >= 36 AND datepart(mi,issue_date) <= 40 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':36:00')
            when datepart(mi,issue_date) >= 41 AND datepart(mi,issue_date) <= 45 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':41:00')
            when datepart(mi,issue_date) >= 46 AND datepart(mi,issue_date) <= 50 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':46:00')
            when datepart(mi,issue_date) >= 51 AND datepart(mi,issue_date) <= 55 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':51:00')
            when datepart(mi,issue_date) >= 56 AND datepart(mi,issue_date) <= 59 then CONVERT(DATETIME, CONVERT(CHAR(10), issue_date, 121) + ' ' + CONVERT(CHAR(2), DATEPART(hh, issue_date)) + ':56:00')
           end
     , var1
     , var2
     , var3
   FROM DBO.TEST
) AS A
GROUP BY issue_date

무식한 방법이죠 ^^;; 이것은 분으로 그룹핑을 한것이고, 날짜별로도 필요하다면 여기서 한번더 그룹핑을 하시면 될듯합니다.

건방진연이(gamebible)님이 2007-12-05 11:29에 작성한 댓글입니다.

 

IF OBJECT_ID('TESTTB') IS NOT NULL

       DROP TABLE TESTTB

 

CREATE TABLE TESTTB

       ( IDX INT IDENTITY(1,1)

       , ISSUE_DATE DATETIME

       , VAR1 NUMERIC(5, 2)

       , VAR2 NUMERIC(5, 2)

       , VAR3 NUMERIC(5, 2))

GO

 

INSERT INTO TESTTB

       SELECT '2007-11-19 12:12:13', 18.1,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:13:14', 18.3,     1.9,   45.6  UNION ALL

       SELECT '2007-11-19 12:14:15', 18.2,     2.1,   45.8  UNION ALL

       SELECT '2007-11-19 12:15:16', 18.3,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:16:13', 18.1,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:17:14', 18.3,     1.9,   45.6  UNION ALL

       SELECT '2007-11-19 12:18:15', 18.2,     2.1,   45.8  UNION ALL

       SELECT '2007-11-19 12:19:16', 18.3,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:20:13', 18.1,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:21:14', 18.3,     1.9,   45.6  UNION ALL

       SELECT '2007-11-19 12:22:15', 18.2,     2.1,   45.8  UNION ALL

       SELECT '2007-11-19 12:23:16', 18.3,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:30:13', 18.1,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:50:14', 18.3,     1.9,   45.6  UNION ALL

       SELECT '2007-11-19 12:55:15', 18.2,     2.1,   45.8  UNION ALL

       SELECT '2007-11-19 12:56:16', 18.3,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:24:13', 18.1,     2      ,  45.7  UNION ALL

       SELECT '2007-11-19 12:36:14', 18.3,     1.9,   45.6  UNION ALL

       SELECT '2007-11-19 12:42:15', 18.2,     2.1,   45.8 

GO    

 

SELECT

       A.ISSUE_DATE

       , B.AVG_VAR1

       , B.AVG_VAR2

       , B.AVG_VAR3

FROM

       TESTTB A

       INNER JOIN (

                           SELECT

                                   IDX = MIN(IDX)

                                 , AVG_VAR1 = AVG(VAR1)

                                 , AVG_VAR2 = AVG(VAR2)

                                 , AVG_VAR3 = AVG(VAR3)

                           FROM

                                 TESTTB

                           GROUP BY DATEPART(MI, ISSUE_DATE) /5

       ) B

       ON A.IDX = B.IDX

GO    

.님이 2007-12-07 16:33에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
3909Select 한 결과값을 파일로 저장하고 싶습니다! [5]
최자영
2007-12-06
5980
3908sql2005 출력질문입니다. [1]
손님
2007-12-05
3079
3907distinct 관련하여 질문 좀 드리겠습니다. [1]
ss
2007-12-04
3539
3906매분마다의 평균을 구하려고 합니다. [2]
곽성택
2007-12-04
3582
3905엑셀에서 sql서버로 커넥션 방법 질문 입니다... [1]
질문
2007-12-04
3071
3904[SQL->Oracle]trigger로 table 동기화
박신정
2007-12-03
3574
3903현재 월의 주 구하기 [2]
건방진연이
2007-12-03
4038
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다