1. GROUP BY와 AGGREGATE 함수

• GROUP BY는 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산하는 함수입니다.

• 두 단계로 이루어집니다.

  1. 먼저 그룹핑을 할 필드를 지정합니다. (하나 이상의 필드)
     => GROUP BY로 지정합니다. (필드명 or 필드의 일련번호로 지정)
  2. 다음 그룹별로 계산할 내용을 결정합니다.
     => Aggregate 함수를 사용 하며, COUNT, SUM, MIN, MAX, LISTAGG 등이 있습니다. (보통 필드 이름을 지정합니다. "alias")

예제1 (월별 세션수를 계산하는 SQL)

• raw_data.session_timestamp를 사용합니다.(sessionId와 ts 필드)

연도-월에 해당하는 mon이라는 필드명을 지정한 후, mon을 그룹핑하여 각 그룹의 세션 수를 카운트하여 session_count로 명명합니다.

SELECT
	LEFT(ts, 7) AS mon, -- "YYYY-MM"까지만 추출됨.
    COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1 -- GROUP BY mon 혹은 GROUP BY LEFT(ts, y)와 동일한 기능.
ORDER BY 1;

예제2 (가장 많이 사용된 채널은 무엇인가?)

위 질문에 대해서는 반론이 필요합니다.

• 가장 많이 사용되었다는 정의가 무엇입니까?
  예) 사용자 기반인가요 아니면 세션 기반인가요 등

그러고나서 필요한 정보 등을 생각합니다.

• 필요한 정보 : 채널, 사용자 혹은 세션

• 필요한 정보를 토대로 어느 테이블을 사용해야하는지 고민합니다.
  => 해당 예제의 경우, 채널, 사용자 혹은 세션의 정보만 필요하기에 user_session_channel 테이블만 사용합니다.

SELECT
	channel, -- 채널 필드
    COUNT(1) AS session_count -- 채널로 그룹핑한 후, 각 채널에 대해서 카운트(세션 수).
    COUNT(DISTINCT userId) AS user_count -- 채널로 그룹핑한 후, 각 채널에 대해서 (중복X, 유일한) 유저ID 수 카운트. channel1 : user1 , channel1 : user2, channel1 : user1 -> channel1에 대해서 2로 카운트됨.
FROM raw_data.user_session_channel
GROUP BY 1 -- (SELECT의 첫 번째 필드인) 채널 필드로 그룹핑.
ORDER BY 2 DESC; -- 세션 수가 많은 것부터 출력.

예제3 (가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?)

• 필요한 정보 : 세션 정보, 사용자 정보
• 필요한 테이블 : user_session_channel

SELECT
	userId,
    COUNT(1) AS session_count
FROM raw_data.user_session_channel
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

예제4 (월별 유니크한 사용자 수)

• 필요한 정보 : 시간 정보, 사용자 정보
• 필요한 테이블 : session_timestamp, user_session_channel. 두 테이블을 sessionId로 조인해야 함.

SELECT
	LEFT(A.ts, 7) AS month, -- TO_CHAR(A.ts, 'YYYY-MM')와 동일.
    COUNT(DISTINCT B.userId) AS mau,
FROM
-- Inner Join의 예시. 양쪽에 같은 sessionId가 존재하는 레코드들만 갖고 오는 Join.
  raw_data.session_timestamp A
  JOIN raw_data.user_session_channel B
  ON A.sesssionId = B.sessionId
GROUP BY 1
ORDER BY 1 DESC;

• 위에서 TO_CHAR()는 timestamp를 문자열로 변환하는 함수입니다.

• DATE_TRUNC('month', A.ts) : date 타입의 월 데이터로 변환하는 함수입니다.
  ex) 2019.01.15 15:00에 적용하면 2019.01.01로 리턴됩니다. (일수는 무조건 "01"로 반환.)

• INNER JOIN : 두 테이블에서 매칭되는 필드값을 가진 레코드들만 조인됩니다.

• LEFT JOIN : 두 테이블에서 왼쪽에 있는 테이블은 무조건 조인되고, 오른쪽에 있는 테이블에서는 매칭되는 필드값을 가진 레코드들만 조인됩니다.

• RIGHT JOIN : 두 테이블에서 오른쪽에 있는 테이블은 무조건 조인되고, 왼쪽에 있는 테이블에서는 매칭되는 필드값을 가진 레코드들만 조인됩니다.

• OUTER JOIN : 두 테이블의 매칭되는 필드값과 관계없이 모두 조인됩니다.

예제5 (월별 채널별 유니크한 사용자 수)

필요한 정보 : 시간 정보, 사용자 정보, 채널 정보
필요한 테이블 : user_session_channel, session_timestamp. 두 테이블을 조인.

SELECT
	TO_CHAR(A.ts, 'YYYY-MM') AS month,
    channel,
    COUNT(DISTINCT B.userId) as mau,
FROM
	raw_data.session_timestamp A
    JOIN raw_data.user_session_channel B
    ON A.sessionId = B.sessionId
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

---

2. CTAS와 CTE

• CTAS는 SELECT를 가지고 테이블을 생성하는 방법입니다.

• 자주 조인되는 테이블을 미리 만들어 놓을 때 자주 사용됩니다.

CREATE TABLE adhoc.table_name AS
	SELECT B.*, A.ts 
    FROM raw_data.session_timestamp A
		JOIN raw_data.user_session_channel B
        ON A.sessionId = B.sessionId;

예제 1 (CTAS 테이블을 활용하여 월 별 유니크한 사용자 수 확인하기)

SELECT
	TO_CHAR(ts, 'YYYY-MM') AS month,
    COUNT(DISTINCT userId) AS mau,
FROM
	adhoc.table_name
GROUP BY 1
ORDER BY 1 DESC;

데이터 품질 체크 리스트

• 중복된 레코드 체크

  -- 다음 두 테이블의 결과값 비교. 같으면 중복 X.
  SELECT COUNT(1)
  FROM adhoc.table_name;
  
  SELECT COUNT(1)
  FROM (
  	SELECT DISTINCT userId, sessionId, ts, channel
      FROM adhoc.table_name
      );

• CTE는 재사용 가능한 임시 테이블을 생성하는 용도입니다.

-- 앞의 예제1과 동일한 기능.
With ds AS(
	SELECT DISTINCT userId, sessionId, ts, channel
    FROM adhoc.table_name
)
SELECT COUNT(1)
FROM ds;

• 최근 데이터의 존재 여부 체크

SELECT MIN(ts), MAX(ts)
FROM adhoc.table_name;

• Primary Key uniqueness가 지켜지는지 체크

SELECT sessionId, COUNT(1)
FROM adhoc.table_name
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

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

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.table_name;