SQL BigQuery 문법 — 채널톡이 쓰는 그 DB

💡 채널톡 JD에 BigQuery가 있다! MySQL과 다른 문법을 정리한다.

---

🤔 BigQuery가 뭐야?

Google Cloud의 데이터 웨어하우스.

• 페타바이트급 데이터 처리
• 서버리스 (관리 필요 없음)
• SQL 기반이지만 문법이 조금 다름

채널톡 JD:

"BigQuery 데이터 마트를 직접 설계하고, Dataform을 활용하여 데이터 모델링"

→ BigQuery 문법 필수!

---

📊 MySQL vs BigQuery 핵심 차이

기능	MySQL	BigQuery
문자열 연결	CONCAT()	CONCAT() 또는 `
날짜 포맷	DATE_FORMAT()	FORMAT_DATE()
날짜 추출	YEAR(), MONTH()	EXTRACT()
문자열 분리	SUBSTRING_INDEX()	SPLIT()
배열	없음	ARRAY, UNNEST()
구조체	없음	STRUCT
정규식	REGEXP	REGEXP_CONTAINS()

---

1️⃣ 기본 문법 차이

테이블 참조

-- MySQL
SELECT * FROM database.table;

-- BigQuery
SELECT * FROM `project.dataset.table`;
-- 백틱(`) 사용!

별칭 (AS 선택)

-- BigQuery에서는 AS 생략 가능
SELECT 
    name AS user_name,  -- AS 있음
    age user_age        -- AS 없음 (가능)
FROM users;

LIMIT

-- 둘 다 동일
SELECT * FROM users LIMIT 10;

---

2️⃣ 날짜/시간 함수

현재 날짜/시간

-- MySQL
SELECT CURDATE(), NOW();

-- BigQuery
SELECT CURRENT_DATE(), CURRENT_TIMESTAMP();

날짜 포맷

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m');

-- BigQuery
SELECT FORMAT_DATE('%Y-%m', order_date);
-- 순서가 반대! (포맷, 날짜)

날짜 추출

-- MySQL
SELECT YEAR(order_date), MONTH(order_date);

-- BigQuery
SELECT 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date);

날짜 연산

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 7 DAY);
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH);

-- BigQuery
SELECT DATE_ADD(order_date, INTERVAL 7 DAY);
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH);
-- 동일!

날짜 차이

-- MySQL
SELECT DATEDIFF(date1, date2);

-- BigQuery
SELECT DATE_DIFF(date1, date2, DAY);
-- 단위 명시 필요 (DAY, MONTH, YEAR 등)

---

3️⃣ 문자열 함수

기본 함수 (동일)

-- 둘 다 동일
SELECT 
    LENGTH(name),
    UPPER(name),
    LOWER(name),
    TRIM(name),
    CONCAT(first, ' ', last)
FROM users;

부분 문자열

-- MySQL
SELECT SUBSTRING(email, 1, 5);
SELECT LEFT(email, 5);

-- BigQuery
SELECT SUBSTR(email, 1, 5);  -- SUBSTRING도 가능
SELECT LEFT(email, 5);       -- 동일

문자열 분리 ⭐

-- MySQL
SELECT SUBSTRING_INDEX(email, '@', 1);  -- @ 앞부분

-- BigQuery (배열 반환!)
SELECT SPLIT(email, '@')[OFFSET(0)];    -- @ 앞부분
SELECT SPLIT(email, '@')[OFFSET(1)];    -- @ 뒷부분
-- OFFSET은 0부터 시작!

문자열 위치

-- MySQL
SELECT LOCATE('@', email);

-- BigQuery
SELECT STRPOS(email, '@');
-- 결과 동일 (1부터 시작)

---

4️⃣ 정규식

패턴 매칭

-- MySQL
SELECT * FROM users WHERE email REGEXP '@gmail';

-- BigQuery
SELECT * FROM users WHERE REGEXP_CONTAINS(email, r'@gmail');
-- r'' 은 raw string (이스케이프 편함)

패턴 추출

-- BigQuery 전용
SELECT REGEXP_EXTRACT(email, r'@(.+)\.') AS domain;
-- 캡처 그룹 추출

패턴 치환

-- BigQuery
SELECT REGEXP_REPLACE(phone, r'[^0-9]', '') AS clean_phone;
-- 숫자만 남기기

---

5️⃣ 배열 (ARRAY) ⭐

BigQuery의 강력한 기능!

배열 생성

SELECT [1, 2, 3] AS numbers;
SELECT ['a', 'b', 'c'] AS letters;

배열 접근

SELECT 
    arr[OFFSET(0)] AS first,    -- 0부터 시작
    arr[ORDINAL(1)] AS first2,  -- 1부터 시작
    arr[SAFE_OFFSET(10)] AS safe_access  -- 범위 벗어나면 NULL
FROM (SELECT [1,2,3] AS arr);

UNNEST — 배열 펼치기

-- 배열을 행으로 변환
SELECT num
FROM UNNEST([1, 2, 3]) AS num;

num
1
2
3

실전 활용: 태그 분석

-- tags 컬럼이 ['서울', '개발', 'AI'] 형태일 때
SELECT 
    user_id,
    tag
FROM users, UNNEST(tags) AS tag;

user_id	tag
1	서울
1	개발
1	AI

---

6️⃣ 구조체 (STRUCT)

구조체 생성

SELECT STRUCT(1 AS id, 'kim' AS name) AS user_info;

