비개발자를 위한
빅쿼리(BigQuery) #2
지난번 비개발자를 위한 빅쿼리(BigQuery) #1(바로가기)에서는 기본적으로 사용되는 쿼리문에 대해서 알아봤습니다. 그리고 마지막에 두가지 문제를 드렸는데요. 2편을 들어가기 앞서 먼저 문제에 대한 정답을 공개하겠습니다.
Q1. google_analytics_sample 데이터세트에서 2017년 7월 1일 유입소스별 방문횟수 데이터를 추출해 보시기 바랍니다.
trafficSource.source 컬럼은 유입소스 데이터이며, visitid의 COUNT는 세션횟수 즉 방문횟수입니다.
그리고 COUNT 연산 데이터가 들어가기 때문에 반드시 trafficSource.source는 GROUP BY 되어야 합니다.
Q2. google_analytics_sample 데이터세트에서 2017년 7월 전체 미국 국가의 지역별 방문횟수를 추출해 보시기 바랍니다.
위의 쿼리문을 실행하면 city컬럼에 “not available in dome dataset”과 “(not set)”이 확인됩니다.
만약 도시를 알 수 없는 데이터는 제외하고자 하는 경우라면 아래와 같이 WHERE 절에 geoNetwork.city에 NOT IN을 사용하여 “not available in dome dataset”과 “(not set)”의 조건을 추가합니다.
SELECT geoNetwork.city, COUNT(visited) as Visit_count
FROM `bigquery-public-data-google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN ‘20170701’ AND ‘20170731’
AND geoNetwork.city NOT IN (‘not available in demo dataset’, ‘(not set)’)
GROUP BY geoNetwork.city
ORDER BY Visit_count DESC
이제 본격적으로 2편에 대해서 이야기하도록 하겠습니다.
1. UNNEST 함수
우리는 1편에서 구조체와 배열에 대해서 알아보았습니다.
빅쿼리는 하나의 컬럼 안에 배열구조의 또 다른 테이블을 확인할 수 있습니다.
예를 들어 주문상품이라는 데이터는 한 유저가 여러 개의 상품을 구매할 수 있습니다.
이 때 상품이라는 컬럼에 상품명, 주문수량, 주문상품금액이 저장됩니다.
만약 유저 별 주문상품, 주문수량, 주문상품금액을 추출할 때 기존과 같이 쿼리문을 실행하면 아래와 같이 에러가 발생됩니다.
“Cannot access field 컬럼명 on a value with type ARRAY<STRUCT<hitNumber INT64…>>at..”
이는 배열구조로 되어 있기 때문에 각 컬럼을 따로따로 추출할 수 없습니다.
이 때 각 컬럼 데이터를 따로따로 추출할 때 바로 UNNEST 함수를 사용하게 됩니다.
UNNEST 함수는 배열을 평면화 시키는 함수입니다.
그럼 유저 별 주문 상품, 주문수량, 주문상품금액을 추출하고 싶다면
SELECT 고객이름, 상품명, 주문수량, 주문상품금액
FROM 테이블명, UNNEST(상품)
쿼리문을 실행시키면 아래와 같이 데이터를 확인할 수 있습니다.
만약 B상품을 구매한 유저 별 상품, 주문수량, 주문상품금액을 추출하고 싶다면
SELECT 고객이름, 상품명, 주문수량, 주문상품금액
FROM 테이블명, UNNEST(상품)
WHERE 상품명 = ‘B상품’
쿼리문을 실행시키는 아래와 같이 데이터를 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 7월 1일 전체 상품별 주문수량, 주문상품금액 데이터를 추출해 보도록 하겠습니다.
상품명 컬럼은 hits.product.v2ProductName입니다.
주문수량 컬럼은 hits.product.productQuantity입니다.
주문상품금액 컬럼은 hits.product.productRevenue입니다.
위의 컬럼명을 보시면 hits라는 컬럼에 product.v2ProductName, product.productQuantity, productRevenue가 있습니다. 그리고 product 컬럼에 v2ProductName, productQuantity, productRevenue가 있습니다.
hits 컬럼과 product 컬럼의 데이터 유형은 모두 RECORD / REPEATED 입니다.
모두 배열 구조로 되어 있는 것이죠.
이 때 각 상품별 주문수량, 주문상품금액 데이터를 추출하기 위해서는 UNNEST 함수를 사용하여 배열을 평면화 시켜서 추출해야 합니다. 그래서 FROM 절에 UNNEST를 두 번 사용하여 hits와 product 컬럼을 평면화 시킨 것입니다.
2. IS NOT NULL
위의 google_analytics_sample 데이터세트의 UNNEST 실습 쿼리문을 실행하면 주문수량과 주문상품금액에 null 값으로 보여집니다. IS NOT NULL은 컬럼에 NULL값은 제외할 때 사용됩니다.
만약 상품명, 주문상품수량, 주문상품금액 컬럼에서 null 값은 제외하고 추출하고 싶다면
SELECT 고객이름, 상품명, 주문수량, 주문상품금액
FROM `테이블명`, UNNEST(상품)
WHERE 상품명 IS NOT NULL
AND 주문수량 IS NOT NULL
AND 주문상품금액 IS NOT NULL
GROUP BY 고객이름, 상품명
쿼리문을 실행시키면 다음과 같이 상품별 주문수량, 주문상품금액을 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 2017년 7월 1일 전체 상품별 주문수량, 주문상품금액 데이터를 주문수량이 많은 순으로 추출해 보도록 하겠습니다.
3. RANK OVER, PARTITION BY
RANK OVER 함수는 순위를 반환하는 함수입니다. PARTITION BY는 테이블은 분할할 때 활용됩니다.
만약 고객 이름별로 주문상품금액이 높은 순으로 데이터를 확인하고 싶을 때 바로 위의 RANK OVER 함수와 PARTITION BY를 사용합니다.
[ 참고 ]
PARTITION BY와 GROUP BY의 차이점은 무엇인가요?
GROUP BY는 공통된 값을 그룹으로 묶을 때 활용됩니다.
예를 들어 A사용자가 A상품과 B상품을 각각 구매한 경우 아래와 같이 데이터가 저장됩니다.
그럼 총 사용자별 총 주문상품수 데이터를 추출하는 경우 A사용자를 그룹으로 묶어서 주문상품수의 SUM 값을 추출하게 됩니다.
PARTITON BY는 하나의 테이블에서 동일한 값을 분할하여 데이터를 확인할 때 활용됩니다.
예를 들어 사용자를 분할하여 사용자 별로 주문 수량이 높은 순으로 데이터를 추출하게 됩니다.
고객이름, 상품명, 주문수량, 주문상품금액 컬럼과 로우가 있는 테이블에서 고객이름 별로 분할하여 주문상품금액이 높은 순의 로우 데이터를 추출하고 싶다면
SELECT 고객이름, 상품명, 주문수량, 주문상품금액,
RANK() OVER(PARTITION BY 고객이름 ORDER BY 주문상품금액 DESC) AS Rank
FROM `테이블명`, UNNEST(주문상품)
WHERE 상품명 IS NOT NULL
AND 주문수량 IS NOT NULL
AND 주문상품금액 IS NOT NULL
쿼리문을 실행시키면 다음과 같이 고객이름별로 분할하여 주문상품금액이 높은 순으로 데이터를 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 2017년 7월 1일 사용자별 데이터를 분할하여 주문상품금액이 높은 순으로 사용자별 주문상품, 주문수량, 주문상품금액 데이터 추출해 보겠습니다.
4. CONCAT 함수
CONCAT 함수는 여러 개의 문자열을 하나로 결합하는 함수입니다.
만약 고객이름, 상품명을 하나의 문자열로 결합하여 데이터를 추출하고 싶다면
SELECT CONCAT(고객이름, 상품명) AS 고객이름_상품명
FROM `테이블명`
쿼리문을 실행하면 고객이름_상품명 컬럼에 고객이름과 상품명이 결합된 데이터를 확인할 수 있습니다.
5. CAST 함수
CAST는 데이터 타입을 변환하는 함수입니다.
주문수량이라는 컬럼의 데이터 타입은 INT(정수)이며 주문수량 데이터 타입을 문자열로 변경하여 데이터를 추출하고 싶다면
SELECT CONCAT(고객이름, 상품명, CAST(주문수량 AS STRING)) AS 고객이름_상품명_주문수량
FROM `테이블명`
쿼리문을 실행하면 고객이름_상품명_주문수량 컬럼에 고객이름과 상품명 그리고 주문수량이 결합된 데이터를 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 2017년 7월 1일 사용자 아이디와 방문시작시간을 하나의 문자열로 결합하여 추출해 보겠습니다.
6. CASE 함수
CASE 함수는 특정 조건에 따라 값을 할당하는 함수입니다. 보통 데이터를 분류할 때 활용합니다.
만약 저장된 데이터 중 첫 구매여부 컬럼에 값이 첫 구매인 경우 숫자 1값을 첫 구매가 아닌 경우는 NULL값을 저장하는 데이터가 있는 경우 고객별로 첫 구매와, 재구매를 분류하여 데이터를 추출하고 싶다면
SELECT 고객이름,
CASE WHEN 첫 구매여부 = 1 THEN ‘첫 구매’ ELSE ‘재구매’ END AS 첫 구매_재구매
FROM `테이블명`
쿼리문을 실행시키면 고객이름별로 첫 구매, 재구매 데이터를 분류하여 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 2017년 7월 1일의 신규 방문자수를 추출해 보겠습니다.
정리하기
Q1 | 2017년 7월 1일 이벤트(카테고리, 액션, 라벨)별 이벤트 수를 추출해 보시기 바랍니다. 이벤트 수가 높은 순으로 추출해 보시기 바랍니다.
A1 | 2017년 7월 1일 이벤트(카테고리, 액션, 라벨)별 이벤트 수를 추출해 보시기 바랍니다. 이벤트 수가 높은 순으로 추출해 보시기 바랍니다.
Q2 | 2017년 7월 1일 전체 방문횟수와 이탈수, 이탈률을 추출해 보시기 바랍니다.
A2 | 2017년 7월 1일 전체 방문횟수와 이탈수, 이탈률을 추출해 보시기 바랍니다.
마무리하며
지금까지 비개발자인 필자가 1편, 2편으로 나눠서 빅쿼리 쿼리문에 대해서 공유해드렸습니다. 1편에서는 기본적인 쿼리문을 중심으로 이야기하였고 2편에서는 다소 난이도가 있는 쿼리문을 중심으로 이야기하였습니다.
최근 많은 비개발자 분들도 개발자 또는 엔지니어의 영역이라 여겼던 SQL을 많이 학습하려고 합니다. 물론 각 회사의 정책상 비개발자가 데이터 접근 권한을 받는 것은 매우 드문 일입니다. 하지만 쿼리문에 대해서 알고 있다면 필요한 시점에 필요한 데이터를 빠르게 확보할 수 있을 것입니다.
비즈스프링은 데이터 엔지니어링 기술을 통해 수 년간 데이터 분석 솔루션을 제공하고 있습니다. 비즈스프링에서는 고객 행동 데이터를 보유하고 싶은데 어떤 데이터를 어떻게 수집해야 하는지 고민이거나, 고객 행동 데이터 플랫폼을 구축할 수 있는 인력이 부족한 기업들을 위해 데이터 엔지니어링 컨설팅 서비스를 제공하고 있습니다.
비즈스프링 데이터 엔지니어링 컨설팅에 관심있으신 분들은 아래 링크를 참고해 주시기 바랍니다.
