[SQL 레시피] 하나의 값 조작하기

데이터를 가공해야하는 이유

1. 다룰 데이터가 데이터 분석 용도로 상정되지 않은 경우
2. 연산할 때 비교 가능한 상태로 만들고 오류를 회피하기 위한 경우 (데이터 형식, NULL)

데이터 가공

1. 코드 값 레이블로 변경하기
   1. CASE 문 활용
2. URL에서 요소 추출하기
   1. net.host 함수 사용 (bigquery)
   2. 정규표현식 활용 - regexp_extract
      • 정규표현식(Regex)의 개념과 기초 문법
      • 정규표현식의 개념과 패턴 사용법 총정리 · Wireframe
      • RegExr: Learn, Build, & Test RegEx
   3. 배열 활용 - split

   select stamp
        , referrer
        , net.host(referrer)   as referrer_host
        , url
        , regexp_extract(url, '//[^/]+([^?#]+)')   as path
        , split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[safe_ordinal(2)] AS path1
        , split(regexp_extract(url, '//[^/]+([^?#]+)'), '/')[safe_ordinal(3)] AS path2
        , regexp_extract(url, 'id=([^&]*)')     as id
   from `sqlrecipe.access_log`
   limit 100;
   

3. 날짜와 타임스탬프
   1. 현재시각 current_date, current_timestamp
   2. 타입변환 CAST stamp AS date, timestamp
   3. EXTRACT(year, month, day, hour FROM stamp)
   4. substr(stamp, 1, 4) AS year
4. 결손 값 디폴트 대치
   1. NULL값은 사칙 연산해도 NULL이 됨
   2. amount - coupon : coupon이 NULL일 경우 NULL이 반환됨
   3. amount - COALESCE(coupon, 0) : coupon이 NULL일 경우 0으로 변환하여 정상 계산

 

References

RegExr: Learn, Build, & Test RegEx

정규표현식의 개념과 패턴 사용법 총정리 · Wireframe

정규표현식(Regex)의 개념과 기초 문법