비개발자를 위한 빅쿼리(BigQuery) #2 - BizSpring BLOG
핵심 요약
- 빅쿼리(BigQuery)에서 배열구조 데이터를 다루는 UNNEST 함수 사용법을 실습 예제로 설명한다.
- NULL값 제외(IS NOT NULL), 순위 산출(RANK OVER, PARTITION BY) 등 심화 쿼리 기법을 다룬다.
- 여러 문자열을 결합하는 CONCAT, 데이터 타입을 바꾸는 CAST, 조건 분류에 쓰는 CASE 함수를 설명한다.
- google_analytics_sample 데이터세트를 활용해 각 함수의 실습 문제와 정답 쿼리문을 제공한다.
- 비즈스프링은 데이터 엔지니어링 컨설팅 서비스를 통해 고객 행동 데이터 수집·플랫폼 구축을 지원한다.
비개발자를 위한 빅쿼리(BigQuery) #2 - BizSpring BLOG
콘텐츠로 건너뛰기
내비게이션 메뉴
인사이트
테크
활용 방법/사례
활용 방법/사례
성공사례
성공 사례 일반
광고/마케팅 에이전시
이커머스
미디어/콘텐츠
금융/핀테크
의료/헬스케어
통신/인터넷
뉴스/트렌드
릴리즈 노트
인터넷트렌드 ↗
웹사이트 ↗
# 비개발자를 위한 빅쿼리(BigQuery) #2
2020년 12월 10일 2023년 04월 14일
테크
지난번 비개발자를 위한 빅쿼리(BigQuery) #1( 바로가기 ) 에서는 기본적으로 사용되는 쿼리문에 대해서 알아봤습니다. 그리고 마지막에 두가지 문제를 드렸는데요. 2편을 들어가기 앞서 먼저 문제에 대한 정답을 공개하겠습니다.
Q1. google_analytics_sample 데이터세트에서 2017년 7월 1일 유입소스별 방문횟수 데이터를 추출해 보시기 바랍니다.
A1.
trafficSource.source 컬럼은 유입소스 데이터이며, visitid의 COUNT는 세션횟수 즉 방문횟수입니다.
그리고 COUNT 연산 데이터가 들어가기 때문에 반드시 trafficSource.source는 GROUP BY 되어야 합니다.
Q2. google_analytics_sample 데이터세트에서 2017년 7월 전체 미국 국가의 지역별 방문횟수를 추출해 보시기 바랍니다.
A2.
위의 쿼리문을 실행하면 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편에 대해서 이야기하도록 하겠습니다.
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 컬럼을 평면화 시킨 것입니다.
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일 전체 상품별 주문수량, 주문상품금액 데이터를 주문수량이 많은 순으로 추출해 보도록 하겠습니다.
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일 사용자별 데이터를 분할하여 주문상품금액이 높은 순으로 사용자별 주문상품, 주문수량, 주문상품금액 데이터 추출해 보겠습니다.
CONCAT 함수
CONCAT 함수는 여러 개의 문자열을 하나로 결합하는 함수입니다.
만약 고객이름, 상품명을 하나의 문자열로 결합하여 데이터를 추출하고 싶다면
SELECT CONCAT(고객이름, 상품명) AS 고객이름_상품명
FROM 테이블명
쿼리문을 실행하면 고객이름_상품명 컬럼에 고객이름과 상품명이 결합된 데이터를 확인할 수 있습니다.
CAST 함수
CAST는 데이터 타입을 변환하는 함수입니다.
주문수량이라는 컬럼의 데이터 타입은 INT(정수)이며 주문수량 데이터 타입을 문자열로 변경하여 데이터를 추출하고 싶다면
SELECT CONCAT(고객이름, 상품명, CAST(주문수량 AS STRING)) AS 고객이름_상품명_주문수량
FROM 테이블명
쿼리문을 실행하면 고객이름_상품명_주문수량 컬럼에 고객이름과 상품명 그리고 주문수량이 결합된 데이터를 확인할 수 있습니다.
그럼 google_analytics_sample 데이터세트에서 2017년 7월 1일 사용자 아이디와 방문시작시간을 하나의 문자열로 결합하여 추출해 보겠습니다.
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을 많이 학습하려고 합니다. 물론 각 회사의 정책상 비개발자가 데이터 접근 권한을 받는 것은 매우 드문 일입니다. 하지만 쿼리문에 대해서 알고 있다면 필요한 시점에 필요한 데이터를 빠르게 확보할 수 있을 것입니다.
비즈스프링은 데이터 엔지니어링 기술을 통해 수 년간 데이터 분석 솔루션을 제공하고 있습니다. 비즈스프링에서는 고객 행동 데이터를 보유하고 싶은데 어떤 데이터를 어떻게 수집해야 하는지 고민이거나, 고객 행동 데이터 플랫폼을 구축할 수 있는 인력이 부족한 기업들을 위해 데이터 엔지니어링 컨설팅 서비스를 제공하고 있습니다.
비즈스프링 데이터 엔지니어링 컨설팅에 관심있으신 분들은 아래 링크를 참고해 주시기 바랍니다.
비즈스프링 데이터 엔지니어링 컨설팅 자세히 알아보기
Related Posts via Categories
구글애즈가 정확검색·구문검색 키워드까지 AI 모드에 노출하기 시작했다 — 자동화 상품 없이 AI 답변 화면에 들어가는 첫 실험
AI가 React 앱을 직접 디버깅 – Agent 기반 디버깅
ChatGPT 광고가 픽셀·전환 API·맞춤 오디언스를 갖췄다 — 답변 화면이 측정 가능한 광고 매체가 되는 순간
AI 시대, 데이터도 설명이 필요합니다
MCP 클라이언트는 Authorization Server를 어떻게 찾는가: 공식 MCP TypeScript SDK와 VS Code 구현 비교
RAG 시대의 GEO: AI가 콘텐츠를 읽는 방식과 스키마의 진짜 역할
AI 에이전트로 웹 데이터 수집 가능 여부 자동 검증하기
GA4 Intraday 실시간 테이블, 대시보드 원천 데이터로 바로 사용할 수 있을까?
워크플로우 오케스트레이션 플랫폼 Temporal 알아보기
iOS 14.5부터 GA4까지, 환경 변화가 가져온 업무 폭증을 해결하는 실무 전략은…?
다음에 대해 검색하기...
최신 글 둘러보기
기존 SEO와 무엇이 같고 무엇이 다른가
콘텐츠가 답인 걸 모르는 사람은 없습니다 — 문제는 지속입니다
광고비를 늘리지 않고 매출을 늘린 회사들은 무엇을 했나
코호트로 비교하면 광고 예산 판단이 달라집니다
GEO를 위한 글쓰기를 위한 작은 노하우
(해외동향) 광고주가 광고플랫폼 리포트에서 자체 측정으로 옮겨가고 있다.
구글애즈가 정확검색·구문검색 키워드까지 AI 모드에 노출하기 시작했다 — 자동화 상품 없이 AI 답변 화면에 들어가는 첫 실험
AI가 React 앱을 직접 디버깅 – Agent 기반 디버깅
ChatGPT 광고가 픽셀·전환 API·맞춤 오디언스를 갖췄다 — 답변 화면이 측정 가능한 광고 매체가 되는 순간
마케팅 자동화, 무엇부터 자동화해야 할까?
출처: https://blog.bizspring.co.kr/%ed%85%8c%ed%81%ac/%eb%b9%84%ea%b0%9c%eb%b0%9c%ec%9e%90-%eb%b9%85%ec%bf%bc%eb%a6%ac-bigquery-2/
자주 묻는 질문
UNNEST 함수는 언제 사용하나요?
하나의 컬럼 안에 배열구조로 저장된 데이터(예: 상품명, 주문수량, 주문상품금액)를 각각 따로 추출하고 싶을 때 사용합니다. 배열구조 그대로는 컬럼을 개별적으로 조회할 수 없어 오류가 발생하므로, UNNEST로 배열을 평면화시켜야 합니다.
PARTITION BY와 GROUP BY는 어떻게 다른가요?
GROUP BY는 공통된 값을 하나의 그룹으로 묶어 집계할 때 사용합니다. 반면 PARTITION BY는 하나의 테이블 내에서 동일한 값 기준으로 데이터를 분할하여, 예를 들어 사용자별로 주문 수량이 높은 순으로 로우 데이터를 확인할 때 활용됩니다.
IS NOT NULL은 왜 필요한가요?
UNNEST로 배열을 평면화하면 주문수량, 주문상품금액 등의 컬럼에 NULL 값이 함께 나타날 수 있습니다. IS NOT NULL 조건을 WHERE 절에 추가하면 이러한 NULL 값을 제외하고 원하는 데이터만 추출할 수 있습니다.
CONCAT과 CAST 함수는 각각 어떤 역할을 하나요?
CONCAT은 여러 개의 문자열 컬럼을 하나의 문자열로 결합하는 함수입니다. CAST는 데이터 타입을 변환하는 함수로, 예를 들어 정수형인 주문수량을 문자열로 바꿔 CONCAT과 함께 결합할 때 사용됩니다.
비개발자도 빅쿼리 쿼리문을 배우면 어떤 도움이 되나요?
회사 정책상 비개발자가 데이터에 직접 접근하는 경우는 드물지만, 쿼리문을 알고 있으면 필요한 시점에 필요한 데이터를 빠르게 확보할 수 있습니다. 이 글은 비개발자 입장에서 실습을 통해 심화 쿼리문을 익힐 수 있도록 안내합니다.