[TIL] BigQuery SQL 명령어 정리

 

BigQuery 모든 명령어 모아놓은 사이트

APIs and reference | BigQuery | Google Cloud

API 및 참조  |  BigQuery  |  Google Cloud

Introduction to SQL in BigQuery | Google Cloud

BigQuery의 SQL 소개  |  Google Cloud

 

SELECT

데이터 조회

 

# 연산자 사용 가능
select 1 + 1;

# 문자열 출력 가능
select 'hello world';

# 문자열을 여러개 출력하고 싶을 때 BigQuery에선 아래와 같이 사용
select 'hello', 'world'

# from을 이용하여 테이블에서 원하는 column만 선택해서 조회 가능
select first_name, last_name
from 'thelook_ecommerce.user';

 

AS

해당 컬럼의 이름을 별칭을 정하여 보여주는 기능

 

select name as product_name
from `thelook_ecommerce.products`;

 

 

# 테이블명을 지정하는 다양한 방법
select 
  a.id,
  a.name
from `thelook_ecommerce.products` as a;

select 
  id,
  name
from `thelook_ecommerce.products` a;

 

LIMIT

조회할 결과의 레코드 수 제한

 

select * from `thelook_ecommerce.users` limit 5;

 

 

DISTINCT

결과에서 중복되는 행 제거

 

# country의 중복된 값을 제외하고 출력
select distinct country
from `thelook_ecommerce.users`;

 

WHERE

데이터에 조건을 부여해서 원하는 데이터만 필터링한 결과 조회
여러 연산자를 결합하여 사용 가능
결합 가능한 연산자 종류 : 비교연산자(=, <, >, !=, >=, <=), SQL 연산자(BETWEEN), 논리 연산자(AND, OR) 등

 

# first_name이 Michael인 사람의 데이터만 조회
SELECT *
FROM `thelook_ecommerce.users` 
WHERE first_name = 'Michael';

 

SQL 연산자

BETWEEN

BETWEEN A AND B : A와 B를 포함한 사이의 값

 

# 나이가 20이상, 30이하인 user의 데이터 조회
select * 
from `thelook_ecommerce.users`
where age between 20 and 30;

# 1월에 가입한 유저의 데이터 조회
select *
from `thelook_ecommerce.users`

# 날짜의 경우 날짜만 적으면 뒤에 00:00:00 이 생략 된 상태라서 그 다음날로 지정해주어야 함 
where created_at between '2020-01-01' and '2020-02-01'

 

IN

IN A : A 안에 값과 일치하는 데이터 조회

 

select * 
from `thelook_ecommerce.products`
where brand in ('Onia', 'Hurley', 'Matix');

 

 

LIKE

비교문자로 비교 문자와 형태가 일치하는 데이터 조회
대소문자를 가리지 않고 주로 %를 사용하여 비교한다.

 

# 이름에 young이 포함된 레코드 조회
select * 
from `thelook_ecommerce.products`
where name like '%Young%';

# 이름이 Hurley로 시작되는 레코드 조회
where name like 'Hurley%';

# 이름이 T-shirt로 끝나는 레코드 조회
where name like '%T-shirt';

# _를 사용해 앞이나 뒤에 몇 글자가 있는지 확인 (_ 하나당 1글자)
# Da로 시작하고 뒤에 3글자가 있는 레코드 조회
where first_name like 'Da___'

 

NULL

NULL 값을 갖는 레코드 조회

 

# shipped_at이 NULL인 레코드 조회
select * 
from `thelook_ecommerce.order_items`
where shipped_at IS NULL;

# shipped_at이 NULL이 아닌 레코드 조회
where shipped_at IS NOT NULL;

# 조회하려는 레코드가 문자 타입일 경우
select ANIMAL_ID
from ANIMAL_INS 
where NAME = 'NULL'

 

집계함수

여러 행으로부터 하나의 결과값을 반환하는 함수

 

• COUNT : 해당 레코드의 개수 반환
  • COUNT(*)일 경우 NULL값이 있어도 개수에 포함되서 반환
  • COUNT(컬럼명)일 경우 NULL값은 제외하고 개수 반환 
