[DE 데브코스] 05.10 TIL - SQL(2)

GROUP BY와 Aggregate Function

• 테이블의 레코드를 그룹으로 묶어 그룹별로 다양한 정보를 계산할 수 있다.
• (1) 그룹핑할 필드를 하나 이상의 필드로 먼저 결정하고
• (2) COUNT, SUM, AVG 등의 Aggregate 함수를 이용하여 그룹별로 연산을 진행한다.
• GROUP BY 1은 이름에 관계없이 첫 번째 컬럼으로 그룹화하는 것을 의미한다. (ORDER BY도 마찬가지)
• 아래 코드 같은 경우, SELECT 절의 첫 번째 컬럼인 mon을 기준으로 그룹핑, 정렬(오름차순)을 하겠다는 뜻.

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

 

SQL 기반 문제 해결 예시

• 만약 "가장 많이 사용된 채널은 무엇인가?"라는 질문을 받았을 때, 이 질문에 대해서 역으로 질문할 줄 알아야 한다.
• 유입된 유저 데이터가 유저id, 세션으로 정의되었을 경우 가장 많이 사용되었다는 정의가 세션인지, 유저 수인지 개념을 정립해야 하기 때문이다.

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

• 위 코드는 세션 수를 바탕으로 가장 많이 사용된 채널을 알아보는 쿼리다. ORDER BY 2 DESC로 알 수 있다.
• 개인적인 선호에 따라 1,2 등의 숫자를 이용하거나 혹은 명확히 하기 위해서 필드 명을 group by에 사용해도 무방하다.
• 필요에 따라서 테이블을 JOIN하는 등의 연산을 진행해야 한다.
• 아래의 DATE_FORMAT 함수는 날짜, 숫자 등의 값을 문자열로 반환하는 함수다.
• ORACLE이나 PostgreSQL에서는 to_char를 사용하지만, mysql에서는 DATE_FORMAT을 사용한다.

SELECT
    DATE_FORMAT(A.ts, '%Y-%m') AS month, -- in oracle, use 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;

• JOIN 앞에 아무런 접두사가 없으면 INNER JOIN으로, 조건에 매칭된 레코드만 나타내겠다는 뜻이 된다.
• 반면 LEFT, RIGHT JOIN은 왼쪽/오른쪽 테이블의 레코드를 무조건 포함한다는 것이다.
• Alias 사용에 AS는 옵션이다. COUNT(*) AS mau나 COUNT(*) mau나 똑같다는 말이다.

 

CTAS와 CTE

• CTAS는 SELECT를 가지고 테이블을 생성하는 것이다.
• 데이터 분석 시 JOIN으로 새로운 테이블을 만들 때, 많이 사용하는 테이블을 미리 만들어 두는 데 사용한다.
• 자주 조인하는 테이블이 있다면 이를 CTAS를 사용해서 조인해두면 편리하다.
• CTE는 공통 테이블 표현식으로써, 중첩된 서브쿼리를 간단하게 표현할 수 있도록 도와주는 기능이다.
• WITH 키워드를 사용하며, 일반적으로 다음과 같은 구조로 재사용이 가능한 임시테이블을 만든다.

WITH [CTE 이름] AS (
    [CTE 쿼리]
)
SELECT [컬럼1], [컬럼2], ...
FROM [기본 테이블]
JOIN [CTE 이름] ON [조인 조건]

 

Primary key uniqueness가 지켜지는지 체크하는 코드 간단 예시

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

• 이렇게 그룹 바이를 하고 COUNT를 내림차순으로 정렬해보면 간단하게 확인할 수 있다.
• COUNT 값이 1보다 크면 PK 유일성이 지켜지지 않은 것이다.

 

오늘 공부하며 어려웠던 내용

• SQL에 아직 적응이 완벽히 안되어서 어려움은 있었지만, 시간이 해결해 줄 문제.