데이터베이스 질문과 답변

rollup 질문 수정후 다시 올립니다... 0 6 2,177

by 사랑초 [2013.03.19 14:49:19]

다시 질문글을 올립니다...

다소 장문의 글이 될 것 같습니다...
필력이 좋으면 간단명료하게 설명 드릴수 있을텐데...아직은 내공이 부족하여..ㅠ

아래와 같은 양식으로 통계를 추출해야 합니다...

원본 데이터를 가지고 3번의 추가 과정을 통해 
수정본의 데이터 중 일부 데이터를 추출하고 있습니다.

일별 COUNT 수, 일별 UV 수, 월별 UV 수 까지는 여차저차 만들었습니다...ㅠ
(나름 방법을 많이 생각해서 했지만 한참 부족하다는 생각....)

월별 SUM, 월별 AVG, 년도별 합산 데이터를 추가해야 하는 상황에 도달했습니다..

수정본을 보시면 
년도별 합산은 2월 SUM + 1월 SUM 이며,
월별 SUM 은 월에 해당하는 COUNT 수를 더한 값이며,
월별 AVG 는 월에 해당하는 COUNT 수를 평균 낸 값이며,
월 UV 는 월에 진입한 UV 수 입니다.

select 한번으로 가능할까요...
혹시 힌트 주실수 있으시면 조언 부탁드립니다... 


읽어주셔서 감사합니다..



원본 수정본
D_DATE ID M_TYPE 　 COUNT UV
2013-01-27 17:00 123456 123 년도별 합산 15 　
2013-01-28 17:00 456789 131 2월 UV 　 3
2013-01-29 16:00 123456 123 2월 SUM 8 　
2013-01-29 17:00 123456 131 2월 AVG 1.6 　
2013-01-30 17:00 135790 131 2013-02-05 2 1
2013-01-30 18:00 135790 123 2013-02-04 1 1
2013-01-31 17:00 123456 123 2013-02-03 1 1
2013-02-01 17:00 123456 123 2013-02-02 2 1
2013-02-01 18:00 123456 131 2013-02-01 2 1
2013-02-02 17:00 456789 131 1월 UV 　 3
2013-02-02 20:00 456789 123 1월 SUM 7 　
2013-02-03 17:00 123456 123 1월 AVG 1.4 　
2013-02-04 17:00 135790 131 2013-01-31 1 1
2013-02-05 17:00 123456 123 2013-01-30 2 1
2013-02-05 21:00 123456 131 2013-01-29 2 1
2013-01-28 1 1
2013-01-27 1 1

by 마농 [2013.03.19 14:58:01]

D_DATE 항목의 DATA TYPE 은 뭔가요?
Varchar2 OR Date ?

by 사랑초 [2013.03.19 15:00:15]
원본의 D_DATE 데이터 타입은 date 입니다..
수정본은 제가 VARCHAR 로 변환했구요...
by 마농 [2013.03.19 16:13:08]
항목의 정의가 약간 모호하고 중복되는 경향이 있네요.
UV 라는 컬럼이 있음에도 UV 라는 행구분이 존재하네요.
이로 인해 빈 항목도 생겨나구요.
행구분은 SUM 과 AVG 만 있으면 되고 열구분으로 CNT 와 UV 가 있으면 될 듯합니다.

제가 추천하는 LayOut 은 다음과 같습니다.
이경우엔 년도별 UV 정보까지도 포함 가능합니다.
구분 CNT UV
년도별 15 3
2월 SUM 8 3
2월 AVG 1.6 　

혹시 출력폼 변경이 가능한지? 불가능한지? 알려주세요.

