2. BigQuery SQL 문법

2.1. DQL

2.1.1. DQL 구성요소

SELECT                          # 데이터 조회
      column1
    , column2
FROM Table1 AS alias            # 별칭(alias) 지정하여 데이터 추출
WHERE condition1                # 조건(condition1)을 지정하여 데이터 필터링
GROUP BY column[1|2]            # 원하는 컬럼명 그룹화
HAVING condition2               # 필터링 된 데이터 기준으로 조건(condition2) 지정
ORDER BY column[1|2][ASC|DESC]  # 원하는 컬럼명 기준 결과 정렬
LIMIT limiting number;          # 데이터 결과 갯수 제한

• SELECT
SELECT 는 데이터를 조회하기 위한 SQL 키워드입니다. SELECT절에 조회하고자 하는 컬럼명을 리스트로 나열합니다. 모든 컬럼을 조회하려면 *(Asterisk)을 사용합니다.
 

• FROM
FROM 은 데이터를 탐색하거나 조작할 대상 테이블(Table)을 정의하는 구성요소입니다. FROM절에 `데이터셋.테이블명` 형태로 데이터를 조회 할 테이블의 이름을 지정해 줍니다. 여러 테이블의 조인, 서브쿼리, 테이블 별칭 지정 등 다양한 기능을 제공합니다.
 

• WHERE
WHERE절은 행에 대한 필터링 조건을 지정하여 특정 조건을 만족하는 행만 조회하기 위한 키워드입니다. WHERE절에서 비교연산자와 논리연산자를 활용하여 조건을 설정함으로써 원하는 데이터를 효과적으로 조회할 수 있습니다.
• 비교 연산자를 사용하여 조건을 지정할 수 있습니다. a= b : a는 b와 같음 a = b
a > b : a는 b보다 큼
a < b : a는 b보다 작음
a >= b : a는 b보다 크거나 같음
a <= b : a는 b보다 작거나 같음
a <> b : a는 b와 같지 않음
a != b : a는 b와 같지 않음
• 논리 연산자를 사용하여 여러 조건을 결합하거나 결과를 반전시킬 수 있습니다.
a AND b : a와 b 두 조건이 모두 TRUE일 경우, TRUE를 반환
a OR b : a와 b 두 조건 중 한 조건이라도 TRUE일 경우, TRUE를 반환
NOT a : 주어진 조건 a의 결과를 반대로 반환 (a가 TRUE이면 FALSE, a가 FALSE이면 TRUE)
• 문자열, 날짜, 수치 등의 함수를 사용하여 조건을 세밀하게 설정할 수 있습니다.
 

• AS
AS 는 테이블명과 컬럼명을 별칭 하는 데 사용합니다. SELECT절에서 컬럼명 뒤에 AS키워드와 별칭을 지정하여 사용합니다. 별칭은 결과셋에서 해당 컬럼의 이름으로 사용됩니다.
FROM절에서는 테이블명 뒤에 AS키워드와 별칭을 지정하여 사용합니다. SELECT, WHERE, GROUP BY, ORDER BY 등 구문에서 FROM절에서 별칭 한 테이블을 참조 시 ‘별칭.테이블명’ 형식으로 사용합니다.
 

• GROUP BY
GROUP BY는 지정한 컬럼을 기준으로 결과를 그룹화합니다. GROUP BY절에 그룹화 할 컬럼명 리스트를 “’column1, column2,..” 형식으로 순서대로 나열해 줍니다.
 

• HAVING
HAVING은 앞의 GROUP BY절에서 그룹화 한 결과에 대해 특정 조건을 적용하여 필터링 하는데 사용합니다.
HAVING은 그룹화 된 데이터를 필터링하고, WHERE은 개별 행에 대해 필터링을 수행한다는 점에서 차이가 있습니다.
 

