[SQL - Bigquery/빅쿼리] URL에서 요소 추출하기(net.host함수)

URL 함수를 이용하여 요소를 추출하는 방법을 알아보고자 한다.

 

다음과 같은 샘플 데이터를 사용해 URL과 관련된 함수를 추출해 보겠다.

 

 

#레퍼러로 어떤 웹 페이지를 거쳐 넘어왔는지 판별하기

어떤 웹 페이지를 거쳐 넘어왔는지 판별할 때는 레퍼러를 집계한다. 하지만 샘플 데이터에서 보듯이 페이지 단위로 집계하면 너무 복잡해지므로, 호스트 단위로 집계하는 것이 일반적이다.

 

※ URL에서 호스트 단위란? 

(url 구조) 출처 : https://www.grabbing.me/URL-018cdd1bb4b541fab6246569244fcf93

 

 

빅쿼리에서는 URL을 다루는 함수가 있다.

net.host() 함수를 이용하면 호스트 이름 부분을 추출할 수 있다.

 

SELECT stamp, net.host(referrer) AS referrer_host
FROM `data.access_log`;

 

net.host()를 이용한 쿼리 결과

다음과 같이 호스트 단위로 집계된 결과를 확인할 수 있다.

 

 

#어떤 웹 페이지에서 몇 번 접속이 발생했는지 확인하기

위 결과를 이용해 어떤 웹 페이지에서 몇번 접속이 발생했는지 알아보려고 한다.

referrer_host를 기준으로 그룹화하여 쿼리를 작성하였다.

 

SELECT count(stamp) AS count, net.host(referrer) AS referrer_host
FROM `data.access_log`
GROUP BY net.host(referrer);

 

위 결과에서는 www.other.com 호스트 단위에서 더 많은 접속이 있었다는 것을 확인할 수 있다.

 

 

지금까지 BigQuery(빅쿼리)의 net.host 함수에 대해 알아보았다.

감사합니다.

 

---

<데이터 분석을 위한 SQL 레시피> 5강 부분을 참고하여 작성하였습니다.