📒 SQL(2)

📌 관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보

• 사용자 ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID : 세션마다 부여되는 ID
  • 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부 링크(광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
    • 사용자가 방문 후 30분간 Interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
  • 하나의 사용자는 여러 개의 세션을 가질 수 있다.
  • 보통 세션의 경우 세션을 만들어낸 접점(경우지)를 채널이란 이름으로 기록해둔다.
    -> 마케팅 관련 기여도 분석을 위한것이다.
  • 세션이 생긴 시간도 기록한다.
• 이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능하다.
  • 마케팅 관련, 사용자 트래픽 관련
  • DAU, WAU, MAU등의 일주월별 Active User 차트
  • Marketing Channel Attribution 분석
    • 어느 채널에 광고를 하는 것이 가장 효과적인가?
• 사용자 ID 100번 : 총 3개의 세션(파란 글씨)을 갖는 예제
  • 세션 1 : 구글 키워드 광고로 시작한 세션
  • 세션 2 : Facebook 광고를 통해 생긴 세션
  • 세션 3 : 네이버 광고를 통해 생긴 세션

구글 광고(방문) -> 상품 광고 클릭 -> 상품 리뷰 페이지 클릭 -> Facebook 광고(방문)
-> 다른 상품 광고 클릭 -> 네이버 광고(방문) -> 상품 구매

---

📌 관계형 데이터베이스 예제 - 데이터베이스와 테이블

raw_data DB(스키마)
user_session_channel Table

컬럼	타입
userid	int
sessionid	varchar(32)
channel	varchar(32)

session_timestamp Table

컬럼	타입
sessionid	varchar(32)
ts	timestamp

---

📌 SQL 기본

• 먼저 다수의 SQL문을 실행한다면 세미콜론(;)으로 분리가 필요하다.
• SQL 주석 -> --(한줄), /--/(여러줄)
• SQL키워드는 대문자를 사용하여 나름대로 포맷팅이 필요하다.
  -> 팀 프로젝트라면 팀에서 사용하는 공통 포맷이 필요하다.
• 테이블/필드 이름의 명명규칙을 정하는 것이 중요하다.
  -> 단수형 vs. 복수형 -> User vs. Users
  -> _ vs. CamelCasing -> user_session_channel vs. UserSessionChannel

---

📌 SQL DDL - 테이블 구조 정의 언어

• CREATE TABLE -> 테이블 생성
• Primary key 속성을 지정할 수 있으나 무시된다.
  • Primary key uniqueness
    • Big Data 데이터웨어하우스에서는 지켜지지 않는다.(Redshift, Snowflake, BigQuery)
    • 대량의 레코드를 추가할 때 성능이 저하된다. -> 하나씩 다 확인해야 하기 때문
• CTAS : CREATE TABLE table_name AS SELECT (테이블 생성 + 내용 추가)
  • vs. CREATE TABLE and thenm INSERT

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; -> 에러 메세지 생략
    -> vs. DELETE FROM
    • DELETE FROM은 조건에 맞는 레코드들을 지움(테이블은 존재)

• ALTER TABLE

  • 새로운 컬럼 추가
    -> ALTER TABLE table_name ADD COLUMN field_name field_type;
  • 기존 컬럼 이름 변경
    -> ALTER TABLE table_name RENAME (현재)field_name to (NEW)field_name
  • 기존 컬럼 제거
    -> ALTER TABLE table_name DROP COLUMN field_name;
  • 테이블 이름 변경
    -> ALTER TABLE (현재)table_name RENAME to (NEW)table_name;

---

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

• 레코드 질의

  • SELECT FROM : 테이블에서 레코드와 필드를 읽어오는데 사용
  • WHERE : 레코드 선택 조건을 지정
  • GROUP BY : 정보를 그룹 레벨에서 뽑는데 사용
    -> DAU(일별), WAU(주별), MAU(월별) 계산은 GROUP BY를 필요로 한다.
  • ORDER BY : 레코드 순서를 결정 -> ASC(오름), DESC(내림) -> 필드 이름 대신 숫자 사용 가능
  • LIMIT N; : 앞에서부터 N개 만큼 가져온다.
  • 보통 다수의 테이블을 조인해서 사용하기도 한다.

• 레코드 수정

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

• DISTINCT : 중복 제거

SELECT DISTINCT channel
FROM raw_data.user_session_channel;

• CASE WHEN
  • 필드 값의 변환을 위해 사용 가능하다.
  • CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일 떄 값 END Field_name
  • 여러 조건을 사용하여 변환 가능

CASE
	WHEN 조건1 THEN 값1
    WHEN 조건2 THEN 값2
END Field_name

SELECT 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;

📌 NULL

• 값이 존재하지 않음을 나타내는 상수. 0 or ""과는 다르다.
• 필드 지정시 값이 없는 경우 NULL로 지정 가능하다.
  • 테이블 정의시 디폴트 값으로도 지정 가능하다.
• 어떤 필드의 값이 NULL인지 아닌지 비교하는 특수한 문법을 필요로 한다.
  • field1 is NULL or field1 is not NULL
• NULL이 사칙연산에 사용되는 경우
  • SELECT 0 + NULL, 0 - NULL, 0 * NULL, 0 / NULL

---

📌 COUNT

• COUNT(n) : 채널별로 카운트를 하고싶은 경우 사용

1. 한 그룹 속에 레코드가 몇개인지

   SELECT channel, COUNT(1)
   FROM raw_data.user_session_channel
   GROUP BY 1;

2. SELECT 조건에 만족하는 레코드의 수

   SELECT COUNT(1)
   FROM raw_data.user_session_channel;

3. channel이름이 Facebook인 레코드 수

   SELECT COUNT(1)
   FROM raw_data.user_session_channel
   WHERE channel = 'Facebook';

• 예시

SELECT COUNT(1) FROM count_test -> count_test의 개수
SELECT COUNT(0) FROM count_test -> count_test의 개수
SELECT COUNT(NULL) FROM count_test -> 0
SELECT COUNT(value) FROM count_test -> NULL이 아닌것의 개수
SELECT COUNT(DISTINCT value) FROM count_test -> NULL이 아니고 중복을 제거한 개수

---

📌 WHERE

• IN
  • WHERE channel in ('Google', 'Youtube')
    -> WHERE channel = 'Google' OR channel = 'Youtube'
  • NOT IN
• LIKE and ILIKE
  • LIKE는 대소문자를 구분하며 문자열을 찾는 것 이다.
  • ILIKE는 대소문자를 구분하지 않으면서 문자열을 찾는 것이다.
  • WHERE channel LIKE 'G%' -> 'G*'
  • WHERE channel LIKE '%o%' -> '* o *'
  • NOT LIKE or NOT ILIKE
• BETWEEN
  • 날짜 범위에 일치하는 것을 찾을 때 사용한다.
• CASE WHEN 사이에서도 사용 가능하다.

---

📌 IN & LIKE/ILIKE

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

SELECT DISTINCT channel
FROM FROM raw_data.user_session_channel
WHERE channel ILIKE '%o%';

SELECT COUNT(1)
FROM FROM raw_data.user_session_channel
WHERE channel ILIKE 'Google' or channel ILIKE 'Facebook';

SELECT DISTINCT channel
FROM FROM raw_data.user_session_channel
WHERE channel NOT ILIKE '%o%';

---

📌 STRING Function

• LEFT(str, N)
• REPLACE(str, exp1, exp2)
• UPPER(str)
• LOWER(str)
• LEN(str)
• LPAD, RPAD
• SUBSTRING

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

---

📌 ORDER BY

• 기본 순서는 오름차순이다.
  -> ORDER BY 1 ASC
• 내림차순은 DESC를 사용한다.
  -> ORDER BY 1 DESC
• 여러 열로 정렬할 때는
  -> ORDER BY 1 DESC,2,3
• NULL의 순서
  • NULL값들은 오름차순 인 경우 마지막
  • NULL값들은 내림차순 인 경우 처음
  • 이를 바꾸고자 하면 NULLS FIRST or NULLS LAST 사용

---

📌 Type Casting

---