데이터베이스 질문과 답변

날짜별 카운트 합계 문의 0 4 1,135

by 임문자 [CUBRID] CUBRID count sum 날짜 합계 [2021.09.06 11:23:27]

안녕하세요. 현재년월을 기준으로 전년도 년월을 포함 사용자 날짜별 합계를 구현하고 있습니다.

올해 기준 2021년 9월06일부터 전년도 2020년 09월 07일까지의 사용자 날짜별 카운트 합계를 구하려고 아래와 같이 

쿼리를 짰는데요.

문제는 현재년월을 기준의로 ADDDATE로 year, month를 이용해서 사용하다 보니 현재 월과 전년도 월에 따라서 적용이 되어야하는데 아래와 같이 현재 9월이니까 INTERVAL -0 MONTH 으로 되어 있습니다.

10월달이 되면 지금 쿼리로는 12개월을 넘어가서 다른 방법이 있는지 알려주시면 감사하겠습니다~.

 

 

전년도 월 : 

CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -0 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR9

현재 월 : 

CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -0 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR9

 

SELECT * FROM(
    SELECT 
        ROWNUM RNUM, A.*
    FROM
        (SELECT
            LOGIN_ID, LOGIN_NAME,
            FORMAT(SUM(NVL(LAST_YEAR1, 0)+NVL(LAST_YEAR2, 0)+NVL(LAST_YEAR3, 0)+NVL(LAST_YEAR4, 0)+NVL(LAST_YEAR5, 0)+NVL(LAST_YEAR6, 0)+NVL(LAST_YEAR7, 0)+NVL(LAST_YEAR8, 0)+NVL(LAST_YEAR9, 0)+NVL(LAST_YEAR10, 0)+NVL(LAST_YEAR11, 0)+NVL(LAST_YEAR12, 0)+NVL(NOW_YEAR1, 0)+NVL(NOW_YEAR2, 0)+NVL(NOW_YEAR3, 0)+NVL(NOW_YEAR4, 0)+NVL(NOW_YEAR5, 0)+NVL(NOW_YEAR6, 0)+NVL(NOW_YEAR7, 0)+NVL(NOW_YEAR8, 0)+NVL(NOW_YEAR9, 0)+NVL(NOW_YEAR10, 0)+NVL(NOW_YEAR11, 0)+NVL(NOW_YEAR12, 0)), 0) TOTAL,            
            FORMAT(NVL(SUM(LAST_YEAR1), 0), 0) AS LAST_YEAR1,
            FORMAT(NVL(SUM(LAST_YEAR2), 0), 0) AS LAST_YEAR2,
            FORMAT(NVL(SUM(LAST_YEAR3), 0), 0) AS LAST_YEAR3,
            FORMAT(NVL(SUM(LAST_YEAR4), 0), 0) AS LAST_YEAR4,
            FORMAT(NVL(SUM(LAST_YEAR5), 0), 0) AS LAST_YEAR5,
            FORMAT(NVL(SUM(LAST_YEAR6), 0), 0) AS LAST_YEAR6,
            FORMAT(NVL(SUM(LAST_YEAR7), 0), 0) AS LAST_YEAR7,
            FORMAT(NVL(SUM(LAST_YEAR8), 0), 0) AS LAST_YEAR8,
            FORMAT(NVL(SUM(LAST_YEAR9), 0), 0) AS LAST_YEAR9,
            FORMAT(NVL(SUM(LAST_YEAR10), 0), 0) AS LAST_YEAR10,
            FORMAT(NVL(SUM(LAST_YEAR11), 0), 0) AS LAST_YEAR11,
            FORMAT(NVL(SUM(LAST_YEAR12), 0), 0) AS LAST_YEAR12,
            FORMAT(NVL(SUM(NOW_YEAR1), 0), 0) AS NOW_YEAR1,
            FORMAT(NVL(SUM(NOW_YEAR2), 0), 0) AS NOW_YEAR2,
            FORMAT(NVL(SUM(NOW_YEAR3), 0), 0) AS NOW_YEAR3,
            FORMAT(NVL(SUM(NOW_YEAR4), 0), 0) AS NOW_YEAR4,
            FORMAT(NVL(SUM(NOW_YEAR5), 0), 0) AS NOW_YEAR5,
            FORMAT(NVL(SUM(NOW_YEAR6), 0), 0) AS NOW_YEAR6,
            FORMAT(NVL(SUM(NOW_YEAR7), 0), 0) AS NOW_YEAR7,
            FORMAT(NVL(SUM(NOW_YEAR8), 0), 0) AS NOW_YEAR8,
            FORMAT(NVL(SUM(NOW_YEAR9), 0), 0) AS NOW_YEAR9,
            FORMAT(NVL(SUM(NOW_YEAR10), 0), 0) AS NOW_YEAR10,
            FORMAT(NVL(SUM(NOW_YEAR11), 0), 0) AS NOW_YEAR11,
            FORMAT(NVL(SUM(NOW_YEAR12), 0), 0) AS NOW_YEAR12    
        FROM          
            (SELECT
                LOGIN_ID, LOGIN_NAME,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -8 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR1,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -7 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR2,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -6 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR3,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -5 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR4,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -4 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR5,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -3 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR6,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -2 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR7,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -1 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR8,    
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -0 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR9,                        
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL +1 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR10,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL +2 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR11,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL +3 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR12,
                
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -8 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR1,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -7 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR2,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -6 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR3,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -5 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR4,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -4 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR5,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -3 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR6,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -2 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR7,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -1 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR8,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -0 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR9,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL +1 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR10,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL +2 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR11,
                CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL +3 MONTH), 'YYYYMM')) THEN COUNT(*) END NOW_YEAR12
            FROM
                TARGET_TABLE
            WHERE 
                (TO_CHAR(CREATE_TIME, 'YYYYMMDD') BETWEEN (SELECT TO_CHAR(ADDDATE(ADDDATE( SYSDATE , INTERVAL +1 DAY), INTERVAL -1 YEAR), 'YYYYMMDD')) AND TO_CHAR(SYSDATE, 'YYYYMMDD'))
            GROUP BY 
                LOGIN_ID, LOGIN_NAME, TO_CHAR(CREATE_TIME, 'YYYYMM'))
        GROUP BY
            LOGIN_ID, LOGIN_NAME) A);

