SQL 2. SQL CRUD&문법

SQL DDL

CREATE

CREATE TABLE raw_data.user_session_channel(
	user int,
    sessionid varchar(32) primary key,
    channel varchar(32)
    );

데이터 웨어하우스에서는 primary key uniqueness를 보장해주지 못한다.

• CREATE TABLE and the INSERT
  INSERT INTO 를 활용하여 레코드를 추가하게되면 한번에 추가할 수 있는 최대 레코드 양이 정해져 있어 그 이상의 레코드를 추가하기 위해선 INSERT INTO를 여러번 해줘야한다. 그래서 COPY 명령어를 사용한다. 레코드들을 csv, json과 같은 파일의 형태로 S3에 저장해두고 그것을 한번에 Bulk update하는 방식을 사용한다.
  INSERT 를 여러번 실행하면 안되는 이유
• CTAS(CREATE TABLE table_name AS SELECT)
  테이블을 만드는 동시에 레코드를 추가함

DROP TABLE

테이블 삭제

# 없는 테이블을 지우려고 하는 경우 에러
DROP TABLE table_name;

# 존재하지 않는 테이블이더라도 에러 안냄
DROP TABLE IF EXISTS table_name;

# 테이블의 구조는 그대로고 레코드만 다 지움
# where를 쓰면 특정 레코드만 삭제 가능
DELETE FROM 

ALTER TABLE

테이블의 정보 변경

# column 추가
ALTER TABLE table_name ADD COLUMN field_name field_type;

# column 이름 변경
ALTER TABLE table_name RENAME field_name to new_field_name;

# column 삭제
ALTER TABLE table_name DROP COLUMN field_name;

# 테이블 이름 변경
ALTER TABLE table_name RENAME to new_talbe_name;

SQL DML

SELECT

테이블에서 레코드와 필드를 읽어오는데 사용

SELECT field1,field2..
FROM table_name
WHERE condition
GROUP BY field1, field2.. -- 필드이름 대신 숫자
ORDER BY field_name [ASD|DESC] -- 필드이름 대신 숫자 사용 가능
LIMIT N;

# 특정 필드의 distinct 한 값을 보여줌
SELECT DISTINCT field_name FROM table_name

# 특정 필드의 각 값의 개수를 count
SELECT field_name, COUNT(1)
FROM table_name
GROUP BY 1;

# 테이블의 모든 레코드 수 카운트
SELECT COUNT(1) FROM table_name;

# 특정 필드에서 특정 값을 가지는 레코드 수 카운트
SELECT COUNT(1) FROM table_name WHERE field_namae='value';

# 필드 값의 변환
SELECT CASE
	WHEN field in ('value1','value1') THEN 'new_value1'
	WHEN field in ('value1','value1') THEN 'new_value2'
    ELSE 'new_value3'
END new_field_name
FROM table_name;

레코드 수정

INSERT INTO : 테이블에 레코드를 추가하는데 사용 (vs COPY)
UPDATE FROM : 테이블 레코드의 필드 값 수정
DELETE FROM : 테이블에서 레코드를 삭제 (한줄단위로)

• vs TRUNCATE : DELETE FROM 과 동일한 역할을 하지만 TRANSACTION 기능을 사용못함(한방에 삭제해서?)

실습에 들어가기 전에..

데이터를 다루기 전에..
데이터를 다루기 전에는 항상 데이터 퀄리티를 확인해야한다. 1000~1000개 정도의 레코드를 직접 살펴보고 내가 원하는 데이터가 맞는지 확인할 필요가 있다. 추가적으로 중복된 레코드, 최근 데이터의 존재 여부 체크(freshness), Primary key uniqueness가 지켜지는지, 값이 비어있는 컬럼들이 있는지를 unit test 형태로 만들어 체크해볼 수 있다.

회사가 성장하게 되면..
어느 시점이 되면 너무나 많은 테이블들이 존재하게 되어 DATA DISCOVERY 문제들이 생겨난다. 중요 테이블들이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해진다. 이 문제를 해결하기 위한 다양한 오픈소스와 서비스가 출현하였다. (Data hub(LinkedIn), Amundsen(Lyft), Select Star, DataFrame...)

SQL 문법

NULL

