[oracle] 도메인별 접속통계 구하기 (집계 함수 활용)

--합계 O
WITH parsed_visits AS (
    SELECT 
        REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2) hn
        , COUNT(*) AS cnt
    FROM visiter
    GROUP BY 
        ROLLUP(REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2))
)
SELECT 
    NVL(hn, '합계') AS hostname,
    cnt,
    ROUND(cnt / SUM(DECODE(hn, NULL, 0, cnt)) OVER () * 100, 1) AS rtr
FROM parsed_visits
ORDER BY DECODE(hn, NULL, 9, 1), cnt DESC;

--합계 X
SELECT REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2) hn
    , COUNT(*) AS cnt
    , round(RATIO_TO_REPORT(COUNT(*)) OVER (),3)*100 AS rtr
FROM visiter
GROUP BY REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2)
ORDER BY cnt DESC;

 

 

1. 테이블 구성

방문 경로가 기록된 테이블이 있다.

도메인만 추출하기 위해 protocol, path, query 등의 포함여부가 각기 다른 데이터로 테스트해보았다.

 

 

2. 도메인 추출

정규식을 사용하여 도메인을 추출하였다.

참고할 부분은 "test.co.kr"과 "www.test.co.kr"을 다른 케이스로 인식한다는 것.

필요에 따라 커스텀 해야할 것으로 보인다.

 

 

3. 합계 구하기

 1) 합계 구하기

도메인별 count와 총계를 구했다.

총계는 rollup으로 구할 수 있다.

 

 2) null 값 처리

3-1)의 결과에서, 실제 값이 null인 경우와 rollup 결과 모두 hostname 값이 null로 나온다.

구분하기 위해 nvl로 처리한다.

 

 

 3) 정렬

hostname를 기준으로 정렬되어있는 결과를 cnt 기준으로 변경한다.

 

 

4.최종

최종 select 절에서 hostname이 null인 경우는 총합계이다. 해당 열에 대한 nvl처리를 해주고, 비율을 구했다.

 

도메인과 합계, 비율까지 구했다.

비율에 ratio_to_report 함수를 사용할 수도 있겠다.

 

아래와 같이 정리해볼 수 있다.

 

 

 +) view 상에서 합계 행에 색을 칠하는 등의 표시를 하는 경우...

각 행의 카운트만 구하고 view 상에서 합계를 따로 계산하는게 유용할 때도 있다.

그럼 쿼리는 훨씬 단순해진다..

SELECT REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2) hn
    , COUNT(*) AS cnt
    , round(RATIO_TO_REPORT(COUNT(*)) OVER (),3)*100 AS rtr
FROM visiter
GROUP BY REGEXP_SUBSTR(nvl(v_url,'직접'), '^(https?://)?([^/\\?]+)', 1, 1, NULL, 2)
ORDER BY cnt DESC;

 

 

 

끝.