SQL소개

SQL 기본

---

다수의 SQL 문을 실행한다면 세미콜론으로 분리한다.
ex) sql문1;sql문2

• SQL 주석: -- 인라인 한줄짜리 주석, /--/ 여러 줄 주석
• SQL 키워드는 대문자를 사용한다던지 하는 나름의 포맷팅이 필요
  팀프로젝트라면 팀에서 사용하는 공통 포맷이 필요
• 테이블/필드 이름의 명명규칙 정하기(단수형(ex : User) vs. 복수형(Users), snake_case vs. CamelCase)

DDL - 테이블 구조 정의 언어

---

CREATE TABLE

• primary key 속성을 지정할 수 있으나 무시됨
  primary key uniqueness - big data데이터웨어하우스에서는 지켜지지않음(레코드들이 추가될 때마다 많은양의 데이터를 파악해서 유니크하게 해줘야되는데 너무 많아서 어려움)
• CTAS: CREATE TABLE table_name AS SELECT : 테이블 생성과 동시에 내용 추가 ( vs. CREATE TABLE and then INSERT)
• raw_data 스키마 안에 user_session_channel 테이블 만들기

ex)

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

DROP TABLE

• DROP TABLE table_name; 없는 테이블을 지우려고 하면 에러 발생
• DROP TABLE IF EXISTS table_name; 테이블이 존재할 때 지움
• DELETE FROM 조건에 맞는 레코드들만 지움(테이블 자체는 존재)

ALTER TABLE

• 새로운 컬럼 추가: ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
• 기존 컬럼 이름 변경: ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;
• 기존 컬럼 제거: ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
• 테이블 이름 변경: ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

DML - 테이블 데이터 조작 언어

---

SELECT: 레코드 질의 언어

• SELECT FROM: 테이블에서 레코드와 필드 읽어온다.
• WHERE를 사용하여 레코드 선택 조건 지정
• GROUP BY를 통해 정보를 그룹 레벨에서 추출
• ORDER BY를 사용해 레코드 순서 결정
• 다수의 테이블을 JOIN해서 사용하기도 함

레코드 수정 언어

• INSERT INTO: 테이블에 레코드를 추가하는데 사용
• UPDATE FROM: 테이블 레코드의 필드 값 수정
• DELETE FROM(vs. TRUNCATE): 테이블에서 레코드를 삭제

---

SELECT 소개

테이블(들)에서 레코드들(혹은 레코드 수)을 읽어오는데 사용하며, WHERE를 사용해 조건을 만족하는 레코드만 읽어올 수 있다.

SELECT 필드이름1, 필드이름2, ... -> *를 사용하면 모든 필드 선택
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC|DESC] -> 필드 이름 대신에 숫자 사용 가능
LIMIT N;
ex)

SELECT *
FROM raw_data.user_session_channel;

SELECT *
FROM raw_data.user_session_channel
LIMIT 10;

SELECT DISTINCT channel                      -> 유일한 채널 이름을 알고 싶은 경우
FROM raw_data.user_session_channel;

SELECT channel, COUNT(1)                    -> 채널별 카운트를 하고 싶은 경우. COUNT 함수!!
FROM raw_data.user_session_channel	    -> SELECT문의 1번 필드로 GROUP BY 수행
GROUP BY 1;

SELECT COUNT(1)                    -> 채널별로 속한 레코드 수를 카운트,테이블의 모든 레코드 수 카운트하는 경우-> COUNT(*).
FROM raw_data.user_session_channel;

SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel = 'Facebook';		   ->channel 이름이 Facebook인 레코드 수 카운트

CASE WHEN

필드값의 변환을 위해 사용 가능하다.
CASE WHEN 조건 THEN 참일 때 값 ELSE 거짓일 때 값 END 필드이름
여러 조건을 사용하여 변환하는 것도 가능하다.

ex