• SUM : 해당 레코드 값의 합계 반환
• AVG : 해당 레코드 값의 평균 반환
• MAX : 해당 레코드의 최댓값 반환
• MIN : 해당 레코드의 최솟값 반환
• VARIANCE : 해당 레코드의 분산 반환 
• STDDEV : 해당 레코드의 표준편차 반환

📎 분산과 표준편차란?

[기초통계] 분산이란? 표준편차란? (What is Variance and Standard Deviation?)

 

GROUP BY

특정 항목을 기준으로 그룹화하여 조회
데이터를 그룹화하여 조회할 때 그룹화 하려는 항목이 select에 들어가야 한다.

 

# 국가별 유저수
select country, count(id) 
from `thelook_ecommerce.users` 
group by country;

# 카테고리별 상품판매가 합계를 별칭 sum_retail_price으로 출력
select 
	category, 
	sum(retail_price) as sum_retail_price  
from `thelook_ecommerce.products` 
group by category

# 카테고리별 비용 평균
select category, avg(cost)
from `thelook_ecommerce.products`
group by category;

# 성별 평균 나이
select 
	gender, 
	avg(gender)
from `thelook_ecommerce.users`
group by gender;

 

HAVING

그룹화된 데이터에 조건 부여
GROUP BY와 함께 사용

 

# 유저를 국가별로 그룹화하고 국가별 유저수가 4000 이상인 국가와 유저수 조회
select 
  country, 
  count(id) as user_count
from `thelook_ecommerce.users`
group by country
having user_count >= 4000;

 

ORDER BY

출력 결과 정렬
오름차순 : ASC (생략 가능)
내림차순 : DESC

 

# 1차 정렬로 나이를 내림차순, 2차 정렬로 id 오름차순으로 조회
select 
  country, 
  count(id) as user_count
from `thelook_ecommerce.users`
group by country
having count(id) >= 4000;

 

쿼리문 작성 순서

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

 

SQL 함수 

미리 정의된 기능 모음으로 단일 행 함수와 그룹 함수가 있다.
BIGQUERY 데이터 타입 함수로는 숫자(INT), 문자(STRING)이 있다.

 

숫자 함수

• ROUND(반올림할 숫자, 자릿수) : 해당 레코드의 숫자를 반올림하여 출력 (0이 소수점 첫째자리)
• TRUNC(숫자, 자릿수) : 해당 레코드의 숫자를 내림(절삭)하여 출력
• MOD(숫자, 나눌 값) : 해당 레코드의 숫자의 나머지 출력 (자바의 %와 동일)
• POWER(숫자, 승수) : 해당 레코드의 숫자를 제곱하여 출력
• SQRT(숫자) : 해당 레코드의 제곱근 출력

 

문자열 함수

• SURSTR(문자열, 시작 위치, 길이) : 문자열의 일부 출력 (자바와 다르게 첫번째 자리가 1)
• LEFT(문자열, 길이) : 문자열을 왼쪽에서 얼만큼 자를 지 설정 후 조회
• RIGHT(문자열, 길이) : 문자열을 오른쪽에서 얼만큼 자를 지 설정 후 조회
• CONCAT : 여러 문자열을 하나로 연결

select concat(first_name, ',', last_name)
from `thelook_ecommerce.users`;

# 또는 연결연산자 (||) 사용
select first_name || ',' || last_name
from `thelook_ecommerce.users`;

concat 사용 예시

• LOWER : 문자열을 모두 소문자로 변경
• UPPER : 문자열을 모두 대문자로 변경
• INITCAP : 맨 앞에 문자만 대문자로 변경
• REPLACE(대상 값, 바꾸고 싶은 값) : 대상 값을 바꾸고 싶은 값으로 변경
• LENGTH : 문자열의 길이 출력
• INSTR(문자열, 찾을 문자열) : 문자열의 위치 출력 (Index 1부터 시작)
• IFNULL(컬럼명, 지정할 문자열) : 해당 컬럼에 NULL값이 있는 경우 지정할 문자열로 변경
• ASCII : 아스키코드 번호로 리턴 

 

형 변환 CAST

