데이터베이스 질문과 답변
select insert_time, value
from datatable
datatable이란 테이블에 time_stamp 타입의 isert_type 컬럼과, float 타입의 value 라는 컬럼이 있습니다.
이 테이블에서 2시간 간격 평균, 12시간 간격 평균, 1일 간격 평균, 2일 간격 평균, 7일 간격 평균, 14일 간격 평균, 30일 간격 평균을 구하고 싶습니다.
예로 1주일 간격 평균에서 해당 기간에 데이터가 10개가 있으면 10개의 평균을 구하고 1주일에 2일만 데이터가 있으면 2일데이터만 평균을 구하고 해당 기간에 한건도 데이터가 없으면 null 로 출력되면 됩니다.
| 2023-03-04 13:00:00 | 45.03 |
| 2023-03-10 14:00:00 | 31.35 |
| 2023-03-11 10:00:00 | 92.25 |
| 2023-03-12 10:00:00 | 8.07 |
| 2023-03-13 10:00:00 | 71.49 |
| 2023-03-14 10:00:00 | 45.54 |
| 2023-03-15 10:00:00 | 45.87 |
| 2023-03-16 10:00:00 | 93.9 |
| 2023-03-17 10:00:00 | 69.96 |
| 2023-04-03 11:13:59 | 43.29 |
| 2023-04-06 17:47:17 | 0.46 |
| 2023-04-07 10:34:12 | 10.8 |
| 2023-04-08 10:34:24 | 22.19 |
| 2023-04-08 19:04:28 | 32.39 |
| 2023-04-10 11:01:42 | 5.63 |
| 2023-04-10 13:42:51 | 92.81 |
| 2023-04-10 19:01:59 | 72.71 |
| 2023-04-10 19:02:26 | 53.35 |
| 2023-04-11 11:07:24 | 78.42 |
| 2023-04-11 12:07:04 | 44.98 |
| 2023-04-11 12:07:08 | 62.07 |
| 2023-04-11 14:02:14 | 72.54 |
| 2023-04-11 14:02:41 | 68.12 |
| 2023-04-12 10:00:00 | 67.62 |
| 2023-04-12 17:39:01 | 81.42 |
데이터는 대충 이런 데이터 입니다.
많은 조언 부탁드립니다.
by 마농
[2023.04.18 08:41:24]
결과 예시가 필요합니다. 유형별 결과 예시
검색 기간 조건도 있는지요?
by 마농
[2023.04.18 09:07:34]
WITH t AS
(
SELECT '2023-03-04 13:00:00'::TIMESTAMP insert_time, 45.03::FLOAT val
UNION ALL SELECT '2023-03-10 14:00:00', 31.35
UNION ALL SELECT '2023-03-11 10:00:00', 92.25
UNION ALL SELECT '2023-03-12 10:00:00', 8.07
UNION ALL SELECT '2023-03-13 10:00:00', 71.49
UNION ALL SELECT '2023-03-14 10:00:00', 45.54
UNION ALL SELECT '2023-03-15 10:00:00', 45.87
UNION ALL SELECT '2023-03-16 10:00:00', 93.90
UNION ALL SELECT '2023-03-17 10:00:00', 69.96
UNION ALL SELECT '2023-04-03 11:13:59', 43.29
UNION ALL SELECT '2023-04-06 17:47:17', 0.46
UNION ALL SELECT '2023-04-07 10:34:12', 10.80
UNION ALL SELECT '2023-04-08 10:34:24', 22.19
UNION ALL SELECT '2023-04-08 19:04:28', 32.39
UNION ALL SELECT '2023-04-10 11:01:42', 5.63
UNION ALL SELECT '2023-04-10 13:42:51', 92.81
UNION ALL SELECT '2023-04-10 19:01:59', 72.71
UNION ALL SELECT '2023-04-10 19:02:26', 53.35
UNION ALL SELECT '2023-04-11 11:07:24', 78.42
UNION ALL SELECT '2023-04-11 12:07:04', 44.98
UNION ALL SELECT '2023-04-11 12:07:08', 62.07
UNION ALL SELECT '2023-04-11 14:02:14', 72.54
UNION ALL SELECT '2023-04-11 14:02:41', 68.12
UNION ALL SELECT '2023-04-12 10:00:00', 67.62
UNION ALL SELECT '2023-04-12 17:39:01', 81.42
)
SELECT b.stm
, b.etm
, AVG(a.val) v_avg
FROM t a
RIGHT OUTER JOIN
(SELECT dt stm
, dt + itv etm
FROM (SELECT TIMESTAMP '2023-03-04 00:00:00' stm
, TIMESTAMP '2023-03-15 00:00:00' etm
-- , INTERVAL '2 HOUR' itv
-- , INTERVAL '12 HOUR' itv
, INTERVAL '1 DAY' itv
-- , INTERVAL '2 DAY' itv
-- , INTERVAL '7 DAY' itv
-- , INTERVAL '14 DAY' itv
-- , INTERVAL '30 DAY' itv
) a
, GENERATE_SERIES(stm, etm, itv) dt
) b
ON a.insert_time >= b.stm
AND a.insert_time < b.etm
GROUP BY b.stm, b.etm
ORDER BY b.stm, b.etm
;
by 김
[2023.04.18 11:16:53]
오호~ 감사 감사 ㅋ
감사합니다 ^^
) 버튼을 클릭하여 작성 하시면 됩니다.