꿈꾸는 개발자, DBA 커뮤니티 구루비

LEVEL 문에 NULL 을 포함시킬수 있나요 ?

by 정진우 [SQL Query] [2014.01.28 15:04:28]

얼마전에 합계를 구하는 방식으로 해서 질문글을 올렸고,

혼자서 방법을 찾던 중에, 구루비에 있는 강좌와 답변들을 찾아보아

ROLLUP을 ()을 사용하게되었습니다.

마농님 말씀대로 내부에서 ROLLUP을 해 보았는데, 내부적으로는 TOTAL 이라고 해서 합계나 나오는데,

LEVEL문의 통해서 TOTAL 나올 수 있도록 하려면 어떻게 해야할까요 ..??

SELECT b.DAY_CHAR, 
    b.DAY_NUM,
    TO_CHAR( NVL( c.RE_VISIT_PER, 0), '990.00' ) AS RE_VISIT_PER_30,
    TO_CHAR( NVL( a.RE_VISIT_PER, 0), '990.00' ) AS RE_VISIT_PER,
    TO_CHAR( NVL( a.RE_VISIT_PER, 0) - NVL( c.RE_VISIT_PER, 0), '990.00' ) AS UPDOWN
FROM
(
 SELECT NVL( TO_CHAR(visit_time, 'YYYY/MM/DD'), 'TOTAL') AS VISIT_TIME, 
     COUNT(*) AS ALL_VISITOR,
     SUM(visit_return) AS RE_VISIT_CNT,
     SUM(visit_return)/COUNT(*) AS RE_VISIT_PER
 FROM ci_visitor_info
 WHERE TO_DATE(TO_CHAR(visit_time, 'YYYY/MM/DD'),'YYYY/MM/DD') 
 BETWEEN TO_DATE ( '2014/01/19' , 'YYYY/MM/DD' )
 AND TO_DATE ( '2014/01/25' , 'YYYY/MM/DD' )
 AND id_site = 1 
 GROUP BY ROLLUP((TO_CHAR(visit_time, 'YYYY/MM/DD')))
 ) a,
 ( SELECT LEVEL lv, 
  TO_CHAR(TO_DATE ( '2014/01/19' , 'YYYY/MM/DD' )-1 + LEVEL , 'YYYY/MM/DD') AS DAY_NUM, 
  TO_CHAR(TO_DATE ( '2014/01/19' , 'YYYY/MM/DD' )-1 + LEVEL , 'DY') AS DAY_CHAR
  FROM dual 
  CONNECT BY (TO_DATE ( '2014/01/25' , 'YYYY/MM/DD' )-TO_DATE ( '2014/01/19' , 'YYYY/MM/DD' ))+1 >= LEVEL
 ) b,
 (
  SELECT TO_CHAR( visit_time, 'DY') AS VISIT_DAY, 
      COUNT(*) AS ALL_VISITOR,
      SUM(visit_return) AS RE_VISIT_CNT,
      SUM(visit_return)/30 AS RE_VISIT_PER
  FROM ci_visitor_info
  WHERE TO_DATE(TO_CHAR(visit_time, 'YYYY/MM/DD'),'YYYY/MM/DD') 
  BETWEEN TO_DATE ( '2014/01/19' , 'YYYY/MM/DD' )-(30-7)
  AND TO_DATE ( '2014/01/25' , 'YYYY/MM/DD' )
  AND id_site = 1 
  GROUP BY TO_CHAR( visit_time, 'DY')
 ) c
WHERE a.VISIT_TIME (+)= b.DAY_NUM 
AND c.VISIT_DAY (+) = b.DAY_CHAR
ORDER BY b.lv

================================================
쿼리는 위와 같구요.

밑에는 내부쿼리 결과값 : ROLLUP 부분

VISIT_TIME ALL_VISITOR RE_VISIT_CNT RE_VISIT_PER

---------- ----------- ------------ ------------

2014/01/20 6 8 1.33333333

2014/01/21 3 10 3.33333333

2014/01/23 1 6 6

TOTAL 10 24 2.4

이것은 전체 쿼리의 결과값 입니다.

DAY_CHAR DAY_NUM RE_VISIT_PER_30 RE_VISIT_PER UPDOWN

-------- ---------- --------------- ------------ -------

일 2014/01/19 0.00 0.00 0.00

월 2014/01/20 0.30 1.33 1.03

화 2014/01/21 0.33 3.33 3.00

수 2014/01/22 0.00 0.00 0.00

목 2014/01/23 0.20 6.00 5.80

금 2014/01/24 0.03 0.00 -0.03

토 2014/01/25 0.00 0.00 0.00

