[TIL - 23일 차] 데이터 웨어하우스와 SQL과 데이터 분석 (3)

GROUP BY

GROUP BY 절 & Aggregate함수

GROUP BY를 활용하여 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산할 수 있다. 계산 과정은 두 단계로 이루어지며, 다음과 같다.

1. 그룹핑할 필드를 GROUP BY로 결정(하나 이상의 필드가 될 수 있음)
2. 그룹별로 계산을 위한 Aggregate 함수 사용(COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...)

예제 (1)

아래의 SQL 구문은 월별 세션 수를 카운트한다. timestamp의 형식은 "yyyy-mm-dd ~"이므로 LEFT(ts, 7)은 "yyyy-mm"이 되며, mon으로 그룹핑하여 월별 세션 수의 개수를 계산한다.

SELECT
  LEFT(ts, 7) AS mon,
  COUNT(1) AS session_count
FROM raw_data.session_timestamp
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(ts, 7)
ORDER BY 1;

예제 (2)

가장 많이 사용된 채널은 무엇인지 확인하려고 한다. 그전에 가장 많이 사용됐다는 기준을 사용자/세션에 따라 나눌 수 있을 것이다. 두 가지 경우를 모두 확인하는 코드를 작성한다.

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

예제 (3)

가장 많이 세션을 만들어낸 사용자 ID를 확인하려고 한다.

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

예제 (4)

월별 유니크한 사용자 수를 확인하려고 한다. 이게 바로 MAU(Monthly Active User)에 해당한다. INNER JOIN을 통해 ts와 userId를 모두 사용하여 SQL 구문을 작성하였다. 여기서 A와 B는 테이블의 alias이며, sessionId를 통해 JOIN 하였다.

• TO_CHAR(A.ts, 'YYYY-MM')
• = LEFT(A.ts, 7)
• = DATE_TRUNC('month', A.ts)
• = SUBSTRING(A.ts, 1, 7)

SELECT
  TO_CHAR(A.ts, 'YYYY-MM') AS month,
  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,
ORDER BY 1 DESC;

아래의 표는 INNER JOIN이 된 테이블의 레코드들을 작성한 것이다.

userId	sessionId	channel	ts
779	7cda...	Instagram	2019-05-01 0:36:00
230	94f1...	Naver	2019-05-01 2:53:49
369	7ed2...	Youtube	2019-05-01 12:16:27

예제 (5)

월별, 채널별 유니크한 사용자 수를 확인하려고 한다. GROUP BY를 month와 channel 두 개로 지정한 것 이외에는 위와 큰 차이가 없다.

SELECT
  TO_CHAR(A.ts, 'YYYY-MM') month,
  channel,
  COUNT(DISTINCT B.userId) 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;

CTAS

CTAS는 SELECT를 활용하여 테이블을 생성하는 것이다. 간단하게 새로운 테이블을 만들 수 있고, 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해 조인해 두면 편리해진다.

DROP TABEL IF EXISTS adhoc.name_session_summary

CREATE TABLE adhoc.name_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

데이터 품질 확인 방법

중복된 레코드 체크

다음 두 개의 카운트를 비교한다. 중복된 레코드가 존재한다면, 두 번째 실행한 코드에서 더 적은 수가 카운트될 것이다.

SELECT COUNT(1)
FROM adhoc.name_session_summary;

SELECT COUNT(1)
FROM (
  SELECT DISTINCT userId, sessionId, ts, channel
  FROM adhoc.name_session_summary
);

CTE를 사용해 중복 제거 후 카운트 해보기

위의 코드에서 FROM에 작성했던 테이블을 ds로 지정한 뒤 SQL 구문을 실행하였다. 실행 결과는 동일하다.

With ds AS (
  SELECT DISTINCT userId, sessionId, ts, channel
  FROM adhoc.name_session_summary
)

SELECT COUNT(1)
FROM ds;

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

처음으로 업데이트된 시간과 마지막으로 업데이트된 시간을 확인할 수 있다.

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

Primary Key Uniqueness 체크

Primary Key인 sessionId를 카운트하여 1보다 큰 것이 존재하는지 확인한다. 만약 1보다 크면 Uniqueness가 지켜지지 않는 것이라 판단할 수 있다.

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

값이 비어있는 컬럼 체크

아래의 코드로 각 컬럼별로 NULL의 개수를 확인할 수 있다.

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

숙제

채널별 월별 매출액 테이블 만들기

Table	Fields
session_timestamp	sessionId (string), ts (timestamp)
user_session_channel	userid (integer), sessionid (string), channel (string)
session_transaction	sessionid (string), refunded (boolean), amount (integer)
channel	channelname (string)

1. adhoc 밑에 CTAS로 본인이름을 포함한 테이블로 만들기
2. session_timestamp, user_session_channel, session_transaction 사용해 아래와 같은 필드로 구성

• month
• channel
• uniqueUsers (총 방문 사용자)
• paidUsers (구매 사용자: refund한 경우도 판매로 고려)
• conversionRate (구매사용자 / 총 방문 사용자)
• grossRevenue (refund 포함)
• netRevenue (refund 제외)

문제 풀이 및 모범 답안

[숙제 - 23일 차] 데브코스 SQL 숙제