database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41139 게시물 읽기
No. 41139
시청률 분포도 구하는 쿼리 질문드립니다..
작성자
박양희(sadadboy)
작성일
2016-04-22 14:11ⓒ
2016-04-25 17:45ⓜ
조회수
8,301

 테이블 A -- 회원 테이블

회원수,      해당년도,     누적회원수

    20,          2013,            20
    15,           2014,           35 
    40,           2015,           75
 
테이블 B  --  컨텐츠 테이블
컨텐츠수,         년도
    50,             2013
    80,             2014
     70,             2015
 
 
테이블 C -- 컨텐츠 시청횟수
회원id ,     시청수,     해당년도
   a,              7,           2013
   b,              13,        2013
    c,              24,         2013
    d,              10,      2013
    e,               40,      2013
     f,             32,       2013
     j,             38,       2013
   a,              54,           2014
   b,              43,        2014
    c,              64,         2014
    d,              20,      2014
    e,               70,      2014
     f,             17,       2014
     j,             69,       2014
     k,             16,      2014
      i,              3,       2014

      h,            37,      2014

a,              14,           2015
   b,              43,        2015
    c,              64,         2015
    d,              50,      2015
    e,               40,      2015
     f,             17,       2015
     j,             69,       2015
     k,             36,      2015
      i,              3,       2015
      h,            7,      2015
       l,            6,      2015
       m,          31,    2015
 
 
해당 년도별 시청율 비율을 구하는건데요..
시청률별로 그룹을 묶어서 표시해야합니다..
시청률은 해당 년도의 컨텐츠로만 뽑습니다.
 
구분,    2013년,  전년대비증감,   2014년,  전년대비증감,  2015년,   전년대비증감
합계,         20,          20,                35,               15,            75,             40
고시청       2,             2,                   5,                 3,            15,              10             
(60~100%)
중시청,     13,            13,                 7,                -5,            42,              47
(30~59%)
저시청        2,              2,                10,               8,              5,              -3
(1~29%)
미시청(0%)    5,               5,               13,                7,               13,            6
 
이런식으로 나와야 하는데요..
 
 
미시청 인원은 로그가 따로없어 A테이블 누적 회원수에서 시청인원을 빼서 넣어야 합니다..
 
부디 도와주시면 감사하겠습니다..
 
 
 
     
 

 

이 글에 대한 댓글이 총 5건 있습니다.

 결과는 임의로 넣었으며 무의미 한 수치입니다..

박양희(sadadboy)님이 2016-04-22 14:13에 작성한 댓글입니다.

올려주신 테이블들이 실제 테이블인지 의심됩니다.
테이블로부터 추출한 데이터 일 듯 하네요?
이런 1차 가공된 산출물들로부터 2차 가공한 최종결과를 원하시는 질문은...
오히려 원본 테이블로부터 직접 데이터를 뽑는것보다 더 어려울 수 있습니다.


결과물에 대한 설명도 전혀 없는 상태에서
결과물의 수치 조차 무의미한 값으로 채워 넣으면?
요구사항을 명확하게 파악하기가 어렵습니다.


중간 산출물을 이용한 질문 보다는 원본으로 질문하시는게 좋구요.
원본대비 결과표를 정확하고 알차게 준비해서 질문해 주시는 게 좋습니다.

마농(manon94)님이 2016-04-25 16:36에 작성한 댓글입니다.