• ORDER BY
ORDER BY 는 SQL 질의의 결과를 특정 컬럼 또는 여러 컬럼을 기준으로 정렬합니다. 여러 컬럼을 기준으로 정렬하고자 할 경우 “column1, column2, ..” 형식으로 컬럼명을 나열해줍니다. 첫 번째 열을 기준으로 정렬한 후, 동일한 값이 있을 경우 두 번째 열을 기준으로 추가 정렬을 수행하고, 이를 계속 반복합니다.
기본적으로 오름차순(’ASC’)으로 정렬되며, 내림차순으로 정렬하려면 ‘DESC’ 키워드를 사용합니다.
 

• LIMIT
LIMIT은 반환할 행의 최대 수를 지정하여 결과를 제한합니다. 제한할 행(limiting number)의 시작 인덱스는 0부터 시작하며, 반환할 행의 수를 지정하여 결과의 범위를 조절할 수 있습니다.

2.1.2. DQL 쿼리 실행 순서

1. FROM: 데이터를 조회할 기본 테이블을 정의합니다.

2. WHERE: 특정 조건을 만족하는 행을 필터링합니다.

3. GROUP BY : 지정된 열을 기준으로 결과를 그룹화합니다.

4. HAVING: WHERE절에서 행을 필터링 후 그룹화된 결과에서 추가적인 조건을 지정합니다.

5. SELECT: 조회할 열을 지정하고, 필요한 경우 계산된 열을 정의합니다.

6. ORDER BY: 지정된 열을 기준으로 결과를 정렬합니다.

7. LIMIT: 결과의 행 수를 제한합니다.

2.1.3. DQL의 기능

• 기본 조회 구문

SELECT 
      column1
		, column2 
FROM Table1 AS alias;

SELECT
      e.first_name
		, e.last_name
FROM `HR.employees` AS e;

SELECT 
			e.first_name
		, d.department_name 
FROM `HR.employees` AS e; 

SELECT 
			* 
FROM `HR.employees` e;

SELECT
			column1 AS alias
FROM Table1;

SELECT 
			salary AS `월급`
FROM `HR.employees`;

• 조건부 조회 구문

SELECT 
			column1
		, column2 
FROM Table1
WHERE condition;

SELECT 
			first_name AS `이름`
		, last_name AS `성`
		, hire_date AS `입사일자`
		, salary AS `월급`
FROM `HR.employees`
WHERE salary >= 15000;

SELECT 
			* 
FROM `HR.departments`
WHERE department_name = 'IT';

• 정렬 후 조회 구문

SELECT 
			column1
		, column2 
FROM Table1
ORDER BY column [1|2] [ASC|DESC];
LIMIT limiting number;

SELECT  
			first_name
		, last_name
		, salary 
FROM `HR.employees`
ORDER BY salary DESC
LIMIT 5;

SELECT
			region_id AS `지역ID`
		, country_id AS `국가ID`
		, country_name AS `국가명`
FROM `HR.countries`
ORDER BY region_id ASC, country_name DESC

• 그룹화 및 집계

SELECT 
		  column1
		, Aggregation(column2)
FROM Table1
GROUP BY column1;

SELECT
			region_id
		, COUNT(country_id) AS num_country
FROM `HR.countries`
GROUP BY region_id

SELECT
	    department_id
	  , AVG(salary) AS avg_salary
FROM `HR.employees`
WHERE salary > 7000
GROUP BY department_id
HAVING AVG(salary) > 10000 AND AVG(salary) < 19000
ORDER BY avg_salary DESC
LIMIT 3;

2.2. DML

2.2.1. DML의 특징

1. 불변성 : BigQuery는 기본적으로 데이터의 불변성 원칙을 따릅니다. DML 작업은 실제로 기존 데이터를 ‘변경’하는 것이 아니라 새로운 데이터를 쓰는 방식으로 동작합니다.

