Week 6 - 3

GROUP BY

테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산

• 먼저 GROUP BY로 그룹핑을 할 필드를 결정(하나 이상의 필드가 될 수 있음, 필드 이름 또는 필드 일련번호를 사용)
• 다음 그룹별로 계산할 내용을 결정
  • Aggregate 함수를 사용
  • COUNT, SUM, AVG, MIN, MAX 등등

※ TO_CHAR(str, 'YYYY-MM') vs LEFT(str, 7) vs DATE_TRUNC('month', str) vs SUBSTRING(str, 1, 7)
   TO_CHAR, LEFT, SUBSTRING은 다 리턴값이 문자열이지만 DATE_TRUNC는 시간.
    DATE_TRUNC 문서 참고 https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html

SELECT
    channel,
    COUNT(1) AS session_count,
    COUNT(DISTINCT userId) AS user_count
FROM raw_data.user_session_channel
GROUP BY 1                         -- GROUP BY channel
ORDER BY 2 DESC;              -- ORDER BY session_count DESC

CTAS(CREATE TABLE table_name AS SELECT TABLE A)

간단하게 새로운 테이블을 만드는 방법. 자주 조인하는 테이블들이 있다면 CTAS를 사용해서 조인해두면 편리함

DROP TABLE IF EXISTS adhoc.joonghyeon_session_summary;
CREATE TABLE adhoc.joonghyeon_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;

CTE

중복된 레코드 체크하기

SELECT COUNT(1)
FROM adhoc.joonghyeon_session_summary;
-- code A


SELECT COUNT(1)
FROM (
	SELECT DISTINCT userID, sessionID, ts, channel
    FROM adhoc.joonghyeon_session_summary
);
-- code B

-- 두개 결과 확인하기

with ds AS (
	SELECT DISTINCT userID, sessionID, ts, channel
    FROM adhoc.joonghyeon_session_summary
)
SELECT COUNT(1)
FROM ds;
-- CTE를 사용해서 중복제거 후 카운트(code B와 결과는 완전 같지만 CTE를 사용하면 ds 테이블을 계속 사용 가능)

최근 데이터의 존재 여부 체크하기(freshness)

SELECT MIN(ts), MAX(ts)
FROM adhoc.joonghyeon_session_summary
-- 타임스탬프 필드를 찾아보는 방법

Primary Key uniqueness가 지켜지는지 체크하기

SELECT sessionID, COUNT(1)
FROM adhoc.joonghyeon_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT(1)
-- sessionID를 그룹핑하여 COUNT를 해주고 COUNT된 값으로 내림차순으로 해주는 방법

값이 비어있는 컬럼들이 있는지 체크하기

SELECT
	COUNT(CASE WHEN sessionID is NULL THEN 1 END) sessionid_null_count,
    COUNT(CASE WHEN userID is NULL THEN 1 END) userid_null_count,
    COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
    COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count,
FROM adhoc.joonghyeon_session_summary;
-- NULL일 때 1을 리턴 아니면 0 리턴