문자형 데이터

• 자주 사용하는 문자형 데이터 함수

  함수	설명	활용 예시
  INSTR	* LOCATE 함수 대신 사용 (LOCATE(”A”, “ABC”) - 만약 찾는 문자가 없다면 0을 반환- 문자가 여러개라면 가장 먼저 찾은 문자의 위치 반환	SELECT INSTR("ABC","B");
  SUBSTRING	“ABC”에서 2번째 문자부터 반환	SELECT SUBSTRING("ABC",2);
  RIGHT	오른쪽에서 1번째 문자까지 반환	SELECT RIGHT("ABC",1);
  LEFT	왼쪽에서 1번째 문자까지 반환	SELECT LEFT("ABC",1)
  UPPER	대문자로 바꿔 변환	SELECT UPPER("abc");
  LOWER	소문자로 바꿔 변환	SELECT LOWER("ABC");
  LENGTH	글자 수 반환	SELECT LENGTH("abc");
  CONCAT	"ABC"문자열과 "DEF"문자열을 합쳐 반환	SELECT CONCAT("ABC","DEF");
  REPLACE	"ABC"의 "A"를 "Z"로 바꿔 전환	SELECT REPLACE("ABC","A","Z");

  -- INSTR
  SELECT name, INSTR(name,'e')
  FROM BigQuery.mypokemon;
  
  -- SUBSTRING
  SELECT name, SUBSTRING(name, 3),SUBSTRING(name, 10)
  FROM BigQuery.mypokemon;
  -- 만약 입력한 숫자가 문자열의 길이보다 크다면, 아무것도 가져오지 않음
  
  -- RIGHT & LEFT
  SELECT name, RIGHT(name,3), LEFT(name,3)
  FROM BigQuery.mypokemon;
  
  -- LENGTH
  SELECT name, LENGTH(name)
  FROM BigQuery.mypokemon;
  
  -- CONCAT
  SELECT name, CONCAT(LEFT(name,1),RIGHT(name,1)) AS first_last
  FROM BigQuery.mypokemon;
  

숫자형 데이터

• 자주 사용하는 대표 숫자 관련 함수

  함수	설명	활용 예시
  ABS	숫자의 절댓값 반환	ABS(숫자)
  CEILING	숫자를 정수로 올림해서 반환	CEILING(숫자)
  FLOOR	숫자를 정수로 내림해서 반환	FLOOR(숫자)
  ROUND	숫자를 소수점 자릿수까지 반올림해서 반환	ROUND(숫자,자릿수)
  TRUNCATE	숫자를 소숫점 자릿수까지 버림해서 반환	TRUNCATE(숫자,자릿수)
  POWER	숫자A의 숫자B 제곱 반환	POWER(숫자A, 숫자B)
  MOD	숫자A를 숫자B로 나눈 나머지 반환- 2로 나누면 짝수인지 홀수인지 확인 가능	MOD(숫자A, 숫자B)

날짜형 데이터

-- CURRENT_DATETIME/ CURRENT_DATE/ CURRENT_TIME
-- X값 입력이 필요없는 함수
SELECT CURRENT_DATETIME(), CURRENT_DATE(), CURRENT_TIME();

• BigQuery에서는 Year함수를 직접 사용할 수 없음
  • 대신 “EXTRACT” 함수를 사용해서 연도를 추출해야함

    SELECT YEAR(CURRENT_DATETIME()); -- 빅쿼리에서는 이런식으로 사용할 수 없음
    
    -- 빅쿼리용
    SELECT EXTRACT(YEAR FROM CURRENT_DATETIME());

• BigQuery에서는 MONTHNAME을 사용할 수 없음

  • FORMAT_DATE함수를 사용해야 함

      SELECT CURRENT_DATE() AS NOW,
      EXTRACT(YEAR FROM CURRENT_DATE()) AS YEAR,
      EXTRACT(MONTH FROM CURRENT_DATE()) AS MONTH,
      FORMAT_DATE('%B',CURRENT_DATE()) AS MONTHNAME,
      FORMAT_DATE('%H',CURRENT_DATETIME()) AS HOUR
      FORMAT_DATE('%a',CURRENT_DATE()) AS DAYNAME;
      
      -- FORMAT_DATE함수를 사용할때에는 함수내에 CURRENT_DATE를 사용해야하는지, 
      -- CURRENT_DATETIME을 사용해야하는지 생각하고 적절하게 사용하기

  • 참고) ‘%B’ 부분에 들어갈 수 있는 여러 옵션
    

• 시간추출

  SELECT CURRENT_DATE() AS NOW,
  FORMAT_DATE('%H',CURRENT_DATETIME()) AS HOUR,
  FORMAT_DATE('%M', CURRENT_DATETIME()) AS MINUTE;

  • 이렇게 쿼리를 짜면, 빅쿼리는 UTC(협정 세계시)를 사용하므로 결과가 한국시간대와 다름

    -- CURRENT_DATETIME에 'Asia/Seoul'이라고 정확하게 명시
    SELECT CURRENT_DATE() AS NOW,
    FORMAT_DATE('%H',CURRENT_DATETIME('Asia/Seoul')) AS HOUR,
    FORMAT_DATE('%M', CURRENT_DATETIME('')) AS MINUTE;

• DATE_DIFF

  • 빅쿼리에서는 다른 sql함수와 다르게 ‘DATEDIFF’함수 대신 ‘DATE_DIFF’ 함수를 사용해야 함
    • 주의할 점
      • DATE_DIFF함수가 날짜형(DATE) 데이터만을 처리함
        • 만약 시간을 포함한 타임스탬프(’TIMESTAMP’)데이터를 사용하고 싶다면, 먼저 TIMESTAMP를 DATE로 변환해야함