Redshift - 기타 고급 문법 소개와 실습

 

 

 

알아두면 유용한 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 first_name, 'han' as last_name

UNION ALL

SELECT 'elon', 'musk'

UNION ALL

SELECT 'keeyong', 'han'

 

 

 

 

 

EXCEPT (MINUS)

하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능

 

INTERSECT (교집합)

여러 개의 SELECT문에서 같은 레코드들만 찾아줌

 

 

 

 

 

 

 

 

 

 

 

 

LISTAGG

GROUP BY에서 사용되는 Aggregate 함수 중의 하나
사용자 ID별로 채널을 순서대로 리스트:

 

SELECT userid, LISTAGG(channel, '->') WITHIN GROUP (ORDER BY ts) channels
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1
LIMIT 10;

 

 

 

 

 

LAG

WINDOW 함수 중 하나

 

어떤 사용자 세션에서 시간순으로 봤을 때
앞 세션의 채널이 무엇인지 알고 싶다면?
혹은 다음 세션의 채널이 무엇인지 알고 싶다면?

 

 

 

 

SELECT 
    usc.*, 
    st.ts, 
    LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts) prev_channel
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
ORDER BY usc.userid, st.ts
LIMIT 100;

 

맨 첫번째 채널은 이전 채널이 존재하지 않을테니 NULL(구글 코랩은 python이므로 None)이 나옵니다.

 

이전 채널이 아닌 이후 채널이 궁금하면 LAG OVER의 ORDER BY에 DESC를 추가하면 됩니다.

 

 

 

 

WINDOW

ROW_NUMBER OVER
FIRST_VALUE
LAST_VALUE
LAG

AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

 

 

 

 

 

 

 

 

JSON parsing 함수

JSON의 포맷을 이미 알고 있어야 사용할 수 있습니다.
JSON string을 입력 받아 특정 필드의 값에 접근할 수 있습니다.

 

{
  "f2": {
    "f3": "1"
  },
  "f4": {
    "f5": "99",
    "f6": "star"
  }
}

 

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3": "1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');

 

 

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3": "1"},"f4":{"f5":"99","f6":"star"}}','f4');