3시간 삽질후 질문 올립니다. 0 2 4,459

by 정윤호 AVG OVER PARTITION BY 시간단위 평균 SELECT [2011.10.07 17:32:12]


혼자서 여러가지 삽질을 해봤으나 시간만 가고 해결이 안되서 여기다 질문올립니다.
안녕하세요. 초보 개발자 입니다.
죄송합니다.
어떠한 데이터가 00:00 분부터 1분단위로 23:59 분 까지 들어옵니다.
이것을 현재 시간 단위로 평균을 내고 있습니다. 현재는 결과가...
시간 값1 값2
--------------------------------------
00:00 1.1 1.2
01:00 2.1 2.2
   .  .  .
   .  .  .
   .  .  .
23:00 24.1 24.2
----------------------------------------
총 결과 24 rows 
이렇게 나옵니다.
이것을 분마다 표시를 하되, 00:01, 00:02, 00:03......00:59 까지는 00:00 에서 표시되던 평균값으로 채우고 싶습니다.
원하는 결과 rows 는..
시간 값1 값2
--------------------------------------
00:00 1.1 1.2
00:01 1.1 1.2
00:02 1.1 1.2
   .  .  .
   .  .  .
   .  .  .
00:59 1.1 1.2
01:00 2.1 2.2
01:01 2.1 2.2
01:02 2.1 2.2
   .  .  .
   .  .  .
   .  .  .
01:59 2.1 2.2
02:00 3.1 3.2
   .  .  .
   .  .  .
23:00 24.1 24.2
23:01 24.1 24.2
23:02 24.1 24.2
   .  .  .
   .  .  .
23:59 24.1 24.2
----------------------------------------
총 결과 1440 rows 
즉, 값은 시간(HH24) 을 가지고 있는 값은 HH24:00 의 값을 넣는 것 입니다.
고수님들 초보에게 많은 가르침 부탁드립니다.
혹시 몰라 기존 쿼리를 첨부 합니다.
WITH
    A AS (
SELECT 
TO_CHAR(COLLECT_TIME, 'HH24')||':00' AS CTIME
, AVG(VAL1) AS VAL1_AVG
, AVG(VAL2) AS VAL2_AVG
FROM TEST_TBL
WHERE COLLECT_TIME BETWEEN TO_DATE('20110928000000', 'YYYYMMDDHH24MISS') AND TO_DATE('20110928235959', 'YYYYMMDDHH24MISS') 
GROUP BY TO_CHAR(COLLECT_TIME, 'HH24')
ORDER BY CTIME
    )
    , B AS (
SELECT
TO_CAHR(TO_DATE((LEVEL-1) *60, 'SSSSS'), 'HH24:MI') AS CTIME
FROM DUAL
CONNECT BY LEVEL <= 1440
ORDER BY CTIME
    )
SELECT 
    B.CTIME
    , A.VAL1_AVG
    , B.VAL1_AVG
FROM A, B
WHERE A.CTIME(+) = B.CTIME
ORDER BY B.CTIME
by 마농 [2011.10.07 17:48:31]
조건절만 바꾸시면 될듯...
-- WHERE A.CTIME(+) = B.CTIME
WHERE A.CTIME(+) = SUBSTR(B.CTIME, 1, 3) || '00'


만약 1분 데이터가 빠짐 없이 모두 들어온다면?
SELECT TO_CHAR(collect_time, 'hh24:mi') AS ctime
, AVG(val1) OVER(PARTITION BY TO_CHAR(collect_time, 'hh24')) AS val1_avg
, AVG(val2) OVER(PARTITION BY TO_CHAR(collect_time, 'hh24')) AS val2_avg
FROM test_tbl
WHERE collect_time BETWEEN TO_DATE('20110928000000', 'yyyymmddhh24miss') AND TO_DATE('20110928235959', 'yyyymmddhh24miss')
;

by 정윤호 [2011.10.07 18:13:07]
마농님 감사합니다.
너무 허무할정도로 간단하게 해결해주시다니... 심히 부끄럽습니다.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입