[Bigquery] 데이터 타입, 함수, 연산자

0. 문제를 풀어볼까유!

0.1. 모의 갯수를 세보자

CREATE TABLE words (
  word_id INT PRIMARY KEY,
  word VARCHAR(50)
);

INSERT INTO words (word_id, word) VALUES
  (1, 'apple'),
  (2, 'korea'),
  (3, 'www'),
  (4, 'sql study'),
  (5, 'happy');

WITH words AS (
  SELECT 1 AS word_id, 'apple' AS word
  UNION ALL
  SELECT 2, 'korea'
  UNION ALL
  SELECT 3, 'www'
  UNION ALL
  SELECT 4, 'sql study'
  UNION ALL
  SELECT 5, 'happy'
)

SELECT word, LENGTH(word) - LENGTH(REGEXP_REPLACE(word, r'[aeiou]', ''))
FROM words

-- 'a', 'e', 'i', 'o', 'u'를 모음으로 간주합니다.
SELECT
  word,
  LENGTH(word) - LENGTH(REPLACE(word, 'a', '')) -
  LENGTH(REPLACE(word, 'e', '')) -
  LENGTH(REPLACE(word, 'i', '')) -
  LENGTH(REPLACE(word, 'o', '')) -
  LENGTH(REPLACE(word, 'u', '')) AS 모음의_갯수
FROM
  words;

0.2. User-id를 만들어보자

WITH user_id AS (
  SELECT 'ab@naver.com' AS email, 'holly' AS name, '010-1234-5678' AS phone
  UNION ALL
  SELECT 'cde@gmail.com' AS email, 'kimjm' AS name, '010-7823-1234' AS phone
  UNION ALL
  SELECT 'fgi@likelion.org' AS email, 'jwon' AS name, '010-4321-8432' AS phone
)

SELECT CONCAT(REGEXP_EXTRACT(email, r'(.*)+@'), LEFT(name,1), RIGHT(phone,4))
FROM user_id

--- 방법2 : SUBSTR(EMAIL, 1, STRPOS(EMAIL, '@')-1)

1. 데이터 타입, 함수, 연산자

1.1. IEEE_DIVIDE()

• 0으로 나누려고 하면 NaN 특수 부동소수점 숫자를 반환함

• NaN은 null보다 큰 값으로 평가됨

SELECT *, ROUND(IEEE_DIVIDE(oneways, numrides),2) AS frac_oneway
FROM example

1.2. COALESCE()

• Null이 아닌 첫번째 값을 반환

SELECT A, B, COALSESCE(A, B) FROM table;

• Null이 아니면 해당 값을, NULL인 경우 0을 리턴

SELECT div, COALESCE(div, 0) FROM table;

1.3. CAST( AS ), SAFE_CAST( AS )

• 타입 강제는 INT64 → FLOAT64 및 NUMERIC, NUMERIC → FLOAT64

SELECT SUM(SAFE_CAST(hours_worked AS INT64)) FROM example

1.4. SUBSTR(), STRPOS()

• SUBSTR() : 문자열에서 특정 위치를 기준으로 지정된 길이만큼 문자열을 추출하는 함수

SELECT SUBSTR('Hello, World!', 7, 5) AS extracted_string;
-- 결과: "World"

• STRPOS() : 문자열에서 특정 부분 문자열이 처음으로 등장하는 위치를 찾는 함수

SELECT STRPOS('Hello, World!', 'World') AS position;
-- 결과: 8

1.5. ST_GeogPoint()

• GIS함수 : 경도, 위도에 따라 어느 주에 있는지 결과값 반환

SELECT state_name
FROM table
WHERE ST_CONTAINS(state_geom, ST_GeogPoint(-122.33, 47.61))

--- 결과값 : Washington

2. 데이터 가공을 위한 SQL

[SQL] SQL에서 정규표현식 활용하기

이번 포스팅에서는 정규표현식에 대한 개념과 SQL에서의 활용법에 대해 다룰 예정입니다. 0. 들어가며 SQL을 활용하다보면 where절에 문자열을 다루는 조건을 넣어 데이터를 추출하게 되는데요. 보통은 like 를 활용하거나 substr 등을 활용해서 데이터를 추출하게 됩니다. 그런데 조금 더 복잡한 규칙을 찾아내서 그에 해당하는 문자열을 뽑아올 때에는 and 조건이나 or조건을 이용하게 됩니다. 이렇게 되면 쿼리 작성을 할 때 귀찮게 반복을 하게 되고 따라서 쿼리문의 길이가 길어지죠. 이러한 부분을 어느정도 해결할 수 있는 방법이 바로 '정규표현식'을 활용하는 것입니다. 그러니 한번 정규표현식에 대해 한번 알아겠습니다. 1. 정규표현식이란? 정규표현식 (Regular Expression) - 특정한 규..

https://schatz37.tistory.com/39#head7

2.1. URL에서 경로와 요청 매개변수 값 추출

SELECT stamp, url,
	regexp_extract(url, '//[^/]+([^?#]+)') path,
	regexp_extract(url, 'id=([^&]*)') id

3. 지원님 문제 (REGEXP 연습)

with employees as (
  select 'john_doe@example.com' as email
  union all select 'jane-123@example.com'
  union all select 'mark@example.com'
  union all select 'emily.davis@example.com'
  union all select 'michael*lee@example.com'
)

SELECT *
FROM employees 
WHERE REGEXP_CONTAINS(email, r'^[a-z][a-z0-9_-]+@example\.com$'

with employees as (
  select 'john_doe@example.com' as email
  union all select 'jane-123@example.com'
  union all select 'mark@example.com'
  union all select 'emily.davis@example.com'
  union all select 'michael*lee@example.com'
)

SELECT REGEXP_EXTRACT(email, r'(.+)@example.com')
FROM employees
WHERE REGEXP_CONTAINS(email, r'^[a-z][a-z0-9_-]+@example\.com$'

WITH products AS (
  SELECT "Apple iPhone 12 (64GB)" AS product_name UNION ALL
  SELECT "Samsung Galaxy S21 (128GB)" UNION ALL
  SELECT "Google Pixel 5 (Black)"
)

SELECT REGEXP_REPLACE(product_name, r'\(\w.*\)|\d+', '') AS modified_product_name
FROM products;