데이터베이스 질문과 답변

방문자 건수 및 평균 방문 계산 문의 0 6 2,253

by Taems [SQL Query] 평균 계산 [2017.09.19 14:24:45]

안녕하세요. 구루비에서 많은 도움을 받고 있는데요^^

현재 년도월 별 웹사이트 방문자를 구해 평균계산을 하려고 합니다.

아래 쿼리는 2016.10월 ~12월, 201708월에 방문자에 대한 건수를 뽑은 쿼리와 결과 값입니다.

아래의 데이터를 기준으로 해당 월별 방문 평균값과 전체 방문건수의 평균값을 쿼리로 구하려고 합니다. 

전체 평균계산을 하자면 (2016년10월 건수 + 2016년11월 건수 + 2016년12월 건수 + 2018년8월 건수)/4 해야하는데 현재 쿼리상으로 평균을 함께 추가하는 방법을

문의드립니다... 그리고 GROUP BY GROUPING SETS (SUBSTR(W_DATE, 1, 6)), MEM_NO 부분에 MEM_NO 컬럼을 GROUPING SETS 에 포함을 시키면 전체 합계는 나오는데 회원 별 소계는 안됩니다... 그래서 소계와 합계 결과도 함께 낼 수 있는 방법도 같이 문의 드립니다.....

SELECT 
    DECODE(SUBSTR(W_DATE, 1, 6), NULL, '합계', SUBSTR(W_DATE, 1, 6)) W_DATE, 
    MEM_NO, 
    COUNT(*)
FROM
    (SELECT 
        TO_CHAR(W_DATE, 'YYYYMMDD') W_DATE, 
        MEM_NO, 
        COUNT(*) CNT
    FROM 
        방문기록 테이블
    WHERE 
        REGEXP_LIKE(TO_CHAR(W_DATE, 'YYYYMMdd'), '201610|201611|201612|201708')
    GROUP BY TO_CHAR(W_DATE, 'YYYYMMDD'), MEM_NO)
GROUP BY GROUPING SETS (SUBSTR(W_DATE, 1, 6)), MEM_NO
ORDER BY MEM_NO, W_DATE;

--결과값

방문날짜 회원번호 방문건수
201610 10059 4
201611 10059 6
201612 10059 3
201708 10059 1
201610 1021 20
201611 1021 23
201612 1021 15
201610 10280 13
201611 10280 8
201612 10280 5
201610 10369 4
201611 10369 4
201612 10369 8
201708 10369 2
201610 10460 15
201611 10460 14
201612 10460 24
201708 10460 7
201708 11714 8

 

-- 소계와 합계 결과===========

방문날짜 회원번호 방문건수 평균값
201610 10059 4  
201611 10059 6  
201612 10059 3  
201708 10059 1  
소계　 10059 14  
201610 1021 20  
201611 1021 23  
201612 1021 15  
소계　 1021 58 ???
201610 10280 13  
201611 10280 8  
201612 10280 5  
소계　 10280 26 ???
201610 10369 4  
201611 10369 4  
201612 10369 8  
201708 10369 2  
소계　 10369 18 ???
201610 10460 15  
201611 10460 14  
201612 10460 24  
201708 10460 7  
소계　 10460 60 ???
201708 11714 8  
소계　 11714 8 ???
합계   176 ???
by 우리집아찌 [2017.09.19 14:35:23]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH T AS (
SELECT '201610' DT , '10059' ID ,   4 CNT FROM DUAL UNION ALL
SELECT '201611', '10059',   6 FROM DUAL UNION ALL
SELECT '201612', '10059',   3 FROM DUAL UNION ALL
SELECT '201708', '10059',   1 FROM DUAL UNION ALL
SELECT '201610', '1021',    20 FROM DUAL UNION ALL
SELECT '201611', '1021',    23 FROM DUAL UNION ALL
SELECT '201612', '1021',    15 FROM DUAL UNION ALL
SELECT '201610', '10280',   13 FROM DUAL UNION ALL
SELECT '201611', '10280',   8 FROM DUAL UNION ALL
SELECT '201612', '10280',   5 FROM DUAL UNION ALL
SELECT '201610', '10369',   4 FROM DUAL UNION ALL
SELECT '201611', '10369',   4 FROM DUAL UNION ALL
SELECT '201612', '10369',   8 FROM DUAL UNION ALL
SELECT '201708', '10369',   2 FROM DUAL UNION ALL
SELECT '201610', '10460',   15 FROM DUAL UNION ALL
SELECT '201611', '10460',   14 FROM DUAL UNION ALL
SELECT '201612', '10460',   24 FROM DUAL UNION ALL
SELECT '201708', '10460',   7 FROM DUAL UNION ALL
SELECT '201708', '11714',   8 FROM DUAL
)
 
SELECT CASE WHEN ID IS NULL THEN '합게'
            WHEN DT IS NULL THEN '소계'
            ELSE DT
       END DT , ID , SUM(CNT) CNT
  FROM T
 GROUP BY  ROLLUP (ID ,DT)
 ORDER BY ID

 

by 마농 [2017.09.19 17:26:53]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DECODE(GROUPING_ID(mem_no, w_date), 0, w_date, 1, '소계', 3, '합계') w_date
     , mem_no
     , SUM(cnt) cnt
     , DECODE(GROUPING(w_date), 1, ROUND(AVG(cnt), 2)) avg
  FROM (SELECT TO_CHAR(w_date, 'yyyymm') w_date
             , mem_no
--             , COUNT(*) cnt
             , COUNT(DISTINCT TO_CHAR(w_date, 'yyyymmdd')) cnt
          FROM 방문기록테이블
         WHERE TO_CHAR(w_date, 'yyyymm') IN ('201610', '201611', '201612', '201708')
         GROUP BY TO_CHAR(w_date, 'yyyymm'), mem_no
        )
 GROUP BY ROLLUP(mem_no, w_date)
;

 

by 김용한 [2017.09.20 09:28:21]

, COUNT(*) cnt

=> ,SUM(CNT) CNT 로 해야하는거아닌가요?

by 마농 [2017.09.20 09:36:32]

1. 원본 테이블에 cnt 라는 항목은 없습니다.
  - cnt 는 count(*) 의 결과값입니다.
2. 다시 질문 쿼리를 살펴보니
  - 그룹바이를 일별,월별 두번에 나누어 한것으로 보아...
  - 한 회원이 하루에 여러번 방문하더라도 1번만 카운트 해야 할 것 같네요.
  - 다음과 같이 바꿔야 맞을 것 같네요.
  - COUNT(DISTINCT TO_CHAR(w_date, 'yyyymmdd')) cnt

by 김용한 [2017.09.20 09:48:59]

아 질문자의 집계쿼리를 보고 제가잘못알았군요

by Taems [2017.09.21 17:05:59]

네 마농님 말씀하신데로 해당일에 여러번 들어왔어도 한번만 카운트하게 했습니다.

정말 도움 감사합니다~

참고로 유용하게 활용하겠습니다~~^^

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