[TIL] 6주차_Day25: 데이터 웨어하우스와 SQL과 데이터분석 (5)

💡Today I Learned

• 데이터 웨어하우스와 SQL, 데이터분석에 대한 다섯 번째 수업을 진행했습니다.
• SQL advanced 문법 실습

 

---

 

1. 트랜잭션

: atomic하게 실행돼야하는 SQL을 묶어서 하나의 작업처럼 처리

: 동시에 실행 or 롤백돼야 함 (작업 처리의 단위)

: BEGIN - 트랜잭션 - END (혹은) BEGIN - 트랜잭션 - COMMIT

: ROLLBACK - begin 전의 상태로 돌아감 (트랜잭션 중 하나라도 실패하면 begin 이전으로 롤백)

 

- commit mode

autocommit = True: 모든 레코드의 수정/삭제/추가 작업이 기본적으로 바로 db에 쓰여짐 (자동으로 커밋됨) / 특정 작업 트랜잭션 구성하고싶을 경우 BEGIN~END(COMMIT)/ROLLBACK으로 처리 가능

autocommit = False: 모든 레코드의 수정/삭제/추가 작업이 COMMIT 호출이 될 때까지 커밋되지 않음

 

2. DELETE FROM vs. TRUNCATE

- DELETE FROM

: 테이블에서 모든 레코드 삭제

: 특정 레코드만 삭제 가능

: 모든 레코드 삭제해도 테이블 자체가 삭제되지는 x (DROP TABLE로..)

 

- TRUNCATE

: DELETE FROM보다 속도 빠름

: 전체 테이블의 내용 삭제시에는 유리

: WHERE 지원 x

: Transaction을 지원하지 x → 롤백 불가!! (롤백할 일 없는데, 테이블 레코드 빠르게 삭제하고싶은 경우)

 

3. UNION, EXCEPT, INTERSECT

- UNION(합집합)

: 여러 개의 테이블 or select 결과를 하나의 결과로 합쳐줌

: UNION은 중복 제거, UNION ALL은 중복 제거 x

*)주의할 점: 동일한 수의 필드 & 필드들의 타입 같기

ex) 정보를 읽어오는 데이터 소스(테이블)가 시간의 흐름에 따라 바꼈을 수도 있음 → 두 개의 테이블을 합집합시켜 추상화된 더 상위의 테이블을 만들어 작업에 사용할 수 있음  

 

- EXCEPT(차집합)

: 하나의 select결과에서 다른 select 결과를 빼주는 것

*)주의할 점: 동일한 수의 필드 & 필드들의 타입 같기

 

- INTERSECT(교집합)

: 여러 개의 select문에서 같은 레코드들만 찾아줌

 

4. LISTAGG

: GROUP BY에서 사용되는 집계함수 중 하나 (list aggregation)

: 사용자 id별로 채널을 순서대로 리스트 (사용자 id로 그룹핑하고 채널명을 붙여나갈 때_문자열 연결하듯_ ts값을 기준으로 오름차순으로 listing)

: LISTAGG(붙일 필드, delimeter)

: 그룹핑 된 레코드들 안에서 특정 필드 값을 쭉 붙여줌

SELECT
	userid,
    	LISTAGG(channel, '->') WITHIN GROUP(ORDER BY ts) channels
FROM raw_data.user_session_channel usc
	JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
ORDER BY 1
LIMIT 10;

 

5. WINDOW 함수

ex) func(expression) OVER ([PARTITION BY expression]) [ORDER BY expression])

 

- LAG

: 이전/다음 필드 값 알고싶을 때

: 하나 이전채널 = LAG(channel, 1)

: 하나 다음채널 = LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts DESC) prev_channel

SELECT usc.*, st.ts,
	LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts) prev_channel
FROM raw_data.user_session_channel usc
	JOIN raw_data.session_timestamp st
    	ON usc.sessionid = st.sessionid
ORDER BY usc.userid, st.ts
LIMIT 100;

 

- JSON 파싱 함수

: (SELECT문에서) JSON_EXTRACT_PATH_TEXT