WITH t_member_cnt (m_cnt, yyyy, m_cnt_nu) AS
(
          SELECT 20, 2013, 20 FROM dual
UNION ALL SELECT 15, 2014, 35 FROM dual
UNION ALL SELECT 40, 2015, 75 FROM dual
)
, t_contnets_cnt (c_cnt, yyyy) AS
(
          SELECT 50, 2013 FROM dual
UNION ALL SELECT 80, 2014 FROM dual
UNION ALL SELECT 70, 2015 FROM dual
)
, t_view_cnt(m_id, v_cnt, yyyy) AS
(
          SELECT 'a',  7, 2013 FROM dual
UNION ALL SELECT 'b', 13, 2013 FROM dual
UNION ALL SELECT 'c', 24, 2013 FROM dual
UNION ALL SELECT 'd', 10, 2013 FROM dual
UNION ALL SELECT 'e', 40, 2013 FROM dual
UNION ALL SELECT 'f', 32, 2013 FROM dual
UNION ALL SELECT 'j', 38, 2013 FROM dual
UNION ALL SELECT 'a', 54, 2014 FROM dual
UNION ALL SELECT 'b', 43, 2014 FROM dual
UNION ALL SELECT 'c', 64, 2014 FROM dual
UNION ALL SELECT 'd', 20, 2014 FROM dual
UNION ALL SELECT 'e', 70, 2014 FROM dual
UNION ALL SELECT 'f', 17, 2014 FROM dual
UNION ALL SELECT 'j', 69, 2014 FROM dual
UNION ALL SELECT 'k', 16, 2014 FROM dual
UNION ALL SELECT 'i',  3, 2014 FROM dual
UNION ALL SELECT 'h', 37, 2014 FROM dual
UNION ALL SELECT 'a', 14, 2015 FROM dual
UNION ALL SELECT 'b', 43, 2015 FROM dual
UNION ALL SELECT 'c', 64, 2015 FROM dual
UNION ALL SELECT 'd', 50, 2015 FROM dual
UNION ALL SELECT 'e', 40, 2015 FROM dual
UNION ALL SELECT 'f', 17, 2015 FROM dual
UNION ALL SELECT 'j', 69, 2015 FROM dual
UNION ALL SELECT 'k', 36, 2015 FROM dual
UNION ALL SELECT 'i',  3, 2015 FROM dual
UNION ALL SELECT 'h',  7, 2015 FROM dual
UNION ALL SELECT 'l',  6, 2015 FROM dual
UNION ALL SELECT 'm', 31, 2015 FROM dual
)
SELECT gb
     , DECODE(gb, 0, '합계'
                , 1, '고시청(60~100%)'
                , 2, '중시청(30~59%)'
                , 3, '저시청(1~29%)'
                , 4, '미시청(0%)'
                ) gubun
     , cnt_2013, cnt_2013 - 0        var_2013
     , cnt_2014, cnt_2014 - cnt_2013 var_2014
     , cnt_2015, cnt_2015 - cnt_2014 var_2015
  FROM (SELECT yyyy
             , DECODE(1, GROUPING(1), 0, GROUPING(gb), 4, gb) gb
             , CASE 1 WHEN GROUPING(1)  THEN m_cnt_nu
                      WHEN GROUPING(gb) THEN m_cnt_nu - COUNT(*)
                      ELSE COUNT(*) END cnt
          FROM (SELECT a.yyyy, a.m_cnt_nu
                     , c.m_id
                     , CASE WHEN c.v_cnt / b.c_cnt >= .6 THEN 1 -- 고시청
                            WHEN c.v_cnt / b.c_cnt >= .3 THEN 2 -- 중시청
                            WHEN c.v_cnt / b.c_cnt >  .0 THEN 3 -- 저시청
                        END gb
                  FROM t_member_cnt a
                     , t_contnets_cnt b
                     , t_view_cnt c
                 WHERE a.yyyy = b.yyyy
                   AND b.yyyy = c.yyyy
                   AND a.yyyy IN ('2013', '2014', '2015')
                )
         GROUP BY yyyy, m_cnt_nu, ROLLUP(1, gb)
        )
 PIVOT (MIN(cnt) FOR yyyy IN ( '2013' cnt_2013
                             , '2014' cnt_2014
                             , '2015' cnt_2015
                             ) )
 ORDER BY gb
;

마농(manon94)님이 2016-04-25 17:39에 작성한 댓글입니다.
이 댓글은 2016-04-26 09:53에 마지막으로 수정되었습니다.

 아..

다시 데이터 작성하고있었는데요.. ^^;

 

작성한 데이테는  일단 올려놓겠습니다..

with 회원테이블as

(select 'id1' id, '20160322', reg_dtm from dual
union all select 'id2', '20160404' from dual
union all select 'id3', '20160212' from dual
union all select 'id4', '20160122' from dual
union all select 'id5', '20150322' from dual
union all select 'id6', '20160112' from dual
.......
union all select 'id20', '20150627' from dual
)
기본적으로 회원 아이디와 가입일이 있습니다.
 
