Big Query_3

Data Type

1. Int64 / Float64

• 64비트 영역의 컴퓨터 메모리에 2진수 형태로 저장됨
• 10진법으로 표현되는 숫자를 정확하게 표현해야 할 시에(재무계산) Numeric 사용

2. Numeric

• 숫자 앞에 NUMERIC을 기재하면 됨
• NUMERIC + 문자열 형태의 숫자

SELECT NUMERIC '1.23' AS pmt

3. String
4. Boolean

• 대문자와 소문자를 구분하지 않음
• IS NOT 사용 가능
• WHERE절과 SELECT 조건식에서 유용하게 사용됨
• IF(평가할 조건(mark IS NULL), 참일 때 반환, 거짓일 때 반환) 만약 SELECT에서 해당 조건을 호출한다면 모든 행에 적용 > 할인가가 적용되는 일부 상품이 존재할 때 사용 가능

5. Timestamp
6. Datetime
7. Geography
8. Struct & Array

Function

1. Scalar

• ROUND
• SUBSTR(str, pos, len) : pos 위치에서 len의 길이 str 반환
• IEEE_DEVIDE(x, y) : 분모가 0일 가능성이 있을 때 사용. 0으로 나누려 하면 NAN(Not a Number)이 반환됨.
• SAFE : 스칼라 함수 앞에 SAFE 접두사를 붙이면 오류를 발생시키지 않고 Null 반환

SELECT SAFE.LOG(10, -3), LOG(10,3)
# Log 음수는 본래 오류가 떠야 하나 Null이 반환됨

2. Aggregate

• MAX
• SUM
• COUNT
• AVG

  GROUP BY와 함께 사용

3. Analytics

• row_number
• rank

4. Table-Valued
   From절에서 사용할 수 있는 결과 집합을 반환하는 함수
   Array에서 UNNEST를 호출한 다음 선택 가능함

5. USER-Defined
   CREATE A FUNCTION

비교연산

• < , <= , > , >= , != (<>)
• Null < NaN < 유효숫자
• Null과 비교시 언제나 Null 반환
• NaN과 비교시 언제나 False 반환

값에 NULL이 있는지 확인하려면 IS 연산자 사용
WHERE <column> vs WHERE <column> IS NOT false
WHERE IS NOT false는 TRUE인 애와 NULL인 애를 반환하지만 WHERE <column>은 (WHERE <column> IS TRUE와 동일) TRUE인 애만 반환함

WHERE <column>

BigQuery에서 &, |은 비트 연산에만 사용 가능

Null값이 있는 데이터일 때

1. IF 조건식
2. COALESCE

• 사용하고자 하는 테이블에 둘 이상의 값이 누락되는 경우 사용
• NULL값이 있으면 기본적으로 세팅되어있는 숫자(ex. 1.2)로 계산되고, NULL값이 아니라면 해당 값(ex. b)가 사용된다
• 단락평가(short circuit : NULL이 아닌 결과를 얻은 후에는 식을 평가하지 않음)을 사용

-- X Table이 존재하고 
 SELECT *, ROUND(COASLESCE(
 a * (1+b) * (1+c), 
 a * 1.2 * (1+c),
 a * (1+b) * 1.2,
 NULL
 ), 2) AS Price
 FROM X

3. IFNULL

• IFNULL(a, b) : a가 NULL이면 b를 반환
• IF(a IS NULL, b, a)와 동일함

SELECT *, ROUND(costPrice*
	(1+ IFNULL(b, 0.5)) * 
    (1+ IFNULL(c, 0.1)),
    2) AS Price
FROM X

Type 변환

1. 명시적 변환 CAST() & SAFE_CAST()

• 예시로, 한 Column에 문자열과 숫자가 있을 경우 Sum 계산이 불가하기 때문에 해당 Column을 INT64로 명시적으로 변환해야 함
• 명시적 타입 변환은 Casting이라 하며 CAST() 함수를 이용함
• 오류 대신 NULL값을 반환하려면 CAST()대신 SAFE_CAST()를 사용

-- X Table이 존재하고 
SELECT SUM(SAFE_CAST(a_column AS INT64)) FROM X

• 결과 값으로 a_column에 SUM이 불가능한 문자열이 존재하는 경우 그를 제외한 INT만 SUM한 값이 나옴
• 만약 모든 행의 데이터가 숫자를 감싸고 있는 문자열이라면 (ex. '24') 그냥 CAST()로도 문제 없이 계산됨

2. 암시적 변환(Coercion, 타입강제)

• 사용하는 데이터와 필요한 데이터 타입이 다르면 자동으로 타입 변환
• BigQuery는
  INT64 > FLOAT64 혹은 NUMERIC으로,
  NUMERIC > FLOAT64
  로만 변환함

문자열 출력

FORMAT() 사용
날짜나 타임스탬프의 경우 FORMAT_DATE, FORMAT_TIMESTAMP 사용

FORMAT('%0.3d', 42) -- 042
FORMAT('%5.3f', 12.4) --12.400
FORMAT('@@%s33', "a") -- @@a33
FORMAT('%s-%02d', "a", 4) -- a-04

True / False Count하기

