오라클통계쿼리

SELECT
    TO_CHAR(ADD_MONTHS(TO_DATE(#{fromDate}, 'YYYY-MM'),(LEVEL - 1)),'YYYY-MM') AS START_TIME,
    TO_CHAR(ADD_MONTHS(TO_DATE(#{fromDate}, 'YYYY-MM'),(LEVEL)),'YYYY-MM') AS END_TIME
FROM DUAL
    CONNECT BY ADD_MONTHS(TO_DATE(#{fromDate}, 'YYYY-MM'),(LEVEL - 1)) <= TO_DATE(#{toDate}, 'YYYY-MM')

fromData, toDate의 값을 받아 일별통계쿼리입니다.

SELECT 
    TO_CHAR(TO_DATE(#{fromDate}, 'YYYY-MM-DD') + LEVEL - 1, 'YYYY-MM-DD') AS START_TIME,
    TO_CHAR(TO_DATE(#{fromDate}, 'YYYY-MM-DD') + LEVEL, 'YYYY-MM-DD') AS END_TIME 
FROM DUAL 
CONNECT BY LEVEL <![CDATA[<=]]> TO_DATE(#{toDate}, 'YYYY-MM-DD') - TO_DATE(#{fromDate}, 'YYYY-MM-DD') + 1

fromData, toDate의 값을 받아 시간별통계쿼리입니다.

SELECT 
    TO_CHAR(STA_DT,'YYYY-MM-DD HH24:MI') AS START_TIME,
    TO_CHAR(END_DT,'YYYY-MM-DD HH24:MI') AS END_TIME
FROM 
( 
    SELECT 
        TO_DATE(#{fromDate}, 'YYYY-MM-DD HH24:MI') +((LEVEL-1)) /24 STA_DT,
        TO_DATE(#{fromDate}, 'YYYY-MM-DD HH24:MI') +((LEVEL)) /24 END_DT
    FROM DUAL
    CONNECT BY LEVEL <![CDATA[<=]]> FLOOR((TO_DATE(#{toDate},'YYYY-MM-DD HH24:MI')
    -TO_DATE(#{fromDate},'YYYY-MM-DD HH24:MI'))*24) + 1
)