터칭 데이터

알아두면 유용한 SQL 문법들 UNION, EXCEPT, INTERSECT COALESCE, NULLIF LISTAGG LAG WINDOW 함수 ROW_NUMBER OVER SUM OVER FIRST_VALUE, LAST_VALUE JSON Parsing 함수 UNION, EXCEPT, INTERSECT UNION (합집합) 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌 UNION vs. UNION ALL UNION은 중복을 제거 UNION SELECT 'keeyong' as first_name, 'han' as last_name UNION SELECT 'elon', 'musk' UNION SELECT 'keeyong', 'han' UNION ALL SELECT 'keeyong' as f..

트랜잭션(Transaction) Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음. SELECT에는 트랜잭션을 사용할 이유가 없음 BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용 BEGIN과 END(COMMIT)사이의 쿼리가 실패하면 ROLLBACK 은행 계좌 이체가 아주 좋은 예 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐 만일 인출은 성공했는데 입금이 실패한다면? 이 두 과정은 동시에 성공하던지 실패해야함 -> Atomic하다는 의미 이런 과정들을 트랜잭션으로 묶어주어야함 조회만 한다면 이는 트랜잭션으로 묶일 이유가 없음 END와 COMMIT은 동일 만일 BEGIN ..

1. 사용자별로 처음 채널과 마지막 채널 알아내기 2. Gross Revenue가 가장 큰 UserID 10개 찾기 3. raw_data.nps 테이블을 바탕으로 월별 NPS 계산 1. 사용자별 처음과 마지막 채널 찾기 먼저 사용자 1명을 기준으로 첫 채널과 마지막 채널을 조회하는 방법입니다. SELECT ts, channel FROM raw_data.user_session_channel usc JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid WHERE userid = 251 ORDER BY 1 251번 사용자 단 한명의 첫채널이 맨위 마지막 채널이 맨 마지막으로 오는 쿼리입니다. WHERE 절 조건마다 모든 사용자별 ID를 입력한 ..

NULLIF NULLIF 표현식은 두 인수를 비교하여 동일한 경우에는 NULL을 반환합니다. 동일하지 않으면 첫 번째 인수가 반환됩니다. NULLIF ( expression1, expression2 ) expression1과 expression2가 동일하다면 NULL을 리턴합니다. 반대로 expression1과 expression2가 다르다면 expression1을 리턴합니다. COALESCE 일련의 표현식에서 NULL이 아닌 첫 번째 표현식의 값을 반환합니다. NULL이 아닌 값이 발견되면 목록의 나머지 표현식은 평가되지 않습니다. NVL은 COALESCE와 동일합니다. NVL( expression, expression, ... ) COALESCE( expression, expression, ... )..

채널별, 월별 매출액 테이블을 CTAS로 만들기 1. session_timestamp,user_session_channel,session_transaction 3개의 테이블들을 사용합니다. 2. 아래와 같은 필드로 구성합니다. month channel uniqueUsers (총방문 사용자) paidUsers (구매 사용자: refund한 경우도 판매로 고려) conversionRate (구매사용자 / 총방문 사용자) grossRevenue (refund 포함) netRevenue (refund 제외) 1. 혹시 OUT JOIN이 필요한지 테이블 점검 select distinct sessionid from raw_data.session_timestamp minus select distinct session..

BOOLEAN 타입 처리 flag = True와 flag is True는 동일한 표현입니다. flag is True와 flag is not False는 동일한 표현이 아닙니다. True가 아닌 NULL도 not False이기 때문입니다. SELECT * FROM raw_data.boolean_test; 위와 같이 True가 2개, False가 2개, Null(None)이 1개 있는 테이블이 있습니다. SELECT COUNT(CASE WHEN flag = True THEN 1 END) true_cnt1, COUNT(CASE WHEN flag is True THEN 1 END) true_cnt2, COUNT(CASE WHEN flag is not False THEN 1 END) not_false_cnt FR..

JOIN SQL 조인은 두 개 혹은 그 이상의 테이블들을 공통 필드를 갖고 머지하는데 사용됩니다. 이는 스타 스키마(star scheme)로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용됩니다. SELECT A JOIN B ON ~ 왼쪽 테이블을 LEFT, 오른쪽 테이블을 RIGHT이라고 가정하면 이해하기 쉽습니다. 위에서는 A가 LEFT, B가 RIGHT 테이블입니다. 95%의 경우는 LEFT JOIN과 RIGHT JOIN을 사용합니다. 5%는 FULL JOIN을 사용하고 RIGHT JOIN은 LEFT JOIN에서 테이블들의 순서만 바꾸면 그만이므로 전혀 사용할 일이 없다고 보셔도 좋습니다. 다양한 종류의 조인 (The Art of PostgreSQL - What is an SQL JOIN?)..

CTAS: SELECT를 가지고 테이블을 생성 데이터 분석을 함에 있어서 많이 사용되는 테이블(예를 들면 join된 테이블)을 미리 만들어 둘 때 흔히 사용됩니다. DROP TABLE IF EXISTS adhoc.keeyong_session_summary; CREATE TABLE adhoc.keeyong_session_summary AS SELECT B.*, A.ts FROM raw_data.session_timestamp A JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid; Redshift는 스키마로 raw_data, analytics, adhoc 세 가지 폴더(DB)로 나뉜다고 했습니다. 그리고 각 폴더에 테이블들을 생성해 사용합니..

GROUP BY & Aggregate 테이블의 레코드들을 필드를 중심으로 그룹화해 그룹별로 다양한 정보를 계산합니다. 크게 두 단계로 이루어집니다. 1. 먼저 그룹핑을 할 필드를 결정합니다. 이때 그룹핑할 필드는 2개 이상이 될 수 있습니다. 그리고 GROUP BY로 그룹핑할 필드(들)의 이름을 사용하거나 필드 일련번호를 사용합니다. (여기서 일련번호란 "ordinal numbering" 또는 "ordinal position"를 말합니다.) 2. 그룹별로 계산할 내용을 결정합니다. 이 단계에서 Aggregate함수를 사용합니다. COUNT, SUM, AVG, MIN, MAX, LISTAGG 등이 있습니다. 이 때 이런 함수들을 사용하면 결과가 곧 새로운 필드가 되기 때문에 보통은 이때 alias(as)를..

실습에 사용할 테이블에 대한 설명 ◆ 관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보 (1) ❖ 사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID ❖ 세션 ID: 세션마다 부여되는 ID ● 세션: 사용자의 방문을 논리적인 단위로 나눈 것 ▪ 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성 ▪ 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성 ● 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음 ● 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠 ▪ 마케팅 관련 기여도 분석을 위함 ● 또한 세션이 생긴 시간도 기록 ◆ 관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정..