여기에 TOTAL이 같이 나오게 하고싶어요 .. LEVEL 쪽을 수정을 해야할 것 같은데 어떻게 해야할지 모르겠네요 . ㅠ

by 마농 [2014.01.28 15:40:41]

1. 날짜 조건을 주실 때 컬럼을 to_char 하고 다시 또 to_date 하네요.
   이는 너무나도 소모적인 일이며 안해도 될 일이죠.
   컬럼은 그대로 두고 조건값만 변경시켜 조건을 주세요.
WHERE visit_time >= TO_DATE ('2014/01/19', 'yyyy/mm/dd')
   AND visit_time < TO_DATE ('2014/01/25', 'yyyy/mm/dd') + 1

2. 일주일치 재방문비율과 30일치 재방문율 구하는 공식이 다르네요?
   SUM(visit_return)/COUNT(*) AS re_visit_per
   SUM(visit_return)/30 AS re_visit_per
   일관성이 있어야 하는 것 아닌가요?
   재방문율 구하는 공식 자체도 왜 이렇게 구하는건지 의문이네요?

3. 결국은 요일별 통계를 구하는 듯 한데?
   최근 30일을 구하면? 주 7일로 나누면 28이 나누어 떨어지고 2일이 남게 되는데?
   금,토는 5주간 통계, 나머지 일~목은 4주간 통계가 되어버립니다.
   이 또한 통계 자료로서 일관성이 떨어지게 되는거죠.

재방문율에 대한 정확한 정의와
통계기간에 대한 조정(7의 배수, 4주 또는 5주)이 필요하지 않을런지요?

by 정진우 [2014.01.28 15:59:17]

1. 아 자세한 설명과 조정 감사드립니다.

2. 재방문 비율을 구하는 내용이 아니다 보니 공식이 저렇게 되네요 .
질문내용에 어떤 쿼리를 구현하는지 제대로 첨부 하지 못하였네요 ㅠ.
위 쿼리목적은 평균 재방문 횟수를 구하는 쿼리입니다. visit_return 은 우선 사용자별로 나누었을 때,
구분되어 진다는 가정하에, 구분된 사용자별 재방문 누적 횟수를 기록하고,
일별 (누적된 재방문 횟수) / 일별(방문자) 수로 나누어 평균 재방문 횟수를 구하려고 합니다. ^^

3. 아 맞습니다. 요일별 통계인데, 뭔가 통계기간에 대한 정의를 정확히 내려야겠습니다.
30 일로 구해버리면 애매모호한 형식이 되는군요. 지금 위의 쿼리는 마농님께서 도움주셔서
'일주일'간의 7일을 기준으로 두고 통계를 내려하는데 딱 꼬집어 주셨네요.

많이 부족하기에 :) 이렇게 딱! 알려주셔서 감사합니다.
좀 많이 부끄러우면서도 기분좋은 .ㅎㅎ

아 그런데, GROUP BY ROLLUP을 한 부분은 빼내올 수 없나요 ??

by 마농 [2014.01.28 18:07:29]

SELECT a.day_char
     , a.day_num
     , TO_CHAR(NVL(b.r28, 0), '990.00') re_visit_per_28
     , TO_CHAR(NVL(b.r07, 0), '990.00') re_visit_per
     , TO_CHAR(NVL(b.r07, 0) - NVL(b.r28, 0), '990.00') updown
  FROM (SELECT LEVEL lv
             , DECODE(LEVEL, 8, '합계', TO_CHAR(sdt + LEVEL - 1, 'dy')) day_char
             , DECODE(LEVEL, 8, '', TO_CHAR(sdt + LEVEL - 1, 'yyyy/mm/dd')) day_num
          FROM (SELECT TO_DATE('2014/01/19', 'yyyy/mm/dd') sdt FROM dual)
         CONNECT BY LEVEL <= 8
        ) a
     , (SELECT NVL(TO_CHAR(visit_time, 'dy'), '합계') AS day_char
             , AVG(visit_return) AS r28
             , AVG(CASE WHEN visit_time >= sdt THEN visit_return END) AS r07
          FROM ci_visitor_info
             , (SELECT TO_DATE('2014/01/19', 'yyyy/mm/dd') sdt FROM dual)
         WHERE visit_time >= sdt - 21
           AND visit_time <  sdt +  7
           AND id_site = 1
         GROUP BY ROLLUP(TO_CHAR(visit_time, 'dy'))
        ) b
 WHERE a.day_char = b.day_char(+)
 ORDER BY a.lv
;

by 정진우 [2014.01.29 09:36:42]

아.............

감사합니다 ...

SQL 참 ..^^;

어렵네요

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변