비개발자를 위한
빅쿼리(BigQuery) #1
이 콘텐츠는 구글 빅쿼리를 활용하고자 하는 분들을 위한 콘텐츠입니다.
비개발자 분들에게 좀 더 적합합니다. 비개발자분들을 대상으로 구글 빅쿼리(BigQuery)에 대해 이야기하는 이유는 두가지입니다.
① 필자와 같이 비개발자지만 적절한 시기에 필요한 데이터를 추출하여 활용하거나 데이터 접근 권한이 없더라도 개발자 분에게 데이터를 요청할 때, 보다 빠른 커뮤니케이션을 할 수 있도록 기본적인 쿼리문에 대해서 공유하고자 합니다.
② 최근 Google Analytics가 GA4로 업그레이드 되면서 GA로 수집되는 RAW 데이터를
빅쿼리에서 활용할 수 있기 때문에 앞으로 빅쿼리의 활용 범위가 점차 넓어질 것이기 때문입니다.
먼저 테이블의 구조를 알아보겠습니다.
테이블은 컬럼과 로우로 구성되어 있습니다.
우리가 필요한 컬럼을 선택하여 필요한 데이터를 추출하게 됩니다.
[기본적인 데이터 유형]
대표적인 데이터 유형은 아래와 같습니다. 가장 많이 사용되는 데이터 유형입니다.
데이터 유형 중 꼭 알아야하는 데이터 유형은 RECORD입니다.
RECORD 유형을 이해하기 위해서는 구조체(Structure)와 배열(Array)를 이해해야 합니다.
구조체는 서로 다른 데이터유형의 여러 변수를 그룹 지어 새로운 자료형을 정의하는 것입니다.
배열은 서로 같은 데이터유형의 연속성을 가진 여러 변수를 그룹 지어 새로운 자료형을 정의하는 것입니다.
[실습에 활용할 데이터세트]
직접 실습할 데이터세트는 “google_analytics_sample” 데이터입니다.
google_analytics_sample 데이터 세트의 데이터를 확인해 보겠습니다.
[실습에 활용할 google_analytics_sample 데이터세트]
출처 : https://support.google.com/analytics/answer/3437719?hl=ko우리가 다룰 google_analytics_sample 데이터 세트의 데이터 스키마를 한번씩 살펴봐야 합니다.
google_analytics_sample 데이터세트에는 어떤 데이터가 존재하는지,
그리고 데이터 유형은 무엇인지 등 데이터에 대한 정책이 정의되어 있습니다.
자. 그럼 이제부터 데이터를 추출해 보도록 하겠습니다.
1. SELECT * FROM 테이블명
현재 Fruit_20201101이라는 테이블이 존재한다고 가정해보겠습니다.
Fruit_20201101 테이블의 전체 데이터를 추출하기 위해서는
SELECT절에 “*”를 입력하고 FROM 절에 “Fruit_20201101” 테이블명을 입력합니다.
SELECT 뒤에 “*”는 모든 컬럼을 선택하게 됩니다.
2. SELECT 컬럼명 FROM 테이블명
Fruit_20201101 테이블의 전체 컬럼이 아닌 내가 필요한 컬럼만 추출하고 싶다면
SELECT절에 추출하고 싶은 컬럼명을 입력합니다.
만약 여러 개의 컬럼을 추출하고 싶다면 쉼표(,)로 구분하여 여러 컬럼명을 입력합니다.
제가 Fruit_20201101 테이블의 데이터 중 “과일명” 컬럼의 데이터만 추출하고 싶다면
“SELECT 과일명 FROM Fruit_20201101” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트로 실습해 보도록 하겠습니다.
추출하고자 하는 데이터는 2017년 7월 1일 중복을 포함한 전체 방문자 리스트를 추출하고자 합니다.
3. DISTINCT 함수
추출하고자 하는 컬럼의 데이터 중 중복을 제거한 데이터만 추출하고자 하는 경우,
중복을 제거하고 싶은 컬럼명 앞에 “DISTINCT” 함수를 입력합니다.
Fruit_20201101 테이블의 “과일명” 컬럼의 데이터 중 중복을 제거한 과일명만 확인하고 싶다면
“SELECT DISTINCT 과일명 FROM Fruit_20201101” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일의 중복을 제거한 방문자 리스트를 추출해 보도록 하겠습니다.
4. COUNT 함수, GROUP BY 절
이번에는 과일명 테이블의 각 과일별로 개수를 추출하고자 합니다.
COUNT는 컬럼의 데이터수를 카운트하여 추출합니다.
GROUP BY 절은 선택한 컬럼의 같은 데이터끼리 묶어주게 됩니다.
그래서 각 과일별 개수 데이터를 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수를 추출해 보도록 하겠습니다.
5. AS 절
아마 위의 “SELECT 과일명, COUNT(과일명) FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 COUNT(과일명) 컬럼의 컬럼명이 “f0_”로 보여집니다.
AS는 Aliases의 약자로 별칭을 뜻합니다.
즉, AS 절을 활용하여 별칭(가칭)을 만들 수 있습니다.
기존 f0_ 컬럼명을 AS 절을 활용하여 count로 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수를 추출하고 방문횟수의 컬럼명은 “visit_count”로 추출해보겠습니다.
6. ORDER BY 절, DESC
ORDER BY절은 선택한 데이터를 정렬할 때 사용합니다.
DESC는 내림차순으로 정렬할 때 사용합니다.
그럼 위에서 과일명 별 개수를 추출했는데요. 개수가 많은 순으로 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명 ORDER BY count DESC” 쿼리문을 실행시키면 개수가 많은 과일명 순으로 추출됩니다.
그럼 google_anlaytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수 중 visit_count가 많은 순으로 추출해 보도록 하겠습니다.
7. WHERE 절
WHERE 절은 추출하고자 하는 데이터의 조건을 넣어 추출할 때 사용됩니다.
만약 과일명과 농장명 데이터를 추출하는데 A농장 데이터만 추출하고 싶다면
SELECT 과일명, 농장명 FROM Fruit_20201101
WHERE 농장명 = ‘A농장’
GROUP BY 과일명, 농장명
쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 미국에서 접속한 방문자별 방문횟수를 “visit_count”가 많은 순으로 추출해 보도록 하겠습니다.
8. _TABLE_SUFFIX, BETWEEN a AND b 절
google_analytics_sample 데이터세트 뿐만 아니라 최근 업데이트된 GA4 데이터가 빅쿼리에 적재될 때 하루하루 데이터가 하나의 테이블로 생성됩니다.
google_analytics_sample 데이터세트의 테이블명을 보시면
.ga.sessions_2017-07-01 … .ga.sessions_2017-07-31 각각 테이블이 생성되어 있는 것을 확인할 수 있습니다. 이 때 FROM 절에 날짜 부분을 *로 입력한 후 WHERE 절에 _TABLE_SUFFIX와 BETWEEN 절을 사용하여 추출하고 싶은 기간의 데이터를 추출할 수 있습니다.
그럼 예시를 통해 원하는 기간의 데이터를 추출하는 방법을 알아보도록 하겠습니다.
2020년 11월 1일 ~ 2020년 11월 2일 간의 개수가 많은 과일명 별 개수를 추출하고 싶다면
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 전체 방문자별 방문횟수를 “visit_count”가 많은 순으로 추출해 보도록 하겠습니다.
9. HAVING 절
HAVING 절은 WHERE 절과 같이 조건을 넣어 추출할 때 사용됩니다.
WHERE 절과 HAVING 절의 차이는 무엇일까요?
WHERE은 FROM에 사용하는 TABLE의 데이터에서 조건을 추가할 때 사용됩니다.
HAVING은 GROUP BY절을 통해 그룹으로 묶인 데이터에서 조건을 추가할 때 사용됩니다.
예를 들어 “SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명”
쿼리문을 실행하면 과일명 별 count수를 확인할 수 있는데요. 이 중 count수가 1 초과한 과일명을 추출하고 싶다면 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
참고사항
위의 쿼리문에서 WHERE절로 조건을 추가하는 경우 Fruit_20201101 테이블에는 count 컬럼이 없기 때문에 에러가 발생합니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자 중 visit_count가 2이상인 방문자를 추출해 보겠습니다.
그럼 문제입니다.
Q1. google_analytics_sample 데이터세트에서 2017년 7월 1일 유입소스별 방문횟수 데이터를 추출해 보시기 바랍니다.
Q2. google_analytics_sample 데이터세트에서 2017년 7월 전체의 미국국가의 지역별 방문횟수를 추출해 보시기 바랍니다.
정답은 다음 콘텐츠에서 공개하겠습니다.
감사합니다.
