[Bigquery] 쿼리 필수요소

빅쿼리 스터디 1일차

Jul 14, 2023

Contents

1. 빅쿼리 객체와 설명 2. SELECT, EXCEPT, REPLACE 3. 배열 4. STRUCT 5. 향후 계획

1. 빅쿼리 객체와 설명

빅쿼리 객체	이름	설명
Project	bigquery-public-data	데이터셋 및 해당 테이블과 연결된 영구 스토리지의 소유자
Dataset	new_york_citibike	데이터셋은 테이블 및 뷰에 대한 액세스를 구성하고 제어하는 데 사용되는 최상위 컨테이너
Table/View	citibike_trips	테이블 또는 뷰는 데이터셋에 속해야하므로 빅쿼리에 데이터를 로드하기 전 하나 이상의 데이터셋을 생성해야 함

2. SELECT, EXCEPT, REPLACE


-- EXCEPT 사용해 특정 컬럼을 제외한 모든 컬럼을 선택하는 쿼리
SELECT * EXCEPT(short_name, last_reported)

-- REPLACE를 사용해 컬럼의 값을 변환하는 쿼리
SELECT * REPLACE(num_bikes_available + 5 AS num_bikes_available)

3. 배열

SQL쿼리에 배열을 하드코딩해서 쿼리를 작성할수도 있다(!). 간단한 테이블 만들듯..


WITH example AS (
	SELECT 'Sat' AS day, 1451 AS numrides, 1018 AS oneways
	UNION ALL SELECT 'Sun', 2376, 936
	UNION ALL SELECT 'Mon', 1476, 736
)
SELECT * FROM example
WHERE numrides < 2000

Q1. 문제 풀어보자!

Customer Segment 테이블을 만들고, 해당 테이블에 열을 추가하고 해당하는 사용자 수를 찾아줘

Customer Segment	device.category	geo.country
P0	mobile	United States
P1	mobile	Japan

쿼리


WITH segment AS (
SELECT 'P0' AS segment, 'mobile' AS category, 'United States' AS country
UNION ALL SELECT 'P1', 'mobile', 'Japan'
)

SELECT segment.segment, segment.category, segment.country, COUNT(distinct user_pseudo_id)
FROM segment
LEFT JOIN firebase-public-project.analytics_153293282.events_20181003 f
ON segment.category = f.device.category
AND segment.country = f.geo.country
GROUP BY 1,2,3

ARRAY_AGG : 개별값을 수집해 순서가 있는 리스트 또는 ARRAY에 넣을 수 있다


SELECT gender, ARRAY_AGG(numbrips ORDER BY year) AS numtrips
FROM (
	SELECT gender, EXTRACT(YEAR FROM starttime) AS year, COUNT(1) AS numbrips
	FROM bigquery-public-data.new_york_citibike.citibike_trips )
GROUP BY 1

4. STRUCT

순서를 갖는 필드 그룹. 필드에는 이름을 정해줄 수 있다.


SELECT
[ STRUCT('male' AS gender, [1, 2] AS numbrips,
STRUCT('female' AS gender, [3, 4] AS numbrips
] AS bikerides

5. 향후 계획

7/17(월)	7/19(수)	7/21(금)
데이터타입, 함수, 연산자	빅쿼리로 데이터 로드 성능 및 비용 최적화	고급쿼리
7/24(월)	7/26(수)	7/28(금)
한국어판 부록 10장 빅쿼리 관리 및 보안	ㅤ	ㅤ