📖 학습 주제
- JOIN
✏️ 주요 메모 사항 소개
JOIN
두 개 혹은 그 이상의 테이블들을 공통 필드를 가지고 머지하는데 사용. 이는 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용된다. 왼쪽 테이블을 LEFT라고 하고 오른쪽 테이블을 RIGHT라고 할 때 JOIN의 결과는 방식에 상관없이 양쪽의 필드를 모두 가진 새로운 테이블을 만들어내게 된다. 조인의 방식에 따라 다음 두 가지가 달라진다
- 어떤 레코드들이 선택되는지
- 어떤 필드들이 채워지는지
- JOIN 시 고려해야할 점
1) 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
2) 조인하는 테이블들간의 관계를 명확하게 정의
3) 어느 테이블을 베이스로 잡을지 (FROM에 사용할지) 결정해야함
JOIN 문법
SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2 --INNER, FULL, LEFT, RIGHT, CROSS ...
WHERE A.ts >= '2019-01-01';JOIN의 종류
JOIN 실습
여섯 종류의 조인에 대한 실습을 하기에 앞서 실습에 사용될 테이블을 살펴보자
1. INNER JOIN
SELECT * FROM raw_data.Vital v
JOIN raw_data.Alert a ON v.vitalID = a.vitalID;
2. LEFT JOIN
- 왼쪽 테이블(Base)의 모든 레코드들을 리턴하고 오른쪽 테이블의 필드는 매칭되는 경우에만 채워진 상태로 리턴
SELECT * FROM raw_data.Vital v
LEFT JOIN raw_data.Alert a ON v.vitalID = a.vitalID;
3. FULL JOIN
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴, 매칭되는 경우에만 모든 필드들이 채워진 상태로 리턴
SELECT * FROM raw_data.Vital v
FULL JOIN raw_data.Alert a ON v.vitalID = a.vitalID;
4. CROSS JOIN
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함
SELECT * FROM raw_data.Vital v
CROSS JOIN raw_data.Alert a;
5. SELF JOIN
- 동일한 테이블을 alias를 달리해서 자기 자신과 조인함
SELECT * FROM raw_data.Vital v1
JOIN raw_data.Vital v2 ON v1.vitalID = v2.vitalID;
주의점
- COALESCE 함수
NULL 값을 다른 값으로 바꿔주는 함수
SELECT
value,
COALESCE(value, 0) --value가 NULL이면 0을 리턴
FROM raw_data.count_test- NULL 값을 판단할 때
칼럼의 값이 NULL일 때 이를 판단하려면is NULL을 사용해야 한다.= NULL을 사용할 시 틀린 값을 반환할 수 있다.
SELECT COUNT(1)
FROM raw_data.boolean_test
WHERE flag is NULL;- 공백이 들어있는 필드이름 혹은 예약된 키워드를 필드이름으로 사용하려면 "" 사용
CREATE TABLE adhoc.keeyong_test (
"group" int primary key,
"mailing address" varchar(32)
);숙제
- 먼저 ROW_NUMBER를 붙인 임시 테이블을 만든 뒤 ROW_NUM이 최솟값을 가지는 채널을 출력하면 된다. 이 때 ROW_NUMBER를 사용해 오름차순 번호와 내림차순 번호를 모두 칼럼으로 추가한 뒤 각각의 최소를 반환하면 처음과 마지막을 알 수 있다.
WITH ranked_channels AS (
SELECT
usc.userId,
usc.channel,
st.ts,
ROW_NUMBER() OVER (PARTITION BY usc.userId ORDER BY st.ts) AS row_num_asc,
ROW_NUMBER() OVER (PARTITION BY usc.userId ORDER BY st.ts DESC) AS row_num_desc
FROM
raw_data.user_session_channel usc
JOIN
raw_data.session_timestamp st ON usc.sessionId = st.sessionId
)
SELECT
userId,
MIN(CASE WHEN row_num_asc = 1 THEN channel END) AS first_channel,
MIN(CASE WHEN row_num_desc = 1 THEN channel END) AS last_channel
FROM
ranked_channels
GROUP BY
userId
ORDER BY
userId
LIMIT 15;
- 두 테이블을 조인하여 내림차순으로 정렬한 뒤 10개만 추출하면 되는 간단한 문제다.
SELECT
usc.userId,
SUM(amount) OVER(PARTITION BY usc.userid)
FROM raw_data.user_session_channel AS usc
JOIN raw_data.session_transaction AS revenue ON revenue.sessionId = usc.sessionId
ORDER BY 2 DESC
LIMIT 10;
- 먼저 raw_data.nps 의 레코드 들 부터 확인해보자
select * from raw_data.nps
LIMIT 5
- created_at에서 월별을 GROUP화 해서 CASE WHEN을 사용해서 SCORE 별로 detractor, passive, promoter를 나누고 계산하면 된다.
SELECT
LEFT(created_at, 7) AS "month",
SUM(CASE WHEN score >= 0 AND score <= 6 THEN 1 ELSE 0 END) AS detractor,
SUM(CASE WHEN score >= 7 AND score <= 8 THEN 1 ELSE 0 END) AS passive,
SUM(CASE WHEN score >= 9 AND score <= 10 THEN 1 ELSE 0 END) AS promoter,
ROUND((promoter - detractor)::FLOAT * 100 / COUNT(*), 2) AS NPS
FROM
raw_data.nps
GROUP BY
1
ORDER BY