SELECT
  channel,
  CASE
    WHEN channel in ('Facebook', 'Instagram') THEN 'Social-Media'
    WHEN channel in ('Google', 'Naver') THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM raw_data.user_session_channel
LIMIT 100;

channel이름으로 분류를 하여 channel_type라는 새로운 필드 생성

NULL이란?

• 값이 존재하지 않음을 나타내는 상수. 0 혹은 ""과는 다름
• 필드 지정 시 값이 없는 경우 NULL로 지정 가능
  테이블 정의시 디폴트 값으로도 지정 가능
• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
  field1 is NULL: 어떤 필드의 값이 NULL인지 아닌지 비교
• NULL이 사칙연산에 사용되면 그 결과는 모두 NULL

COUNT함수 제대로 이해하기

• SELECT COUNT(1) FROM count_test
  모든 레코드 수를 세어 7을 반환
• SELECT COUNT(0) FROM count_test
  위와 같은 결과
• SELECT COUNT(NULL) FROM count_test
  NULL이 인자로 들어오면 세지 않으므로 0을 반환
• SELECT COUNT(value) FROM count_test
  NULL이 아닌 value의 레코드 수를 세어 6을 반환
• SELECT COUNT(DISTINCT value) FROM count_test
  유일한 값들(NULL, 1, 0, 4, 3) 중 NULL이 아닌 값만 세어 4를 반환

WHERE

1. IN and NOT IN

• WHERE channel in ('Google', 'Youtube')
• WHERE channel = 'Google' OR channel = 'Youtube'

2. LIKE and ILIKE

• LIKE: 대소문자를 구분
• ILIKE: 대소문자를 구분하지 않음
• WHERE channel LIKE 'G%' ->'G*(채널명이 G로 시작하는 채널)
• WHERE channel LIKE '%o%' ->o(채널명에 o가 들어있는 채널)
• NOT LIKE or NOT ILIKE

3. BETWEEN

• 날짜사이에 들어가면 매칭

ex)

SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel in ('Google','Facebook');

SELECT DISTINCT channel
FROM raw_data.user_session_channel
WHERE channel ILIKE '%o%'; 
채널명에 o가 들어있는 채널을 구분되는 채널만 출력

STRING Functions

• LEFT(str, N): 왼쪽부터 N개 문자열만 추출
• REPLACE(str, exp1, exp2): 주어진 문자열에서 exp1을 exp2로 대체
• UPPER(str) : 대문자로 변환
• LOWER(str) : 소문자로 변환
• LEN(str) : 문자열의 길이 추출
• LPAD, RPAD: 문자열의 왼쪽/오른쪽에 문자 padding
• SUBSTRING: 범위를 지정하여 문자열 추출
  ex)

SELECT
    LEN(channel),
    UPPER(channel),
    LOWER(channel),
    LEFT(channel, 4)
FROM raw_data.user_session_channel
LIMIT 100;

ORDER BY

• 디폴트값은 오름차순으로 정렬한다.
• 내림차순 정렬을 하고싶다면 "DESC" 명시
• 여러 개 컬럼을 기준으로 정렬할 수 있음 ORDER BY 1 DESC, 2, 3
• NULL값의 순서
  NULL값들은 오름차순일 경우 마지막에, 내림차순일 경우 처음에 위치함
  이를 바꾸려면 NULLS FIRST 혹은 NULLS LAST 사용

Type Casting

1/2의 결과는 0이됨. 정수간의 연산은 정수가 되어야 하기 때문이다.
따라서 분자나 분모중의 하나를 float으로 캐스팅해야 0.5가 나옴

캐스팅하는 방법은 두가지가 있다.

• 오퍼레이터를 사용
  category::float
• cast함수를 사용
  cast(category as float)

Pandas랑 연동하는 법

result = %sql SELECT * FROM raw_data.user_session_channel

이 후 판다스 문법을 이용하여 사용 가능
df = result.DataFrame()

df.groupby(["channel"]).size()
df.groupby(["channel"])["sessionid"].count()

끝!

---