TIL_day23 GROUP BY, TIMESTAMP, CTAS

230510 수요일

 

학습 주제 - 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (3)

 

GROUP BY와 Aggregate 함수

GROUP BY는 Aggregate 함수와 함께 사용한다.

많이 쓰이는 Aggregate 함수에는 COUNT, SUM, AVG, MIN, MAX, LISTAGG 함수 등이 있다.

 

이들은 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산할 때 사용하는데 이때 두 단계를 거쳐 생각하고 사용할 수 있다.

 

1. 먼저 그룹핑할 필드를 결정한다. 이때 하나 이상의 필드가 될 수 있다. ex) GROUP BY 1,2,...

2. 그룹별로 계산할 내용을 결정하고 적절한 Aggregate 함수를 사용한다.

 

간단한 사용 예제 두 가지를 살펴보자.

-- 예제 1
-- 채널별로 그룹핑 후, 접속한 user가 많은 순으로 채널을 내림차순

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 3 DESC;

 

위의 예제 1에서 짚고 넘어가면 좋은 부분은 COUNT()와 DISTINCT가 함께 쓰인 부분이다.

DISTINCT는 NULL값을 제외해주지 않는데, COUNT함수는 NULL값을 제외해 주기 때문에 위의 예제에서 user_count에 대해 따로 NULL 값 체크를 해주지 않아도 된다!

 

 

-- 예제 2
-- 월별 유니크한 사용자 수

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;

 

날짜 표시 (TO_CHAR, LEFT, SUBSTRING, DATE_TRUNC)

위의 예제 2에 날짜 정보가 있다. 위의 예시를 사용해 날짜 정보를 표시하는 방법 4가지에 대해서 알아보자.

1. TO_CHAR(A.ts, 'YYYY-MM')

2. LEFT(A.ts, 7)

3. SUBSTRING(A.ts, 1, 7)

4. DATE_TRUNC('month', A.ts)

 

1, 2, 3번은 문자열로 반환되지만 4번 DATE_TRUNC()는 timestamp로 반환된다!

이때 항상 첫 번째 날을 포함하여 자르는데, 만약 위처럼 DATE_TRUNC의 첫 번째 인자에 month를 작성하였다면 해당 달의 첫 번째 날로 잘린다. 더 자세한 정보는 아래의 링크를 참조하자.

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_DATE_TRUNC.html

DATE_TRUNC 함수 - Amazon Redshift

 

 

 

CTAS와 CTE

CTAS(CREATE TABLE AS SELECT) 문은 현재 제공되고 있는 가장 중요한 T-SQL 기능 중 하나로, SELECT 문의 출력을 기반으로 새 테이블을 만드는 병렬 연산입니다. CTAS는 단일 명령을 사용하여 데이터를 만들고 테이블에 삽입하는 가장 간단하고 빠른 방법입니다.

출처 : https://learn.microsoft.com/ko-kr/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-ctas

CREATE TABLE AS SELECT (CTAS) - Azure Synapse Analytics

 

자주 조인하는 테이블들이 있다면 CTAS를 사용하여 조인해두면 편리하다.

사용 예시는 아래와 같다.

DROP TABLE IF EXISTS adhoc.sikhye_session_summary; 
-- adhoc 스키마 아래에 새로운 테이블을 생성할 건데 그 전에 동일한 테이블이 존재할 수 있으니 이를 방지한다.

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

 

 

 

잠깐!!!!!!!!!

바로 전 포스팅에도 작성했지만, 작업을 할 때 항상 데이터 품질을 꼭 확인해야 한다!

아래의 4가지를 꼭 기억하기 위해 각각의 경우들을 어떻게 체크하는지 코드를 통해 살펴볼 것이다. 

이때 중복 레코드 체크하기에서 CTE의 개념이 등장한다.

 

1. 중복 레코드 체크하기

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

3. Primary key uniqueness가 지켜지는지 체크하기

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

 

 

-- 1. 중복 레코드 체크하기
-- 같은 결과값이 나와야하는 서로 다른 3가지 구문을 통해 결과값을 비교하여 중복 레코드가 있는지 체크한다.
-- 가장 마지막 구문이 with를 사용하는 CTE 구문이다.

SELECT COUNT(1) FROM adhoc.sikhye_session_summary;

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

-- CTE(Common Table Expression) 구문
With ds AS (
  SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.sikhye_session_summary
)
SELECT COUNT(1)
FROM ds;

3가지 구문 모두 결과값은 같다.중복되는 레코드는 존재하지 않는다는 뜻이다.

 

https://learn.microsoft.com/ko-kr/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

WITH common_table_expression(Transact-SQL) - SQL Server

 

 

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

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

good

 

-- 3. Primary key uniqueness가 지켜지는지 체크하기

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

good

 

-- 4. 값이 비어있는 컬럼이 있는지 체크하기

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

값이 비어있는 컬럼은 없다!!