BigQuery에서는 Boolean을 SUM, AVG로 계산 불가하기 때문에 방법이 필요함
1. 타입 변환

• 타입 변환은 가능하면 사용하지 않는 방법이 추천됨

-- X Table이 존재하고 
SELECT SUM(CAST(a_column AS INT64)) AS num_column FROM X

2. IF 사용

-- X Table이 존재하고 
SELECT SUM(IF(a_column, 1, 0)) AS num_column FROM X

2. COUNTIF 사용 [BEST Option]

-- X Table이 존재하고 
SELECT COUNTIF(a_column) AS num_column FROM X

문자열

1. 문자열 함수
   BigQuery는 내장 문자열 함수를 제공함

• LENGTH(a_col)

• LOWER(a_col)

• `ENDS_WITH(기준문자열, 찾고자하는 문자열)

• `STARTS_WITH(기준문자열, 찾고자하는 문자열)

• STRPOS(a_col, '찾고자 하는 문자열') : 찾고자 하는 문자열 위치 값 나옴. 없으면 0값 나옴

• CONCAT() : 입력값 연결

• SUBSTR(str, pos, len) : 부분 문자열을 추출 > pos의 위치에서 len만큼의 문자열을 읽음

SELECT(CONCAT(SUBSTR(email, 1, STRPOS(email, '@') -1, 'from', city) AS name_city

2. 문자별 타입 변환
   BigQuery의 문자열은 Unicode. 문자별로 소문자/대문자 유무가 다르고 UTF-8인코딩이 되지 않는 문자열로 존재하기 때문에 어떤 문자열을 다루는지 확인 후에 적절하게 사용함이 필요함.
   BigQuery가 문자열을 나타내는 방법은 아래의 3가지

• 유니코드 문자 배열 > CHAR_LENGTH() 로 길이 확인
• CAST(a_column AS BYTES)바이트 배열 > BYTE_LENGTH() 로 길이 확인
• TO_CODE_POINTS() 유니코드 코드 포인트 배열(INT64) > ARRAY_LENGTH() 로 길이 확인

3. 문자열 변환

• LPAD(기준문자열, 총 문자열 length, 왼쪽에 추가하고자 하는 문자)
• RPAD(기준문자열, 총 문자열 length, 오른쪽에 추가하고자 하는 문자)
• LTRIM(기준문자열) : 왼쪽의 공백 제거
• RTRIM(기준문자열) : 오른쪽 공백 제거
• TRIM(기준문자열, 일부 문자열만 제거하고싶을 시 해당 문자열 기재) : 양쪽 공백 제거
• REVERSE(기준문자열) : 문자열 앞 뒤 바꿈

TRIM('**street', '*') -- street

4. 정규표현식

• REGEXP_CONTAINS(column, r'')
• REGEXP_EXTRACT
• REGEXP_EXTRACT_ALL
• REGEXP_REPLACE

TIMESTAMP

1. TIMESTAMP 파싱
   BigQuery는 문자열 표현의 날짜, 시간 부분은 ISO 8601에 따라 T 또는 공백으로 분리 가능

• 2021-01-01 12:00:00(UTC기준)은 2021-01-01 13:00:00+1:00 과 동일한 시간임
• TIMESTAMP_DIFF(기준1, 기준2, Diff 기준)
• 표준 형식이 아닌 문자열을 파싱할 시 PARSE_TIMESTAMP() 사용
• 원하는 형식으로 타임스탬프를 출력할 시 FORMAT_TIMESTAMP

  PARSE_TIMESTAMP
  1) %Y%m%d-%H%M%S 2) %c3)%x %X`
  TODO 정확히 확인하기

2. 달력 정보 추출

• EXTRACT(DAYOFWEEK FROM a_column)
• EXTRACT(YEAR FROM a_column)
• EXTRACT(WEEK FROM a_column) : 한 주의 시작은 일요일. 이스라엘과 같이 토요일이 한 주의 시작인 국가는 EXTRACT(WEEK('SATURDAY') FROM a_column)으로 지정

3. TIMESTAMP to UNIX TIME

• UNIX_MILLIS(TIMESTAMP, '타임스탬프 정보')
• TIMESTAMP_MILLIS(유닉스 시간 정보)

4. TIMESTAMP 연산

• TIMESTAMP_ADD(a_col, INTERVAL 1 HOUR)
• TIMESTAMP_SUB(a_col, INTERVAL 10 MINUTE)
• TIMESTAMP_DIFF(A타임스탬프, B타임스탬프, SECOND)

5. BigQuery 시간 표시

• DATE, TIME, DATETIME사용
• DATETIME타입에는 TIMESTAMP 함수 사용 가능하며, 서로 호환/변환 가능함
• DATETIME타입에는 날짜와 시간을 구분하는 T가 사용되며, TIMESTAMP는 공백을 사용함
• DATETIME타입에는 시간대가 내포되어있고, TIMESTAMP는 시간대를 포함하고 있음(UTC 포함)
• 보통 TIMESTAMP와 DATE가 많이 사용되지만, (다른 SQL에서)TIMESTAMP는 유닉스 시간의 한도인 1970년도 to 2038년도까지만 사용 가능하기 때문에 60대 이상의 나이 계산이 불가함