2. 쿼터 및 제한 : BigQuery는 DML 작업에 대한 일일 쿼터와 제한이 있습니다. 예를 들어, 특정 테이블에 대한 DML 작업의 빈도 및 업데이트된 행의 수 등에 제한이 있을 수 있습니다.

3. 비용 : DML 작업은 BigQuery에서 데이터를 스캔하므로 비용이 발생할 수 있습니다. 따라서 비용과 관련된 부분을 고려하여 DML 작업을 수행해야 합니다.

2.2.2. DML의 구성요소

• INSERT

INSERT INTO 
					 Table1 (column1, column2, ...) 
VALUES (value1, value2, ...); 

INSERT INTO 
					`HR.employees` (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (207, 'Hailey', 'Kim', 'haileyk@example.com','2023-07-01', 'IT_PROG', 10000);

• UPDATE

UPDATE
			Table1 
SET column1 = value1, column2 = value2, ... 
WHERE condition;

UPDATE 
			`HR.employees`
SET salary = 12000, email = 'HKIM', phone_number = '515.123.8282'
WHERE first_name = 'Hailey' AND last_name = 'Kim';

• DELETE

DELETE FROM 
					 Table1
WHERE condition;

DELETE FROM 
					`HR.employees`
WHERE first_name = 'Hailey' AND last_name = 'Kim';

2.3. DDL

2.3.1. DDL의 구성요소

2.3.2. DDL의 기능

1. 스키마의 생성(CREATE), 변경(ALTER), 삭제(DROP) 등 스키마에 대한 조작을 할 수 있습니다. 즉, 데이터베이스, 테이블, 뷰, 인덱스 등 데이터베이스 객체에 대한 조작을 수행합니다.

2. 직접 데이터 베이스의 테이블에 영향을 주기 때문에 실행 즉시 자동으로 테이블 상태를 데이터베이스에 적용되어 명령어를 취소(ROLLBACK) 할 수 없습니다.

3. 데이터 무결성 규칙을 지정하는데 사용할 수 있습니다. 무결성을 보장하기 위한 규칙과 제약조건도 DDL을 통해 정의하여 데이터의 품질과 일관성을 보장하는데 도움을 줍니다.

2.3.3. CREATE

• CREATE SCHEMA

CREATE SCHEMA [ IF NOT EXISTS ] [project_name.]dataset_name
[DEFAULT COLLATE collate_specification]
[OPTIONS(schema_option_list)];

CREATE SCHEMA mydataset;

• CREATE TABLE

CREATE TABLE [dataset_name.]table_name (
	column_name column_schema [NOT NULL]);

CREATE TABLE mydataset.departments (
  department_id INTEGER NOT NULL,
  department_name STRING,
  location_id INTEGER,
  manager_id INTEGER);

2.3.4. ALTER

• ADD COLUMN

ALTER TABLE table_name
	ADD COLUMN [IF NOT EXISTS] column[, ...];

ALTER TABLE mydataset.departments
  ADD COLUMN newcolumn STRING;

• RENAME TO (rename table)

ALTER TABLE [IF EXISTS] table_name
	RENAME TO new_table_name;

ALTER TABLE mydataset.departments
	RENAME TO newtable;

• RENAME COLUMN

ALTER TABLE [IF EXISTS] table_name
	RENAME COLUMN [IF EXISTS] column_name TO new_column_name[, ...];

ALTER TABLE mydataset.newtable
  RENAME COLUMN newcolumn TO columnA;

• DROP COLUMN

ALTER TABLE table_name
	DROP COLUMN [IF EXISTS] column_name [, ...];

ALTER TABLE mydataset.newtable
  DROP COLUMN columnA;

2.3.5. DROP

• DROP TABLE

DROP TABLE [IF EXISTS] dataset_name.table_name;

DROP TABLE mydataset.newtable;

• DROP SCHEMA

DROP SCHEMA [IF EXISTS] [project_name.]dataset_name [CASCADE|RESTRICT];

DROP SCHEMA mydataset;