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

PostgreSQL Q&A 10308 게시물 읽기

No. 10308

SQL 질문드립니다.
작성자	전성용(sdf4268)	작성일	2022-01-06 15:33	조회수	1,643

안녕하세요.

학생은 아니지만.. 궁금한데 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.	제목	작성자	작성일	조회
10311	postgresql 덤프 오류 [1]	정승준	2022-01-14	1582
10310	공간연산질문입니다. [1]	nadadf	2022-01-13	1564
10309	postgresql 12버전 UI 관련 문의 [1]	고준구	2022-01-07	1540
10308	SQL 질문드립니다. [7]	전성용	2022-01-06	1643
10307	pg 안정화 버전 추천 부탁드립니다! [3]	김성아	2021-12-31	1887
10306	인덱스 생성 시 에러가납니다 ㅜㅠ [3]	포스트초보	2021-12-14	2152
10305	오류: 1114 블럭을 "base/16385/16536" 파일에서 읽을 수 없음: 0 / 8192 바이트만 읽음 [1]	황성범	2021-12-13	2070