[Oracle Database] 날짜별 통계 + 컬럼 병합
위와 같은 데이터가 있을 때 다음처럼 날짜별 통계 데이터를 출력하는 방법이다:
WITH TEMP AS (
SELECT 1 AS NO, '20150401' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 2 AS NO, '20150401' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 3 AS NO, '20150402' AS REGDATE, '20150408' AS USEDATE FROM DUAL UNION
SELECT 4 AS NO, '20150403' AS REGDATE, '20150409' AS USEDATE FROM DUAL UNION
SELECT 5 AS NO, '20150403' AS REGDATE, '20150409' AS USEDATE FROM DUAL UNION
SELECT 6 AS NO, '20150403' AS REGDATE, '20150410' AS USEDATE FROM DUAL UNION
SELECT 7 AS NO, '20150405' AS REGDATE, '20150411' AS USEDATE FROM DUAL UNION
SELECT 8 AS NO, '20150406' AS REGDATE, '20150412' AS USEDATE FROM DUAL UNION
SELECT 9 AS NO, '20150406' AS REGDATE, '20150413' AS USEDATE FROM DUAL UNION
SELECT 10 AS NO, '20150407' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 11 AS NO, '20150408' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 12 AS NO, '20150409' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 13 AS NO, '20150409' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 14 AS NO, '20150410' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 15 AS NO, '20150411' AS REGDATE, '' AS USEDATE FROM DUAL
)
SELECT NVL(REGDATE, USEDATE), NVL(REG_CNT, 0), NVL(USE_CNT, 0)
FROM (
SELECT REGDATE, COUNT(REGDATE) AS REG_CNT
FROM TEMP
GROUP BY REGDATE
) A
FULL JOIN (
SELECT USEDATE, COUNT(USEDATE) AS USE_CNT
FROM TEMP
GROUP BY USEDATE
) B
ON A.REGDATE = B.USEDATE
ORDER BY REGDATE
별도의 날짜 데이터가 필요한 경우:
WITH days AS (
SELECT TO_CHAR(TO_DATE('20150401','yyyymmdd') + LEVEL - 1, 'yyyymmdd') AS DAY
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20150413','yyyymmdd') - TO_DATE('20150401','yyyymmdd') + 1
),
TEMP AS (
SELECT 1 AS NO, '20150401' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 2 AS NO, '20150401' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 3 AS NO, '20150402' AS REGDATE, '20150408' AS USEDATE FROM DUAL UNION
SELECT 4 AS NO, '20150403' AS REGDATE, '20150409' AS USEDATE FROM DUAL UNION
SELECT 5 AS NO, '20150403' AS REGDATE, '20150409' AS USEDATE FROM DUAL UNION
SELECT 6 AS NO, '20150403' AS REGDATE, '20150410' AS USEDATE FROM DUAL UNION
SELECT 7 AS NO, '20150405' AS REGDATE, '20150411' AS USEDATE FROM DUAL UNION
SELECT 8 AS NO, '20150406' AS REGDATE, '20150412' AS USEDATE FROM DUAL UNION
SELECT 9 AS NO, '20150406' AS REGDATE, '20150413' AS USEDATE FROM DUAL UNION
SELECT 10 AS NO, '20150407' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 11 AS NO, '20150408' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 12 AS NO, '20150409' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 13 AS NO, '20150409' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 14 AS NO, '20150410' AS REGDATE, '' AS USEDATE FROM DUAL UNION
SELECT 15 AS NO, '20150411' AS REGDATE, '' AS USEDATE FROM DUAL
), reg AS (
SELECT day, count(regdate) AS regcnt
FROM TEMP, days
WHERE temp.regdate(+) = days.day
GROUP BY days.day
), use AS (
SELECT day, count(usedate) AS usecnt
FROM TEMP, days
WHERE temp.usedate(+) = days.day
GROUP BY days.day
)
SELECT reg.day, reg.regcnt, use.usecnt
FROM reg, use
WHERE reg.day = use.day
ORDER BY reg.day