JOIN

두 개 혹은 그 이상의 테이블들을 특정 키를 기준으로 합친다.

조인의 방식에 따라 다음 두 가지가 달라진다:

1. 어떤 레코드들이 선택되는지
2. 어떤 필드들이 채워지는지

다양한 종류의 JOIN

INNER JOIN

특정 필드 기준으로 왼쪽 테이블과 오른쪽 테이블 모두 존재하는 레코드들만 반환한다.

LEFT (OUTER) JOIN

왼쪽테이블 전체와, 왼쪽 테이블에 있는 필드와 연관된 오른쪽 테이블의 필드를 선택한다.

RIGHT (OUTER) JOIN

LEFT (OUTER) JOIN과 같은데 방향만 다르다.

FULL (OUTER) JOIN

양 쪽 레코드 전체

한 쪽 테이블에만 존재하는 레코드는, 다른 쪽 테이블의 필드를 NULL로 반환한다.

CROSS JOIN

양 테이블의 모든 레코드들의 조합

SELF JOIN

동일한 테이블 두 개로 조인한다.

CARTESIAN JOIN

CROSS JOIN과 같으나 조인 조건이 없는 경우

SELCT A.*, B.*
FROM A
___ JOIN B on A.key1 = B.key1 and A.key2 = B.key2
WHERE ... ;

JOIN시 고려해야할 점

• 중복 x, PK uniqueness
• 조인하는 테이블간의 관계
  • 1:1
    • 완전한 1:1
      • user_session_channel & session_timestamp
    • 한 쪽이 부분집합이 되는 1:1
      • inner join, outer join에 따라 결과가 달라진다.
      • user_session_channel & session_transaction
  • 1:N
    • order & order_items
    • 중복이 문제가 된다. (증폭된다.)
  • N:1
  • N:N
• 어떤 테이블을 베이스로 할지
  • OUTER JOIN에서만 중요하다.

지난 시간 숙제 리뷰

내가 짠 코드

CREATE TABLE t AS
SELECT
		TO_CHAR(B.ts, 'YYYY-MM') AS month,
		A.channel AS channel, 
		COUNT(DISTINCT A.userId) AS uniqueUsers,
		COUNT(DISTINCT CASE WHEN C.sessionId IS NOT NULL THEN A.userId END) AS paidUsers,
		(COUNT(DISTINCT A.userId)::FLOAT/COUNT(DISTINCT CASE WHEN C.sessionId IS NOT NULL THEN A.userId END)) AS conversionRate,
		SUM(amount) AS grossRevenue,
		SUM(CASE WHEN refunded is TRUE THEN amount ELSE 0 END) AS netREVENUE
FROM user_session_channel A
		JOIN session_timestamp B
				ON A.sessionId = B.sessionId
		LEFT OUTER JOIN session_transaction C
				ON B.sessionId = C.sessionId
GROUP BY TO_CHAR(B.ts, 'YYYY-MM'), A.channel

놓친 부분

1. amount가 0인 user를 paidUser로 고려하면 안된다.
   1. C.session Id IS NOT NULL 대신 amount > 0 을 써야 한다.
2. 나누기 때 DIVIDEDBYZERO가 나올 수 있다.
   1. NULLIF를 사용해 0을 NULL로 변경한다.
      1. paidUsers/NULLIF(uqniaueUsers, 0)
      2. 사칙연산에 NULL이 있으면 결과는 0이 된다.

모범답안

CREATE TABLE adhoc.keeyong_monthly_channel_summary
AS
SELECT TO_CHAR(ts, 'YYYY-MM') year_month,
    usc.channel,
    COUNT(DISTINCT usc.userid) unique_users,
    COUNT(DISTINCT CASE WHEN amount>0 THEN userid END) paid_users,
    ROUND(paid_users*100./NULLIF(unique_users,0),2) conversion_rate,
    SUM(amount) gross_revenue,
    SUM(CASE WHEN refunded is False THEN amount
        ELSE 0 END) net_revenue
  FROM raw_data.user_session_channel usc
  JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
  LEFT JOIN raw_data.session_transaction str ON usc.sessionid = str.sessionid
GROUP BY 1, 2;

그 외..

COALESCE

• NULL 값을 다른 값으로 바꿔주는 함수’
• ex
  • COALESCE(exp1, exp2, exp3, …)
    • exp1부터 인자를 하나씩 살펴서 NULL이 아닌 값이 나오면 그 값을 리턴한다.
    • 모두 NULL이면 NULL을 리턴

공백 혹은 예약 키워드를 필드 이름으로 사용하려면

“”로 감싸서 사용한다.

DROP TABLE IF EXISTS adhoc.keeyong_test;
CREATE TABLE adhoc.keeyong_test (
    group int primary key,
    'mailing address' varchar(32)
);

위의 코드를 아래처럼 써야한다.

CREATE TABLE adhoc.keeyong_test (
    "group" int primary key,
    "mailing address" varchar(32)
);