with 컨텐츠테이블 as
(
select '1' 순번, '20151013' 등록일자 from dual
union all select '2' , '20150113'  from dual
union all select '3' , '20150213'  from dual
union all select '4' , '20150312'  from dual
union all select '5' , '20150403'  from dual
union all select '6' , '20150612'  from dual
union all select '7' , '20151013'  from dual
.......
union all select '50' , '20160113'  from dual
union all select '51' , '20150216'  from dual
union all select '52' , '20150313'  from dual
)
컨텐츠 테이블로 컨텐츠pk와 등록일자가 있습니다.
 
with 컨텐츠재생로그 as
(
select 'id1' id, '1' 컨텐츠순번, '20150124' 재생일자 from dual
union all select 'id1' , '3' , '20150124'  from dual
union all select 'id1' , '4' , '20150224'  from dual
union all select 'id1' , '8' , '20150324'  from dual
union all select 'id1' , '9' , '20150324'  from dual
union all select 'id2' , '1' , '20150824'  from dual
union all select 'id2' , '10' , '20150124'  from dual
union all select 'id2' , '15' , '20160124'  from dual
union all select 'id2' , '20' , '20151024'  from dual
union all select 'id3' , '3' , '20151215'  from dual
union all select 'id3' , '9' , '20160124'  from dual
.....
union all select 'id9' , '21' , '20160124'  from dual
union all select 'id9' , '19' , '20160216'  from dual
)
컨텐츠 재생 로그 테이블입니다..
같은 사용자가 동일 컨텐츠 재생에 대해선 중복로우가 쌓이지 않습니다..
 
이렇게 3개의 테이블로 통계를 뽑아야 하는데요
 
검색조건이 년도 단위입니다..
 
구분,    2013년,  전년대비증감,   2014년,  전년대비증감,  2015년,   전년대비증감
합계,         20,          20,                35,               15,            75,             40
고시청       2,             2,                   5,                 3,            15,              10             
(60~100%)
중시청,     13,            13,                 7,                -5,            42,              47
(30~59%)
저시청        2,              2,                10,               8,              5,              -3
(1~29%)
미시청(0%)    5,               5,               13,                7,               13,            6
 
결과는 이와같은데요(년도가 안맞는부분은 무시해 주십시요, 지난번에 작성한 게시판에서 긁어왓습니다)
 
통계가 재생이력 테이블에서 각각 아이디당 컨텐츠 시청횟수를 가지고  비율로 만든다음 그룹을 생성하고요.
 
그 그룹별로 카운트를 해서 뿌려주는 통계입니다..
컨텐츠가 100개이고 한 아이디가 90개의 컨텐츠를 시청했다면 고시청에 카운트1이 되는겁니다..(그 행당년도에..)
미시청은 해당년도 전체 회원에서 시정인원 총합을 빼야만 뽑을수 있게 되있구요..
 
모두 인원에 대한 데이터 입니다..
 
2014~2016을 검색하면
2016년도는 아직 지나지 않아 2014~2015년도의 데이터를 뽑아야 하며
2013년도 단일데이터를 가지고 전년대비 증감을 만들어야 합니다..
 
모바일로 작성하여 다소 보시기 불편하셨다면 죄송합니다..
 

 

박양희(sadadboy)님이 2016-04-25 17:43에 작성한 댓글입니다.

감사합니다.

이걸로 다시 해볼게요..

박양희(sadadboy)님이 2016-04-25 17:45에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41142쿼리 도와주세요^^ [3]
강승현
2016-04-25
7819
41141마농님 좀 도와주세요 ㅠㅠ
박양희
2016-04-25
7828
41140쿼리 도와 주세요.. ㅜㅜ [2]
초보
2016-04-22
8000
41139시청률 분포도 구하는 쿼리 질문드립니다.. [5]
박양희
2016-04-22
8301
41138순차적으로 잔액 차감하는 쿼리좀 도와주세요...ㅜ.ㅜ [1]
도끼
2016-04-22
8661
41136역 계층구조 쿼리 질문드립니다. [1]
안드로메지션
2016-04-21
7737
41135이런 경우는 어떻게 해야 할까요? [1]
이상균
2016-04-21
7316
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.049초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다