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
운영게시판
최근게시물
PostgreSQL Q&A 10308 게시물 읽기
No. 10308
SQL 질문드립니다.
작성자
전성용(sdf4268)
작성일
2022-01-06 15:33
조회수
1,188

안녕하세요.

학생은 아니지만.. 궁금한데 DB에 대한 공부가 부족하여 글을 남기게 되었습니다.

환경은 CentOS7에서 구동중입니다.


아래는 테이블의 예시입니다.

SAVE_DATE DEVICE_ID    ... DATA -
2021-12-01 15:03:02 EXAM_ID    ... DATA -
2021-12-01 15:03:22 EXAM_ID    ... DATA -
2021-12-01 15:03:45 EXAM_ID    ... DATA -
2021-12-01 15:04:03 EXAM_ID    ... DATA -
2021-12-01 15:05:05 EXAM_ID    ... DATA -
2021-12-01 15:05:40 EXAM_ID    ... DATA -


위의 경우처럼 시간과 데이터가 있는 EXAMPLE_DATA 테이블이 있습니다.

데이터의 저장주기는 네트워크의 상황에 따라 20초가 될 수도, 30초가 될 수도, 혹은 10분이 될 수도 있습니다.


이때, 분단위로 평균을 뽑아낼 수 있는 SQL문이 있을까요?

3분대에 들어온 데이터들이 평균되어 3분 데이터로 셀렉되고

4분대에 들어온 데이터는 또 평균되어 4분대 데이터로 셀렉됩니다.

아래는 결과의 예시입니다.

SAVE_DATE DEVICE_ID    ... DATA -
2021-12-01 15:03:00 EXAM_ID    ... (3분대 데이터의 평균)
2021-12-01 15:04:00 EXAM_ID    ... (4분대 데이터의 평균)
2021-12-01 15:05:00 EXAM_ID    ... (5분대 데이터의 평균)


해당 작업을 하는 이유는 시간별 데이터를 비교할 때 X축을 동기화시켜 비교하기 위해서입니다.

제대로 설명을 드린건지 걱정이 되네요..

부디 능력자분들의 관심을 부탁드립니다.


감사합니다.


전성용 드림.

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

궁금해하시는 분야가 데이터베이스에서는 time series , 우리말 학술용어로는 시계열 이라고 하는 분야입니다. 

생각보다 복잡합니다.  (이 빠진 자료 보정은 어떻게 할지, 중간 집계 범위가 가변적일때는, 옛날 자료, 미래 자료가 어뚱한 시점에 입력될 때는 .... 등등 꽤 어려운 학문분야입니다.)


그렇다고 겁 먹을 필요는 없습니다. 우리에는 timescaledb 라는 아주 훌륭한 PostgreSQL 확장 모듈이 있으니까요. :)


https://docs.timescale.com/timescaledb/latest/getting-started/query-data/


참고하세요.

김상기(ioseph)님이 2022-01-06 15:52에 작성한 댓글입니다.

와... 감사드립니다..

이렇게 검색하니까 자료가 잔뜩나오네요!

정확한 답변과 또 빠른 답변 너무 감사드립니다!

새해 복 많이 받으시고 아무쪼록 코로나 조심하셔요!

감사합니다!


전성용 드림

전성용(sdf4268)님이 2022-01-06 16:02에 작성한 댓글입니다.

유입되는 데이터 패턴과 양에 따라 다를거 같습니다.


1. 대용량, 스트림 데이터 성격

 - 위에 상기님 말씀 처럼 time series 같은 걸 활용할 수 있을거 같습니다.

2. 소량, 특정 시점의 일괄 데이터

 - 몇건 되지 않고, 특정 시점에 10건, 100건, 1000건 이렇게 한꺼번에 들어 온다면

   db 에 사전에 통계나 그래프에 활용할 컬럼을 하나 더 만들고

   애플리케이션 레벨에서 db에 등록 전에 적절한 값을 구해 넣는걸 추천 드립니다.

  컬럼 한두개 더 추가 해도 용량에 크게 영향이 없고 

  실시간 조회가 필요한 경우나 복잡한 쿼리 없이도 처리 할 수 있는 장점이 있습니다.

  중복을 어디까지 허용할지는 업무 복잡도나 편의성에 따라 다를거 같습니다.

 

천정대(gt1000)님이 2022-01-06 19:09에 작성한 댓글입니다.
이 댓글은 2022-01-06 19:31에 마지막으로 수정되었습니다.

// 천정대

답변 감사드립니다!


데이터는 실시간으로 환경데이터를 수집하는 IoT기기에 의해 수집됩니다.

때문에 한번 수집할 때의 데이터 종류가 매우 다양하고

각 IoT 기기마다 수십만건 이상의 데이터가 있어서 말씀해주신 두번째 방법이 쉽지 않을 것으로 보입니다. (혹은 제가 제대로 이해하지 못하였을 수 있습니다.)


서버단에서 처리한 후 DB에 넣을 생각을 해보긴 했습니다만 

매우 가끔 시계열 데이터를 사용하는 것 때문에 그리 하지 않으려고 하였습니다.

그냥 DB에 접근해서 데이터를 긁어온 후 시간단위로 정리하는 프로그램을 만들어야겠다 하다가 질문을 남기게 되었네요.


아무튼, 답변 매우 감사드립니다! 행복한 금요일 되셔요!! :)

전성용(sdf4268)님이 2022-01-07 08:58에 작성한 댓글입니다.

iot 라면 time series 가 좋겠네요.

time series 같은걸 도입 하기 힘든 경우


람다 아키텍처(전날까지 배치 + 오늘 데이터)

같은 것도 한 방안이 될 수 있을거 같습니다.


좋은 하루 되십시오.

천정대(gt1000)님이 2022-01-07 10:22에 작성한 댓글입니다.
SELECT TO_CHAR(save_date, 'yyyy-mm-dd hh24:mi')
     , AVG(data) avg_data
  FROM t
 GROUP BY TO_CHAR(save_date, 'yyyy-mm-dd hh24:mi')
;
마농(manon94)님이 2022-01-07 10:56에 작성한 댓글입니다.

감사드립니다.

제가 얼마나 DB에 무지한지 바로 나와버리네요 ㅠㅠ


천정대님, 마농님 진심으로 감사드립니다.

해결했어요!


행복가득한 하루 되셔요!!

전성용(sdf4268)님이 2022-01-07 13:56에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
10311postgresql 덤프 오류 [1]
정승준
2022-01-14
1119
10310공간연산질문입니다. [1]
nadadf
2022-01-13
1132
10309postgresql 12버전 UI 관련 문의 [1]
고준구
2022-01-07
1107
10308SQL 질문드립니다. [7]
전성용
2022-01-06
1188
10307pg 안정화 버전 추천 부탁드립니다! [3]
김성아
2021-12-31
1341
10306인덱스 생성 시 에러가납니다 ㅜㅠ [3]
포스트초보
2021-12-14
1566
10305오류: 1114 블럭을 "base/16385/16536" 파일에서 읽을 수 없음: 0 / 8192 바이트만 읽음 [1]
황성범
2021-12-13
1456
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다