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 |
데이터는 대충 이런 데이터 입니다.
많은 조언 부탁드립니다.
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 ;