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

• 사용자 ID : 보통 웹 서비스에서는 등록된 사용자마다 부여하는 유일한 ID
• 세션 ID : 세션마다 부여되는 ID
  • 세션 : 사용자의 방문을 논리적인 단위로 나눈 것
    • 사용자가 외부링크를 타고 오거나 직접 방문해서 올 경우 세션 생성
    • 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
      → 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음
  • 보통 세션의 경우 세션을 만들어낸 접점(경유지)을 채널이란 이름으로 기록해둠 → 마케팅 관련 기여도 분석을 위해서
  • 또한 세션이 생긴 시간도 기록
• 사용자 ID, 세션 ID, 세션 생성 시각, 채널들이 기록됨
  • 위 정보들을 통해서 데이터 분석 및 지표 설정
    • 마케팅 관련, 사용자 트래픽 관련
    • DAU(Daily Active User), WAU(Weekly Active User), MAU(Monthly Actice User) 등의 일/주/월별 Actice User차트
    • Marketing Channel Attribution 분석 → 어느 채널에 광고를 하는 것이 가장 효과적인지

SQL 문법

• 다수의 SQL문을 실행한다면 세미콜론(;)으로 분리
  ex) SQL문1; SQL문2; SQL문3;
• 주석처리
  • -- : 한 줄짜리 주석
  • /* -- */ : 여러 줄 주석
• SQL 키워드는 주로 대문자로 포맷팅
  ex) SELECT, FROM, WHERE
• 테이블/필드 이름의 명명규칙을 정하는 것이 중요
  • 단수형 vs 복수형 → ex) User vs Users
  • CamelCasing vs snake_casing → ex) UserSessionChanner vs user_session_channel

DDL(테이블 구조 정의 언어)

Primary key 속성을 지정할 수 있으나 무시됨(Big Data 데이터웨어하우스에서는 지켜지지 않음)

테이블 생성

CREATE TABLE raw_data.user_session_channer(
	userid int,
    sessionid varchar(32) primary key,
    channer varchar(32)
);

• CTAS : 생성과 동시에 레코드 저장

CREATE TABLE table_name AS SELECT

테이블 삭제

DROP TABLE table_name;

→ 없는 테이블을 지우려고 하는 경우 에러 발생

DROP TABLE IF EXISTS table_name;

• DROP TABLE vs DELETE FROM
  DROP TABLE은 테이블 자체를 날려버리고, DELETE FROM은 테이블은 남겨두고 레코드들만 삭제함. 또한 WHERE을 사용해서 조건에 맞는 레코드들만 지울 수 있음

테이블 변경

• 새로운 컬럼 추가

ALTER TABLE 테이블 이름 ADD COLUMN 필드이름 필드 타입;

• 기존 컬럼 이름 변경

ALTER TABLE 테이블 이름 RENAME 현재필드이름 TO 새필드이름;

• 기존 컬럼 제거

ALTER TABLE 테이블이름 DROP COLUMN 필드이름;

• 테이블 이름 변경

ALTER TABLE 현재테이블이름 RENAME TO 새테이블이름;

DML

테이블 데이터 조작 언어

삽입

INSERT INTO 테이블이름 VALUES();

삭제

• 테이블 안에 레코드들만 삭제

DELETE FROM schema_name.table;

• DELETE FROM vs TRUNCATE
  TRUNCATE는 TRANSACTION에 사용이 불가능

선택

• 테이블(들)에서 레코드들(혹은 레코드 수)을 읽어오는데 사용
• WHERE를 사용해 조건을 만족하는 레코드
• ASC : 오름차순, DESC : 내림차순
• LIMIT N : N개까지 확인

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

• SELECT * : 주어진 테이블의 모든 필드를 읽어옴

SELECT *
FROM raw_data.user_session_channel;

SELECT userId, sessionId, channel
FROM raw_data.user_session_channel

SELECT *
FROM raw_data.user_session_channel
LIMIT 10;

• 유일한 채널 이름을 알고 싶은 경우

SELECT DISTINCT channel
FROM raw_data.user_session_channel;

COUNT

• 채널별 카운트를 하고 싶은 경우

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

• 테이블의 모든 레코드 수 카운트

SELECT COUNT(1)
FROM raw_data.user_session_channel;

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

→ channel 이름이 Facebook인 경우만 고려해서 레코드 수 카운트

CASE WHEN

필드 값의 변환을 위해 사용 가능
CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름

• 여러 조건을 사용하여 변환하는 것도 가능

CASE
 WHEN 조건1 THEN 값1
 WHEN 조건2 THEN 값2
 ELSE 값3
END 필드이름

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 혹은 " "과는 다름
• 필드 지정시 값이 없는 경우 NULL로 지정 가능
  • 테이블 정의시 디폴트 값으로도 지정 가능
• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로함
  • field1 is NULL 혹은 field1 is not NULL
• NULL이 사칙연산에 사용되면 결과는 무조건 NULL

COUNT

테이블 : count_text

value
NULL
1
1
0
0
4
3

SELECT COUNT(1) FROM count_text → 7
SELECT COUNT(0) FROM count_text → 7
SELECT COUNT(NULL) FROM count_text → 0
SELECT COUNT(value) FROM count_text → 6
SELECT COUNT(DISTINCT value) FROM count_text → 4

WHERE

아래 오퍼레이터들은 CASE WHEN 사이에서도 사용 가능

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

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

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

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

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

BETWEEN

Used for date range matching

STRING

• LEFT(str,N) : str에서 왼쪽에서부터 N개만큼 반환
• REPLACE(str, exp1, exp2) : str에서 exp1을 찾아서 exp2로 변환
• UPPER(str) : str을 대문자로
• LOWER(str) : str을 소문자로
• LEN(str) : str의 길이
• LPAD, RPAD : 문자의 왼쪽 혹은 오른쪽에 string padding
• SUBSTRING : 문자열에서 시작점 ~ N개 만큼 리턴

ORDER BY

• Default ordering is ascending
  • Descending requires "DESC" : ORDER BY 1 DESC
• Ordering by multiple columns : ORDER BY 1 DESC, 2, e
• NULL값 순서는?
  • NULL값들은 오름차순일 경우 마지막에 위치
  • NULL값들은 내림차순일 경우 처음에 위치
  • 이름을 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST 사용

타입 변환

• DATE Conversion
  • 타임존 관련 변환
    • CONVERT_TIMEZONE('Americal/Los_Angeles', ts)
    • select pg_timezone_names();
  • DATE, TRUNCATE : 날짜만 리턴
  • DATE_TRUNC : 첫번째 인자가 어떤 값을 추출하는지 지정(week, month, day, ...)
  • EXTRACT or DATE_PART : 날짜 시간에서 특정 부분의 값을 추출 가능
  • DATEDIFF
  • DATEADD
  • GET_CURRENT
• TO_CHAR, TO_TIMESTAMP

Type Casting

• category를 float으로 변환
  • category::float
  • cast(category as float)