by 동동동 [2021.09.06 13:11:29]

CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( ADDDATE( SYSDATE , INTERVAL -0 MONTH), INTERVAL -1 YEAR), 'YYYYMM')) THEN COUNT(*) END LAST_YEAR9

부분을 그냥

CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (TO_CHAR(SYSDATE + (INTERVAL '-1' YEAR), 'YYYY') || '09') THEN COUNT(*) END LAST_YEAR9

로 하면 안되나요?

아..CUBRID는 좀 다르려나??

by 임문자 [2021.09.06 13:29:44]

비슷해요~ 이런식으로 하면 되는데 다른 방식으론 함수 밖에 없을거 같기도 하고요...

CASE WHEN TO_CHAR(CREATE_TIME, 'YYYYMM') = (SELECT TO_CHAR( ADDDATE( SYSDATE , INTERVAL -1 YEAR), 'YYYY')||'09') THEN COUNT(*) END LAST_YEAR9

by 마농 [2021.09.06 13:34:04] 
SELECT login_id
     , login_name
     , COUNT(*) total
     , COUNT(CASE ym WHEN last_year||'01' THEN 1 END) last_year01
     , COUNT(CASE ym WHEN last_year||'02' THEN 1 END) last_year02
     , COUNT(CASE ym WHEN last_year||'03' THEN 1 END) last_year03
     , COUNT(CASE ym WHEN last_year||'04' THEN 1 END) last_year04
     , COUNT(CASE ym WHEN last_year||'05' THEN 1 END) last_year05
     , COUNT(CASE ym WHEN last_year||'06' THEN 1 END) last_year06
     , COUNT(CASE ym WHEN last_year||'07' THEN 1 END) last_year07
     , COUNT(CASE ym WHEN last_year||'08' THEN 1 END) last_year08
     , COUNT(CASE ym WHEN last_year||'09' THEN 1 END) last_year09
     , COUNT(CASE ym WHEN last_year||'10' THEN 1 END) last_year10
     , COUNT(CASE ym WHEN last_year||'11' THEN 1 END) last_year11
     , COUNT(CASE ym WHEN last_year||'12' THEN 1 END) last_year12
     , COUNT(CASE ym WHEN  now_year||'01' THEN 1 END)  now_year01
     , COUNT(CASE ym WHEN  now_year||'02' THEN 1 END)  now_year02
     , COUNT(CASE ym WHEN  now_year||'03' THEN 1 END)  now_year03
     , COUNT(CASE ym WHEN  now_year||'04' THEN 1 END)  now_year04
     , COUNT(CASE ym WHEN  now_year||'05' THEN 1 END)  now_year05
     , COUNT(CASE ym WHEN  now_year||'06' THEN 1 END)  now_year06
     , COUNT(CASE ym WHEN  now_year||'07' THEN 1 END)  now_year07
     , COUNT(CASE ym WHEN  now_year||'08' THEN 1 END)  now_year08
     , COUNT(CASE ym WHEN  now_year||'09' THEN 1 END)  now_year09
     , COUNT(CASE ym WHEN  now_year||'10' THEN 1 END)  now_year10
     , COUNT(CASE ym WHEN  now_year||'11' THEN 1 END)  now_year11
     , COUNT(CASE ym WHEN  now_year||'12' THEN 1 END)  now_year12
  FROM (SELECT login_id
             , login_name
             , TO_CHAR(create_time, 'yyyymm') ym
             , TO_CHAR(sdt, 'yyyy') last_year
             , TO_CHAR(edt, 'yyyy')  now_year
          FROM target_table a
             , (SELECT ADD_MONTHS(TRUNC(sysdate, 'dd'), -12) sdt  -- 시작일
                     ,            TRUNC(sysdate, 'dd')       edt  -- 종료일
                ) b
         WHERE create_time >= sdt + 1
           AND create_time <  edt + 1
        ) c
 GROUP BY login_id, login_name
;

 

by 임문자 [2021.09.06 13:54:31]

감사합니다~ 동동동님~! 마농님~! 구루비에서 정말 많은 배움을 얻고 갑니다^^

두분 즐거운 한주 보내세요~^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입