# 문자열 -> 숫자로 바꾸는거
# 문자열 -> 자연수(INTEGER)
# 문자열 -> FLOAT

select CAST('123' AS INT64)
select '123' + '123' # 에러
select CAST('123' AS INT64) + CAST('123' AS INT64)
select CAST('123.123' AS FLOAT64)
select CAST('123' AS NUMERIC)
select CAST('123.123' AS NUMERIC)

# 숫자(INTEGER) -> 문자
# 숫자(FLOAT) -> 문자
# true, false -> 문자
select CAST(123 AS STRING)
select CAST(123.123 AS STRING)
select CAST(true AS STRING)
select CAST(false AS STRING)
select CAST(NULL AS STRING)

# 날짜 타입
# 1) DATE
# 문자열 -> DATE
# 2) DATETIME
# 문자열 -> DATETIME
select DATE('2011-12-01 11:12:34')
select DATETIME('2011-12-01 11:12:34')

 

날짜 함수

# 날짜 : DATE()
# 시간 : DATETIME()

# bigquery에서는 current_date()로 현재 날짜를 얻을 수 있다.
SELECT CURRENT_DATE();

# 다른 SQL 에서는?
SELECT DATE();

# timezone을 설정
SELECT DATE('2023-1-1', 'Asia/Seoul')

# YEAR는 년도, MONTH는 월, DAY는 일, DAYOFWEEK는 요일(일요일이 1)
# EXTRACT는 특정 날짜, 시간 값에서 원하는 부분 출력하는 함수
SELECT EXTRACT(YEAR FROM DATE '2023-1-1');
SELECT EXTRACT(MONTH FROM DATE '2023-1-1');
SELECT EXTRACT(DAY FROM DATE '2023-1-1');
SELECT EXTRACT(DAYOFWEEK FROM DATE '2023-1-1');

 

시간 함수

# 현재 시간 출력
SELECT CURRENT_DATETIME()

# DATETIME을 통해 시간 객체 생성 가능
# DATETIME(year, month, day, hour, minute, second)
# DATETIME(date_expression[, time_expression])
# DATETIME(timestamp_expression [, time_zone])
# 위와 같은 형식으로 작성

SELECT DATETIME(2023, 1, 25, 05, 30, 00)
SELECT DATETIME(TIMESTAMP "2023-12-25 05:30:00+00", "Asia/Seoul")

# 데이터 추출
SELECT EXTRACT(HOUR FROM CURRENT_DATETIME());
select extract(hour from created_at) from `thelook_ecommerce.users`
select extract(minute from created_at) from `thelook_ecommerce.users`
select extract(second from created_at) from `thelook_ecommerce.users`

 

FORMAT_DATE, FORMAT_DATETIME

지정된 format_string에 따라 날짜(date_expr)나 시간(datetime_expr) 변경

 

모든 날짜 형식 확인

Format elements  |  BigQuery  |  Google Cloud

 

 

# %x는 날짜를 MM/DD/YY 형식으로 표현
SELECT FORMAT_DATE('%x', DATE '2023-1-25') AS US_format;
SELECT FORMAT_DATE('%b-%d-%Y', DATE '2023-1-25') AS US_format;
SELECT FORMAT_DATE('%y/%m/%d', DATE '2023-1-25') AS KR_format;

SELECT FORMAT_DATETIME('%y/%m/%d %T', DATETIME '2023-1-25 15:30:00') AS KR_format;

 

DATE_DIFF

두 날짜 간에 지정된 date part 간격의 정수를 반환
date_part는 YEAR, MONTH, DAY 등 지정 가능
첫 번째 날짜가 두 번째 날짜보다 이전이면 음수로 출력

 

SELECT DATE_DIFF(DATE '2023-9-3', DATE '2023-1-1', DAY) AS days_diff; #245
SELECT DATE_DIFF(DATE '2033-9-3', DATE '2023-1-1', YEAR) AS days_diff; #10

# 또는
SELECT '2023-9-3'-'2023-1-1'

 

DATE_ADD, DATE_SUB, DATETIME_ADD, DATETIME+SUB

지정된 날짜, 시간 간격을 추가 및 빼는 함수

 

