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
운영게시판
최근게시물
MySQL Q&A 30892 게시물 읽기
No. 30892
[문의] 5분 단위로 group 하는 방법
작성자
최현태(gusxo79)
작성일
2015-11-04 13:33
조회수
7,591

현재 데이터가 아래와 같이 1분 단위로 저장됩니다.

 

 

DEVICE_ADDRESS DATA_DT TEMPERATURE

DF:7F:78:CB:45:C2 2015-11-02 15:00:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:01:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:02:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:03:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:04:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:05:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:06:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:07:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:08:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:09:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:10:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:11:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:12:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:13:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:14:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:15:57 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:16:57 23

DF:7F:78:CB:45:C2 2015-11-02 15:17:57 23

DF:7F:78:CB:45:C2 2015-11-02 15:18:57 23

DF:7F:78:CB:45:C2 2015-11-02 15:19:57 23

DF:7F:78:CB:45:C2 2015-11-02 15:20:57 23.1

 

해당 값을 5분 단위로 평균을 내고 싶은데, 구간별로 나눠서

union으로 묶는거 외에는 방법이 없을까요..?

 

아래는 원하는 모습

 

DEVICE_ADDRESS DATA_DT avg(TEMPERATURE)

DF:7F:78:CB:45:C2 2015-11-02 15:00:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:05:56 23.7

DF:7F:78:CB:45:C2 2015-11-02 15:10:57 23.7

 

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

MySQL 은 사용해본적이 없어서 -_-;;;

MSSQL 쿼리로 작성되었습니다.

 

mysql 에 맞게 수정하셔서 쓰시면 될듯요.

참고만 하세요..

 

---쿼리시작---


with tblA (DEVICE_ADDRESS,DATA_DT,TEMPERATURE) As
(
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:00:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:01:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:02:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:03:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:04:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:05:56',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:06:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:07:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:08:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:09:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:10:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:11:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:12:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:13:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:14:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:15:57',23.7     Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:16:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:17:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:18:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:19:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:50:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 18:50:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-07 18:50:57',23       Union All
       Select 'DF:7F:78:CB:45:C2','2015-11-02 15:20:57',23.1
)
               Select a.DEVICE_ADDRESS    As DEVICE_ADDRESS
                     ,a.DT + ' ' + Min(DT_TIME)   As DATA_DT
                     ,  Sum(a.TEMPERATURE)
                      / Count (a.TEMPERATURE)   As TEMPERATURE
                 From
                     (
                         Select a.DEVICE_ADDRESS    As DEVICE_ADDRESS
                               ,a.DT                As DT
                               ,Left(Convert(varchar(max),a.DT_MIN / 5),1)    As MIN_SEQ
                               ,a.DT_MIN
                               ,a.DT_HOUR           As DT_HOUR
                               ,a.DT_TIME           As DT_TIME
                               ,a.TEMPERATURE       As TEMPERATURE
                           From (
                                      Select a.DEVICE_ADDRESS            As DEVICE_ADDRESS
                                            ,SubString(a.DATA_DT,1,10)   As DT
                                            ,SubString(a.DATA_DT,12,2)   AS DT_HOUR
                                            ,Convert(Decimal(18,0),SubString(a.DATA_DT,15,2),0)  As DT_MIN
                                            ,SubString(a.DATA_DT,12,8)   AS DT_TIME
                                            ,a.TEMPERATURE               As TEMPERATURE
                                        From tblA a
                                ) a
                     ) a
             Group By a.DEVICE_ADDRESS
                     ,a.DT
                     ,a.DT_HOUR + a.MIN_SEQ
                     --,a.MIN_SEQ
---쿼리끝----                    

 

---결과---

DF:7F:78:CB:45:C2 2015-11-02 15:00:56 23.700000
DF:7F:78:CB:45:C2 2015-11-02 15:05:56 23.583333
DF:7F:78:CB:45:C2 2015-11-02 15:10:57 23.700000
DF:7F:78:CB:45:C2 2015-11-02 15:15:57 23.140000
DF:7F:78:CB:45:C2 2015-11-02 15:20:57 23.100000
DF:7F:78:CB:45:C2 2015-11-02 18:50:57 23.000000
DF:7F:78:CB:45:C2 2015-11-07 18:50:57 23.000000

최한영(terry0515)님이 2015-11-04 16:57에 작성한 댓글입니다.
이 댓글은 2015-11-04 16:58에 마지막으로 수정되었습니다.

SELECT device_address
     , MIN(data_dt) data_dt
     , AVG(temperature) temperature
  FROM tblA
 GROUP BY device_address
     , DATE_FORMAT(data_dt, '%Y-%m-%d %H')
     , FLOOR(DATE_FORMAT(data_dt, '%i') / 5)
;

마농(manon94)님이 2015-11-04 17:17에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
30895select 절의 한 컬럼의 값이 256자리를 초과 할수 없나요?
김태경
2015-11-09
5850
30894user 권한설정 [3]
김양훈
2015-11-09
5700
30893접속 로그 테이블인데, 일별 통계를 못내겠어요.. [1]
질문
2015-11-05
5762
30892[문의] 5분 단위로 group 하는 방법 [2]
최현태
2015-11-04
7591
30888show table status
래마리오
2015-10-23
5993
30887방문 통계에서 빠진 날짜를 채우는 최적의 방법이 있나요? [1]
김종수
2015-10-22
6111
30886mysql select 이후 저장하기 질문드립니다.
무사 무휼
2015-10-21
6367
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.068초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다