이건 또다른 출력형태.
이경우엔 빈 칸이 없고, 명확하지요. 연도별 UV 및 AVG 까지도 포함되네요.
구분 CNT AVG UV
년도별 15 1.5 3
2월 SUM 8 1.6 3
by 사랑초 [2013.03.19 16:26:49]
그쵸... 의미가 비슷하면서 중복된 값들이 있긴합니다...
제가 하자고 한게 아니라 일방적으로 당한 상황이라;;;
COUNT / UV 를 비롯하여 기재한 필드명칭은 제가 임의로 정한거라...
첫번째 말씀해 주신
구분    CNT    UV
년도별
2월SUM
2월AVG
사항대로 변경이 가능할 지도 모르겠습니다...ㅠ
알려주시면 다시 해보고 말씀드리겠습니다..ㅠㅠ
by 마농 [2013.03.19 17:20:30] 
WITH t AS
(
SELECT TO_DATE('2013-01-27 17', 'yyyy-mm-dd hh24') d_date, 123456 id, 123 m_type FROM dual
UNION ALL SELECT TO_DATE('2013-01-28 17', 'yyyy-mm-dd hh24'), 456789, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-01-29 16', 'yyyy-mm-dd hh24'), 123456, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-01-29 17', 'yyyy-mm-dd hh24'), 123456, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-01-30 17', 'yyyy-mm-dd hh24'), 135790, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-01-30 18', 'yyyy-mm-dd hh24'), 135790, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-01-31 17', 'yyyy-mm-dd hh24'), 123456, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-02-01 17', 'yyyy-mm-dd hh24'), 123456, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-02-01 18', 'yyyy-mm-dd hh24'), 123456, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-02-02 17', 'yyyy-mm-dd hh24'), 456789, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-02-02 20', 'yyyy-mm-dd hh24'), 456789, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-02-03 17', 'yyyy-mm-dd hh24'), 123456, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-02-04 17', 'yyyy-mm-dd hh24'), 135790, 131 FROM dual
UNION ALL SELECT TO_DATE('2013-02-05 17', 'yyyy-mm-dd hh24'), 123456, 123 FROM dual
UNION ALL SELECT TO_DATE('2013-02-05 21', 'yyyy-mm-dd hh24'), 123456, 131 FROM dual
)
-- 1. 우선 제일 마지막에 추천한 양식으로 하면 초 간결하지요...
SELECT COALESCE(ymd, ym, yy) dt
     , COUNT(*) cnt
     , COUNT(*) / COUNT(DISTINCT ymd) avg
     , COUNT(DISTINCT id) uv
  FROM (SELECT TO_CHAR(d_date, 'yyyy') yy
             , TO_CHAR(d_date, 'yyyy-mm') ym
             , TO_CHAR(d_date, 'yyyy-mm-dd') ymd
             , id
          FROM t
        )
 GROUP BY yy, ROLLUP(ym, ymd)
 ORDER BY yy DESC, ym DESC, ymd DESC
;
-- 2. 중간 형태의 양식으로 하면
SELECT COALESCE(ymd, ym, yy) || DECODE(GROUPING_ID(1, ymd), 1, ' AVG', 3, ' SUM') dt
     , COUNT(*) / DECODE(GROUPING(1), 0, COUNT(DISTINCT ymd), 1) cnt
     , COUNT(DISTINCT id) / DECODE(GROUPING(1), 0, COUNT(DISTINCT ymd), 1) uv
  FROM (SELECT TO_CHAR(d_date, 'yyyy') yy
             , TO_CHAR(d_date, 'yyyy-mm') ym
             , TO_CHAR(d_date, 'yyyy-mm-dd') ymd
             , id
          FROM t
        )
 GROUP BY yy, ROLLUP(ym, 1, ymd)
 ORDER BY yy DESC, ym DESC, ymd DESC, GROUPING(1) DESC
;
-- 3. 최초 요규사항 양식으로 하면
SELECT DECODE(GROUPING_ID(ym, 1, 2, ymd)
       , 0, ymd
       , 1, ym || ' AVG'
       , 3, ym || ' SUM'
       , 7, ym || ' UV'
       , 15, yy || ' SUM'
       ) dt 
     , DECODE(GROUPING_ID(ym, 1, 2, ymd)
       , 1, COUNT(*) / COUNT(DISTINCT ymd)
       , 7, null
       , COUNT(*)
       ) cnt
     , DECODE(GROUPING_ID(ym, 1, 2, ymd)
       , 0, COUNT(DISTINCT id)
       , 7, COUNT(DISTINCT id)
       ) uv
  FROM (SELECT TO_CHAR(d_date, 'yyyy') yy
             , TO_CHAR(d_date, 'yyyy-mm') ym
             , TO_CHAR(d_date, 'yyyy-mm-dd') ymd
             , id
          FROM t
        )
 GROUP BY yy, ROLLUP(ym, 1, 2, ymd)
 ORDER BY yy DESC, ym DESC, ymd DESC, GROUPING_ID(ym, 1, 2, ymd) DESC
;
-- 여담.
-- 요즘 데이터 모델링 쪽 스터디를 진행했었는데.
-- 모델링(설계)를 잘해야 한다는 걸 느끼게 하는 문제였네요.
-- 의미 중복 및 빈공간이 나오는 모델은 쿼리가 복잡해 지네요.
by 사랑초 [2013.03.19 17:53:32]
헛 스터디...
음 스터디 열리는것 과 질문글 겸사겸사 보는 중인데,
오라클 클럽에서 자체적으로 하는건 이미 기존분들간에 진행하시는것 같고
새로 하는 스터디 있음 저도 참가하고 싶은 마음...이네요...
제가 마농님 시간 뺏고 고생만 시켜드렸네요;
감사합니다!
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입