구조체 접근

SELECT 
    user_info.id,
    user_info.name
FROM (
    SELECT STRUCT(1 AS id, 'kim' AS name) AS user_info
);

---

7️⃣ 윈도우 함수

기본 (동일)

-- 둘 다 동일
SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

BigQuery 전용: QUALIFY ⭐

-- MySQL: CTE 필요
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

-- BigQuery: QUALIFY로 간단하게!
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) = 1;

→ 코드가 훨씬 간결!

---

8️⃣ 집계 함수

배열 집계

-- 그룹별로 값들을 배열로
SELECT 
    user_id,
    ARRAY_AGG(product) AS products
FROM orders
GROUP BY user_id;

user_id	products
1	['노트북', '마우스', '키보드']
2	['모니터']

문자열 집계

-- MySQL: GROUP_CONCAT
SELECT user_id, GROUP_CONCAT(product) FROM orders GROUP BY user_id;

-- BigQuery: STRING_AGG
SELECT user_id, STRING_AGG(product, ', ') AS products
FROM orders
GROUP BY user_id;

---

9️⃣ 조건문

IF

-- MySQL
SELECT IF(amount > 100000, 'VIP', '일반') AS tier;

-- BigQuery (동일)
SELECT IF(amount > 100000, 'VIP', '일반') AS tier;

CASE WHEN (동일)

SELECT 
    CASE 
        WHEN amount >= 1000000 THEN 'VIP'
        WHEN amount >= 100000 THEN '우수'
        ELSE '일반'
    END AS tier
FROM orders;

IFNULL / COALESCE

-- MySQL
SELECT IFNULL(amount, 0);

-- BigQuery (IFNULL도 되지만 COALESCE 권장)
SELECT COALESCE(amount, 0);

---

🔟 유용한 BigQuery 전용 기능

WITH RECURSIVE (계층 쿼리)

-- 조직도, 카테고리 트리 등
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

PIVOT / UNPIVOT

-- 행 → 열 변환
SELECT * FROM sales
PIVOT(SUM(amount) FOR month IN ('Jan', 'Feb', 'Mar'));

TABLESAMPLE

-- 샘플링 (대용량 테이블에서 일부만)
SELECT * FROM `bigquery-public-data.samples.natality`
TABLESAMPLE SYSTEM (1 PERCENT);

---

🎯 실전 문제

문제 1: 날짜 포맷

월별 주문 건수를 BigQuery 문법으로 구하세요.

정답 보기

SELECT 
    FORMAT_DATE('%Y-%m', order_date) AS month,
    COUNT(*) AS order_count
FROM `project.dataset.orders`
GROUP BY FORMAT_DATE('%Y-%m', order_date)
ORDER BY month;

---

문제 2: 이메일 도메인 추출

이메일에서 도메인을 추출하세요. (BigQuery)

정답 보기

SELECT 
    email,
    SPLIT(email, '@')[OFFSET(1)] AS domain
FROM users;

-- 또는 정규식
SELECT 
    email,
    REGEXP_EXTRACT(email, r'@(.+)') AS domain
FROM users;

---

문제 3: QUALIFY 활용

각 유저별 가장 큰 주문을 QUALIFY로 구하세요.

정답 보기

SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) = 1;

---

문제 4: 배열 활용

유저별 구매한 상품들을 배열로 집계하세요.

정답 보기

SELECT 
    user_id,
    ARRAY_AGG(DISTINCT product ORDER BY product) AS products,
    COUNT(DISTINCT product) AS product_count
FROM orders
GROUP BY user_id;

---

📝 MySQL → BigQuery 변환 치트시트

MySQL	BigQuery
DATE_FORMAT(d, '%Y-%m')	FORMAT_DATE('%Y-%m', d)
YEAR(d)	EXTRACT(YEAR FROM d)
DATEDIFF(a, b)	DATE_DIFF(a, b, DAY)
SUBSTRING_INDEX(s, '@', 1)	SPLIT(s, '@')[OFFSET(0)]
LOCATE('@', s)	STRPOS(s, '@')
GROUP_CONCAT(x)	STRING_AGG(x, ',')
IFNULL(x, 0)	COALESCE(x, 0)
CTE + WHERE rn=1	QUALIFY
REGEXP '패턴'	REGEXP_CONTAINS(col, r'패턴')

---

💡 BigQuery 팁

1. 비용 주의

-- ❌ 전체 스캔 (비용 큼)
SELECT * FROM huge_table;

-- ✅ 필요한 컬럼만 (비용 절약)
SELECT id, name FROM huge_table;

-- ✅ 파티션 필터 (비용 절약)
SELECT * FROM huge_table
WHERE date >= '2024-01-01';

2. 백틱 사용

-- 프로젝트.데이터셋.테이블 형식
SELECT * FROM `my-project.my_dataset.my_table`;

3. SAFE 함수

-- 에러 대신 NULL 반환
SELECT 
    SAFE_DIVIDE(a, b),           -- 0으로 나눠도 NULL
    SAFE_CAST('abc' AS INT64)    -- 변환 실패해도 NULL
FROM table;

---

🔗 참고 자료

• BigQuery 공식 문서
• BigQuery 표준 SQL 레퍼런스

---

📝 BigQuery는 MySQL과 90% 비슷하지만, 10%의 차이가 중요하다.
FORMAT_DATE, SPLIT, QUALIFY만 기억해도 대부분 커버 가능!