3장 데이터 가공을 위한 SQL
5강 하나의 값 조작하기
1. 코드 값을 레이블로 변경하기: CASE WHEN
2. URL에서 요소 추출하기
1) 레퍼러 도메인 추출: HOST(), REGEXP_REPLACE(REGEXP_SUBSTR()) - BQ에선 host()!
2) URL path 나 매개변수 값 추출: REGEXP_EXTRACT(url, '//[^/]+[^?#]+)'), REGEXP_EXTRACT(url, 'id=([^&]*)')
3. 문자열을 배열로 분해하기: SPLIT(REGEXP_EXTRACT(url, '//[^/]+([^?#]+)'), '/')[SAFE_ORDINAL(2)]
→ SAFE_OFFSET과 SAFE_ORDINAL의 차이? 일단 SAFE_OFFSET은 0부터, SAFE_ORDINAL은 1부터 시작함!
4. 날짜와 타임스탬프 다루기:
1) 현재 날짜와 타임스탬프 추출: CURRENT_DATE(), CURRENT_TIMESTAMP() - UTC 기준!
2) 지정한 값의 날짜나 시각 데이터 추출: DATE(), TIMESTAMP(), CAST(value AS type), DATE '0000-00-00'
3) 날짜/시각에서 특정 필드 추출: EXTRACT(DAY FROM stamp), SUBSTR(stamp, 9, 2)
5. 결손 값을 디폴트 값으로 대치하기: COALESCE(value, 0)
6강 여러 개의 값에 대한 조작
1. 문자열 연결하기: CONCAT(value1, ' ', value2), '||'
2. 여러 개의 값 비교하기:
1) 음양 확인: SIGN()
2) 컬럼 중 비교: GREATEST(col1, col2, col3), LEAST(col1, col2, col3)
3. 2개의 값 비율 계산하기: 연산
4. 두 값의 거리 계산하기: 유클리드(SQRT(POWER(col1) - POWER(col2))
5. 날짜/시간 계산하기: TIMESTAMP_ADD(stamp, interval 1 hour), TIMESTAMP_SUB(), DATE_DIFF
6. IP 주소 다루기: LPAD
7강 하나의 테이블에 대한 조작
1. 그룹의 특징 잡기: AVG, SUM, MAX, MIN
2. 그룹 내부의 순서: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
- FUNC(col1) OVER(ORDER BY col2 DESC ROWS BETWEEN UNBOWNDED PRECEDING AND CURRENT ROW)
3. 세로 기반 데이터를 가로 기반으로 변환하기: CASE, STRING_AGG()
4. 가로 기반 데이터를 세로 기반으로 변환하기: CROSS JOIN, UNNEST
8강 여러 개의 테이블 조작하기
1. 여러 개의 테이블을 세로로 결합하기: UNION
2. 여러 개의 테이블을 가로로 정렬하기: LEFT JOIN
3. 조건 플래그를 0과 1로 표현하기: CASE
4. 계산한 테이블에 이름 붙여 재사용하기: WITH
5. 유사 테이블 만들기: UNNEST(GENERATE_ARRAY(1,5))
4장 매출을 파악하기 위한 데이터 추출
9강 시계열 기반으로 데이터 집계하기
1. 날짜별 매출 집계하기
2. 이동평균을 사용한 날짜별 추이 보기
SELECT dt, SUM(order_amt) AS total_amt
, AVG(SUM(order_amt)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_avg
, CASE WHEN COUNT(*) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) = 7
THEN AVG(SUM(order_amt)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
END as seven_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt
;3. 당월 매출 누계 구하기: SUM(col1) OVER(PARTITION BY ORDER BY)
4. 월별 매출의 작대비 구하기: SUM(CASE)
5. Z 차트로 업적의 추이 확인하기 SUM(CASE) OVER()
6. 매출을 파악할 때 중요 포인트: 구매횟수, 구매 단가 등을 고려할 것
10강 다면적인 축을 사용해 데이터 집약하기
1. 카테고리별 매출과 소계 계산하기: UNION ALL
2. ABC 분석으로 잘 팔리는 상품 판별하기: SUM() OVER()
3. 팬 차트로 상품의 매출 증가율 확인하기: FIRST_VALUE()
4. 히스토그램으로 구매 가격대 집계하기: MIN, MAX, FLOOR()