데이터베이스 질문과 답변
얼마전에 합계를 구하는 방식으로 해서 질문글을 올렸고,
혼자서 방법을 찾던 중에, 구루비에 있는 강좌와 답변들을 찾아보아
ROLLUP을 ()을 사용하게되었습니다.
마농님 말씀대로 내부에서 ROLLUP을 해 보았는데, 내부적으로는 TOTAL 이라고 해서 합계나 나오는데,
LEVEL문의 통해서 TOTAL 나올 수 있도록 하려면 어떻게 해야할까요 ..??
================================================
쿼리는 위와 같구요.
밑에는 내부쿼리 결과값 : ROLLUP 부분
혼자서 방법을 찾던 중에, 구루비에 있는 강좌와 답변들을 찾아보아
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 쪽을 수정을 해야할 것 같은데 어떻게 해야할지 모르겠네요 . ㅠ
여기에 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을 한 부분은 빼내올 수 없나요 ??
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 참 ..^^;
어렵네요