SELECT DATE_ADD(DATE '2023-1-25', INTERVAL 5 DAY); # 5일 뒤
SELECT DATE_ADD(DATE '2023-1-25', INTERVAL 5 MONTH); # 5개월 뒤
SELECT DATE_ADD(DATE '2023-1-25', INTERVAL 5 YEAR); # 5년 뒤

SELECT DATE_SUB(DATE '2023-1-25', INTERVAL 5 DAY); # 5일 전
SELECT DATE_SUB(DATE '2023-1-25', INTERVAL 5 MONTH); # 5개월 전
SELECT DATE_SUB(DATE '2023-1-25', INTERVAL 5 YEAR); # 5년 전

SELECT DATETIME_ADD(DATETIME "2023-12-25 15:30:00", INTERVAL 10 MINUTE); # 10분 후
SELECT DATETIME_SUB(DATETIME "2023-12-25 15:30:00", INTERVAL 10 MINUTE); # 10분 전

# 그 외 사용 가능한 함수
# MICROSECOND
# MILLISECOND
# SECOND 
# MINUTE
# HOUR
# DAY
# WEEK
# MONTH
# QUARTER
# YEAR

 

조건분기

# MySQL의 경우 IF, CASE WHEN 사용

# 기본 예시
SELECT
	CASE 
		WHEN true THEN '참입니다'
	ELSE
		'거짓입니다'
	END
# 참입니다 출력

# then 사용
# WHEN이 한 번 들어가면 일반적인 if문, WHEN이 여러 개 들어가면 일반적인 else-if문

SELECT 
CASE 
	WHEN floor = 1 THEN '1층 입니다.' 
	WHEN floor = 2 THEN '2층 입니다.'
	WHEN floor = 3 THEN '3층 입니다.'
	WHEN floor = 4 THEN '4층 입니다.'
ELSE 
	'층수가 없어요' 
END;

 

배송 상태를 조건분기를 사용하여 출력해보기

select order_id, user_id,
case 
  when status = 'Shipped' then '배송됨'
  when status = 'Processing' then '처리중'
  when status = 'Returned' then '반품됨'
  when status = 'Cancelled' then '취소됨'
  when status = 'Complete' then '완료됨'
  else '기타'
end as status_text
from `thelook_ecommerce.orders`
order by order_id;

 

IF

select
  if(true, '참입니다', '거짓입니다.'); # 참입니다 출력
  
is별 cost가 50 이하이면 저비용, 50 이상이면 고비용으로 cost_level 테이블로 출력
select 
  id,
  cost,
  if(cost <= 50, '저비용', '고비용') as `cost_level`
from `thelook_ecommerce.products`
order by id

 

JOIN

기준을 가지고 데이터를 합치는 함수 

 

 

INNER JOIN

두 테이블 모두에서 일치하는 값이 있는 행 반환 (교집합)

 

select 
	orders.order_id, 
	users.name, 
	orders.created_at
from `weniv.weniv_order` as orders
inner join `weniv.weniv_user` as users
on orders.user_id = users.id

 

LEFT JOIN, RIGHT JOIN

왼쪽 조인 기준으로 왼쪽 테이블은 오른쪽 테이블의 해당 행과 함께 왼쪽 테이블의 모든 행 반환
일치하는 행이 없으면 NULL이 두 번째 테이블의 값으로 반환 (오른쪽은 반대)
즉, 그냥 JOIN 시 NULL값을 제외하고 반환하지만 LEFT JOIN, RIGHT JOIN은 NULL값을 포함해서 반환

 

# 해당 코드는 왼쪽 조인
select 
	orders.order_id, 
  orders.created_at,
	users.name
from `weniv.weniv_order` as orders
left join `weniv.weniv_user` as users
on orders.user_id=users.id
order by orders.order_id

 

 

FULL JOIN

전체 조인은 두 번째 테이블에 일치하는 행이 없으면 두 테이블의 모든 행을 반환하고 NULL이 반환
즉, 양쪽에 일치하는 값이 없어도 모든 행을 반환

 

CROSS JOIN

교차 조인은 두 테이블에서 가능한 모든 행 조합을 반환