SQL 학습 - GROUP BY, CTAS (TIL 230510)

📚 KDT WEEK 6 DAY 3 TIL

• GROUP BY
• CTAS

 

---

 

🟥 GROUP BY & Aggregate

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

1. 그룹핑할 하나 이상의 필드를 결정한다.
2. 그룹별로 계산할 내용을 결정하고 함수를 사용
   • COUNT, SUM, AVG, MIN, MAX, LISTAGG...

 

예시 1)

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;

raw_data.session_timestamp 테이블에서 월별 총 세션 수를 구하는 쿼리문이다.

GROUP BY 1 을 사용하면, 1에 해당하는 mon이 그룹핑된다.

따라서 그룹핑된 mon별로 COUNT(1)을 통해 총 세션의 수를 구할 수 있게된다.

 

예시 2)

SELECT
    channel,
    COUNT(1) AS session_count,
    COUNT(DISTNICT 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

raw_data.user_session_channel 테이블에서 채널별 세션 수를 내림차순으로 구하는 쿼리문이다.

 

예시 3)

SELECT
    TO_CHAR(A.ts, 'YYYY-MM') AS month,
    COUNT(DINTINCT 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)하여 월별 유니크한 사용자 수를 구하는 쿼리문이다.

• TO_CHAR의 경우 non char타입의 데이터를 해당 형태(YYYY-MM)의 문자열로 바꿔주는 역할을 한다.
• LEFT(A.ts, 7) 과 SUBSTRING(A.ts, 1, 7)은 받은 인자값에 따라 문자열로 잘라주는 역할로써 대신 사용 가능하다.
• timestamp 타입을 유지한다면 DATE_TRUNC('month', A.ts)을 사용한다.

 

---

 

🟦 CTAS

생성과 동시에 AS SELECT 문을 붙여서 데이터까지 동시에 넣어주는 방법

자주 JOIN하는 테이블이 있다면 유용하다.

 

CREATE TABLE 테이블이름 AS SELECT ~

 

ex)

DROP TABLE IF EXISTS adhoc.test_session_summary; -- 이미 존재하는 건 삭제
CREATE TABLE adhoc.test_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;

임시로 사용할 테이블이므로 raw_data 폴더가 아닌 adhoc 폴더에 생성하도록 한다.

AS SELECT 구문 밑으로 나오는 테이블을 test_session_summary 이름으로 새로운 테이블 생성할 수 있다.

 

 

---

 

숙제