[5주차] SQL을 이용한 데이터 분석(5)

🙂 트랜잭션

✔ 트랜잭션이란?

Atomic 하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

• DDL이나 DML 중 레코드를 수정/추가/삭제 한 것에만 의미가 있음.
• SELECT에는 트랜잭션을 사용할 이유가 없음.
• BEGIN - END 혹은 BEGIN - COMMIT 사이에 해당 SQL을 사용
• ROLLBACK

e.g. 은행 계좌 이체

BEGIN;
  A의 계좌로부터 인출;
  B의 계좌로 입금;
END;

• END와 COMMIT은 동일
• BEGIN 전의 상태로 돌아가고 싶으면 ROLLBACK 실행
• COMMIT MODE에 따라 상이

 

🍦 트랜잭션 커밋 모드: autocommit

autocommit = True

• 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. = commit
• 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT) / ROLLBACK으로 처리

autocommit = False

• 모든 레코드 수정/삭제/추가 작업이 commit 호출될 때까지 commit 되지 않음.

 

🍦 트랜잭션 방식

Google Colab의 트랜잭션

• 기본적으로 모든 SQL statement가 바로 커밋 (autocommit = True)
• 이를 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT; 사용

psycopg2의 트랜잭션

• autocommit이라는 파라미터로 조절
• autocommit=True가 되면 기본적으로 PostgreSQL의 커밋 모드와 동일
• autocommit=False가 되면 커넥션 객체의 .commit()과 .rollback() 함수로 트랜잭션 조절 가능

 

🍦 DELETE FROM vs. TRUNCATE

DELETE FROM table_name (not DELETE * FROM)

• 테이블에서 모든 레코드를 삭제
• vs. DROP TABLE table_name
• WHERE을 사용해 특정 레코드만 삭제 가능

TRUNCATE table_name도 테이블에서 모든 레코드를 삭제

• DELTE FROM보다 빠름
• TRUNCATE가 전체 테이블 삭제 시에는 유리
• WHERE 지원 X
• 트랜잭션 지원 X

---

🙂 기타 문법

🍦 UNION (합집합)

• 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
• UNION은 중복제거 UNION ALL은 중복 제거 x

🍦 EXCEPT (차집합)

• 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능

🍦 INTERSECT (교집합)

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

🍦 LISTAGG

• GROUP BY에서 사용되는 Aggregate 함수
• 사용자 ID별로 채널을 순서대로 리스팅

SELECT
  userid,
  LISTAGG(channel, '->') WITHIN GROUP (ORDER BY ts) channels	-- WITHIN은 순서를 붙이려 할 때 사용
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1;

68 Youtube -> Google -> Instagram -> Youtube -> Instagram -> ...

🍦 LAG

-- 이전 채널 찾기
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

-- 다음 채널 찾기
SELECT usc.*, st.ts,
  LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts DESC) next_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

🍦 JSON Parsing Functions

• JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
• JSON String을 입력으로 받아 특정 필드의 값을 추출가능 (nested 구조 지원)
• 이미 JSON String의 구조를 알고 있을 때 유용.

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"}, "f4":{"f5":"99", "f6":"star"}}', 'f4', 'f6};