[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