일별 통계 작성법(ORACLE)

 

 

 

 

DICT, LAST_DAY  FUNCTION 사용
dict : dictionary  테이블

 

 

 

 

1. 등록된 데이터만 나오게 할 경우 

SELECT 
        TO_CHAR(TO_DATE(JRNL_DT,'yyyy-mm-dd'),'dd') AS JRNL_DT,
        SUM(TAKEOUT_AMOUNT) AS TAKEOUT_AMOUNT 
FROM HCTB_MEDICAL_INJ_PRE
GROUP BY TO_CHAR(TO_DATE(JRNL_DT,'yyyy-mm-dd'),'dd')

 

EX)

JRNL_DT    TAKEOUT_AMOUNT

10                 48
11                 2
12                 1

 

 

2.   1~31일 까지 모두 나오게 할때, 데이터가 없는경우 0 ( DICT 테이블을 사용하여 임시로 데이터를 생성한다.)

 

2.1  UNION ALL 방식

SELECT 
    JRNL_DT,
    SUM(TAKEOUT_AMOUNT) AS TAKEOUT_AMOUNT
FROM(           
        SELECT 
            TO_CHAR(TO_DATE(JRNL_DT,'yyyy-mm-dd'),'dd') AS JRNL_DT,
            TAKEOUT_AMOUNT 
        FROM HCTB_MEDICAL_INJ_PRE
        UNION ALL
        SELECT  
            DECODE(LENGTH(ROWNUM), 1, '0'||TO_CHAR(ROWNUM), TO_CHAR(ROWNUM)) JRNL_DT,
            '' TAKEOUT_AMOUNT
        FROM    DICT
        WHERE ROWNUM <= TO_NUMBER(SUBSTR(LAST_DAY('200902'||'01'), 7, 2)) 
)
GROUP BY JRNL_DT
ORDER BY JRNL_DT 

 

2.2 LEFT OUTER JOIN 방식

 

SELECT
    A.JRNL_DT,
    NVL(B.TAKEOUT_AMOUNT,0) AS TAKEOUT_AMOUNT
FROM
    (SELECT  
        DECODE(LENGTH(ROWNUM), 1, '0'||TO_CHAR(ROWNUM), TO_CHAR(ROWNUM)) JRNL_DT,
        0 TAKEOUT_AMOUNT
    FROM    DICT
    WHERE ROWNUM <= TO_NUMBER(SUBSTR(LAST_DAY('200902'||'01'), 7, 2)) 
    )A
    LEFT OUTER JOIN
    (SELECT 
        TO_CHAR(TO_DATE(JRNL_DT,'yyyy-mm-dd'),'dd') AS JRNL_DT,
        SUM(TAKEOUT_AMOUNT) AS TAKEOUT_AMOUNT 
    FROM HCTB_MEDICAL_INJ_PRE
    GROUP BY TO_CHAR(TO_DATE(JRNL_DT,'yyyy-mm-dd'),'dd')
    )B ON(A.JRNL_DT = B.JRNL_DT)
ORDER BY A.JRNL_DT 

EX) 

 

JRNL_DT    TAKEOUT_AMOUNT

1                   0

2                   0
3                   0
.                    .
.                    .

 

10                 48
11                 2
12                 1
.                    .
.                    .
28                 0