[Oracle] 오라클 통계 날짜가 없을 경우 0으로 표시 방법 (월별, 일별)

젠트
데이터베이스/오라클
2020. 8. 6.

오라클에서 날짜별로 건수를 집계하면 존재하는 날짜만 조회되고 존재하지 않는 날짜는 표시되지 않는다. 상황에 따라서 건수가 존재하는 않는 날짜도 "0"으로 조회 되어야 할 상황이 있다.

가상의 날짜뷰를 만들어서 아우터 조인을 하면 해당 날짜가 데이터에 존재하지 않는 날짜도 표시가 가능하다. 필요에 따라서 이런 케이스가 많다면 물리적인 테이블을 생성해 놓고 사용하는 방법도 있다.

일반적인 일별 통계 (존재하는 일자만 표시)

SELECT TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate
     , COUNT(*)
  FROM emp
 WHERE hiredate BETWEEN TO_DATE('1981-02-01', 'YYYY-MM-DD')
                    AND TO_DATE('1981-02-28', 'YYYY-MM-DD')
 GROUP BY hiredate
 ORDER BY hiredate

시작일자~종료일자 까지 모든 일자를 가진 날짜 뷰

 SELECT TO_DATE('1981-02-01','YYYY-MM-DD') + LEVEL - 1 AS dt
   FROM dual 
CONNECT BY LEVEL <= (TO_DATE('1981-02-28','YYYY-MM-DD')      --종료일자
                   - TO_DATE('1981-02-01','YYYY-MM-DD') + 1) --시작일자

일별 통계 (건수가 없는 일자도 표시)

SELECT TO_CHAR(b.dt, 'YYYY-MM-DD') AS hiredate
     , NVL(SUM(a.cnt), 0) cnt
  FROM ( SELECT TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate
              , COUNT(*) cnt
           FROM emp
          WHERE hiredate BETWEEN TO_DATE('1981-02-01', 'YYYY-MM-DD')
                             AND TO_DATE('1981-02-28', 'YYYY-MM-DD') 
          GROUP BY hiredate
        ) a
      , ( SELECT TO_DATE('1981-02-01','YYYY-MM-DD') + LEVEL - 1 AS dt
            FROM dual 
         CONNECT BY LEVEL <= (TO_DATE('1981-02-28','YYYY-MM-DD') 
                            - TO_DATE('1981-02-01','YYYY-MM-DD') + 1)
        ) b
  WHERE b.dt = a.hiredate(+)
  GROUP BY b.dt
  ORDER BY b.dt

월별 통계 (건수가 없는 월도 표시)

SELECT TO_CHAR(b.dt, 'YYYY-MM') AS hiredate
     , NVL(SUM(a.cnt), 0) cnt
  FROM ( SELECT TO_CHAR(hiredate, 'YYYY-MM-DD') AS hiredate
              , COUNT(*) cnt
           FROM emp
          WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
                             AND TO_DATE('1981-12-31', 'YYYY-MM-DD') 
          GROUP BY hiredate
        ) a
      , ( SELECT TO_DATE('1981-01-01','YYYY-MM-DD') + LEVEL - 1 AS dt
            FROM dual 
         CONNECT BY LEVEL <= (TO_DATE('1981-12-31','YYYY-MM-DD') 
                            - TO_DATE('1981-01-01','YYYY-MM-DD') + 1)
        ) b
  WHERE b.dt = a.hiredate(+)
  GROUP BY TO_CHAR(b.dt, 'YYYY-MM')
  ORDER BY TO_CHAR(b.dt, 'YYYY-MM')