📖 학습주제
데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (2)
SQL
SQL 기본
- 다수의 SQL 문을 실행한다면 세미콜론으로 분리가 필요함
- SQL 주석
---: 인라인 한줄짜리 주석
-/* -- */: 여러 줄에 걸쳐 사용 가능한 주석
DDL
CREATE TABLE :
- 테이블 생성
- CTAS:
CREATE TABLE table_name AS SELECT
select로 읽어온 것들로 create table
insert into 시간이 오래 걸려서 사용
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;
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를 사용해서 레코드 순서를 결정하기도 함
- 보통 다수의 테이블의 조인해서 사용하기도 함
레코드 수정 언어
- INSERT INTO : 테이블에 레코드를 추가하는데 사용
- UDATE FROM : 테이블 레코드의 필드 값 수정
- DELETE FROM : 테이블에서 레코드를 삭제
- vs DROP TABLE : DELETE FROM은 테이블 자체는 남아있으나 DROP TABLE은 테이블 자체를 삭제함
- vs TRUNCATE : DELETE FROM는 트랜잭션 기능 사용가능하나 TRUNCATE은 불가능
예제 데이터베이스
사용자 ID(userId): 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID(sessionId): 세션마다 부여되는 ID
- 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
- 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
SELECT
- 테이블(들)에서 레코드들(혹은 레코드수)을 읽어오는데 사용
SELECT 필드이름1, 필드이름2, …
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
LIMIT N; -- 갯수 제한
CASE WHEN
- 필드 값의 변환을 위해 사용 가능
- CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름 - 여러 조건을 사용하여 변환하는 것도 가능
CASE
WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
ELSE 값3
END 필드이름
NULL
- 값이 존재하지 않음을 나타내는 상수.(0 혹은 ""과는 다름)
- 필드 지정시 값이 없는 경우 NULL로 지정 가능(테이블 정의시 디폴트로 지정 가능)
- 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법이 필요
(is NULL/is not NULL) - NULL이 사칙연산에 사용되면 결과값은 모두 NULL
COUNT 함수 제대로 이해하기
| value |
|---|
| NULL |
| 1 |
| 1 |
| 0 |
| 0 |
| 4 |
| 3 |
SELECT COUNT(1) FROM count_test -> 7
: SELECT COUNT(1)은 주어진 테이블의 모든 레코드 수를 셈
SELECT COUNT(0) FROM count_test -> 7
SELECT COUNT(NULL) FROM count_test -> 0
: COUNT()는 주어진 인자가 NULL이면 0이 되고 NULL이 아니면 +1연산을 함
SELECT COUNT(value) FROM count_test -> 6
SELECT COUNT(DISTINCT value) FROM count_test -> 4
: DISTINCT는 중복을 제거함
WHERE
IN
- WHERE channel in (‘Google’, ‘Youtube’) : 채널이 Google이거나 Youtube
LIKE and ILIKE
- 문자열 매칭
WHERE channel LIKE ‘G%’ -> ‘G’로 시작
WHERE channel LIKE ‘%o%’ -> ‘o’가 들어감 - LIKE : 대소문자 구분, ILIKE : 대소문자 구분 x
BETWEEN
- 범위를 지정
STRING Functions
- LEFT(str, N) : str에서 왼쪽에서부터 N만큼 자름
- REPLACE(str, exp1, exp2) : str에서 exp1을 exp2로 바꿈
- UPPER(str) : 대문자로 바꿈
- LOWER(str) : 소문자로 바꿈
- LEN(str) : 길이를 리턴
- LPAD, RPAD : 왼/오른쪽에 특정 문자로 특정 개수만큼 채움
- SUBSTRING : 문자열을 자르는데 시작점 지정 가능
ORDER BY
- 디폴트는 오름차순(ASC)
- 내림차순도 가능(DESC)
ORDER BY 1 DESC - 복수의 컬럼을 정렬 가능
- NULL 값들은 오름차순 일 경우 (ASC) 마지막에, 내림차순 일 경우 (DESC) 처음에 위치함
이를 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST를 사용
타입 변환
DATE Conversion
- 타임존 관련 변환
CONVERT_TIMEZONE('America/Los_Angeles', ts) LA 시간대를 타임스탬프 시간대로 변경 - DATE, TRUNCATE : 타임스탬프를 인자로 받아 날짜만 리턴
- DATE_TRUNC : 첫번째 인자가 어떤 값을 추출하는지 지정 (week, month, day, …)
- EXTRACT/DATE_PART : 날짜시간에서 특정 부분의 값을 추출가능
- DATEDIFF : 특정 날에서 특정 시간(날짜, 시간 등등) 뺌, 특정 날짜들 사이의 차이도 알 수 있음
- DATEADD : 특정 날에서 특정 시간(날짜, 시간 등등) 더함
- GETDATE : 현재 시각
TO_CHAR, TO_TIMESTAMP
Type Casting
- 1/2의 결과 -> 0이 됨(정수간의 연산은 정수가 되어야하기 때문)
- 분자나 분모 중의 하나를 float로 캐스팅해야 0.5가 나옴
- 이는 프로그래밍 언어에서도 일반적으로 동일하게 동작함
어떻게?
- :: 오퍼레이터를 사용(category::float)
- cast 함수를 사용(cast(category as float))
.