값이 존재하지 않음을 의미한다. 어떤 필드의 값이 NULL인지 아닌지 비교는 is NULL, is not NULL 을 사용한다. 그리고 NULL이라는 값이 사칙연산에 들어가는 순간 무조건 결과 값은 NULL이다.

COUNT

# COUNT 함수의 인자가 NULL이 아니면 1씩 더해짐
# 결과 : 레코드의 수
SELET COUNT(0) FROM table_name;
SELET COUNT(1) FROM table_name;

# 결과 : 0
SELET COUNT(NULL) FROM table_name;

# field의 값이 NULL이 아닌 값을 COUNT
SELET COUNT(field) FROM table_name;

# DISTINCT field의 값이 NULL이 아닌 값을 COUNT
SELET COUNT(DISTINCT field) FROM table_name;

WHERE

# IN
WHERE field in ('value1', 'value2'..)
# WHERE field = 'value1' OR 'value2'
WHERE field not in ('value1', 'value2'..)

# LIKE, ILIKE
# LIKE는 대소문자 구별, ILIKE는 구별안함
WHERE channel LIKE 'G%'
WHERE channel LIKE 'G_'
# %=문자 여러개, _:문자 한개
# NOT LIKE, NOT ILIKE의 형태로 쓸 수 있음

# BETWEEN : 범위
WHERE field_name BETWEEN value1 AND value2
# value1, value2 사이

STRING Function

LEFT(str,N) # N개 출력
REPLACE(str,exp1,exp2)  # exp1 을 exp2로 변경하여 return
UPPER(str) # 모든 문자열을 대문자로 변경하여 return
LOWER(str) # 모든 문자열을 소문자로 변경하여 return
LEN(str) # 길이를 리턴
LPAD, RPAD(str,N,'c') : str이 N의 길이를 가질때 까지 'c'를 L/R 에 padding한 값을 return
SUBSTRING(str,start,N) : str의 start번째부터 N개의 문자 return

ORDERED BY

default ordering은 ASC 이다.

ORDERED BY 1 # ASC
ORDERED BY 1 DESC
ORDERED BY 1 DESC, 2, 3 # 1이 같으면 2로, 2도 같으면 3으로

NULL 값들은 오름차순일 경우(ASC), 마지막에 위치
NULL 값들은 내림차순일 경우(DESC), 처음에 위치
이를 바꾸고 싶다면 NULLS FIRST, NULLS LAST를 사용하면 됨

타입 변환

CONVERT_TIMEZONE('PST', ts_field) # ts_field UTC 시간대를 'PST' 시간대로 바꿈
SELEFT pg_timezone_names(); # 어떤 timezone이 있는지 확인가능

DATE_TRUNC('day', ts_field) # ~'day' 까지만 반영
EXTRACT(unit from date) # date에서 unit 추출
DATE_PART(datepart, {date|timestamp})
DATEDIFF, DATEADD, GET_CURRENT...

TO_CHAR : 문자열로 변환
TO_TIMESTAMP : 문자열을 timestamp로 변환

Type Casting

1/2의 결과는 0이됨, 왜냐하면 정수간의 연산은 정수가 나와야하기 때문, 분모나 분자를 float으로 캐스팅해줘야함

category::float # float으로 변환
cast(category as float) # 동일

jupyter에서 sql 엔진 사용하기

sql 설치

# 라이브러리 설치
pip install ipython-sql

# sql server
pip install pyodbc
# PostgreSQL 
pip install pyscopg2
# MySQL
pip install PyMySQL

노트북에서 매직명령어로 익스텐션을 로드

%load_ext sql

DB에 연결

# SQL Server
%sql mssql+pyodbc://user_name:password@host:port_number/db

# PostgreSQL
%sql postgresql://user_name:password@host:port_number/db
           
# MySQL
%sql mysql://user_name:password@host:port_number/db

sql 엔진을 쓰려면 %%sql을 입력해주고 한줄 공백을 주고 sql statement를 입력하면됨

%%sql

SELET *
FROM table_name

# table 생성
DROP TABLE IF EXISTS sale_data;
CREATE TABLE sale_data (
 order_id SERIAL,
 date DATE,
 category VARCHAR(50),
 itemcode integer,
 price integer,
 amount integer,
 Primary KEY(order_id)
);

# csv 복사
\COPY sale_data FROM 'C:\Users\skh95\workspace\sales.csv' DELIMITER ',' CSV HEADER;