[TIL - 25일 차] 데이터 웨어하우스와 SQL과 데이터 분석 (5)

트랜잭션과 기타 고급 SQL 문법

트랜잭션이란?

트랜잭션이란 Atomic하게 실행되어야 하는 SQL을 묶어서 하나의 작업처럼 처리하는 방법이다. 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있다. BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용한다. 만약 조회만 진행할 경우 트랜잭션으로 묶일 이유가 없다.

예시 (은행 계좌 이체)

계좌 이체는 인출과 입금의 두 과정으로 이루어진다. 인출은 성공했는데, 입금이 실패하는 경우가 생기는 상황이 생기면 안 되며, 두 과정은 동시에 성공하거나 실패해야 한다. 이를 Atomic하다고 하며, 이런 과정들은 트랜잭션으로 묶어주어야 한다.

아래의 코드에서는 마치 하나의 명령어처럼 처리된다. 즉 모두 성공하거나 실패하는 두 경우로 처리된다. 만약 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK을 실행하면 된다.

BEGIN;
  A의 계좌로부터 인출;
  B의 계좌로 입금;
END; -- END와 COMMIT은 동일

트랜잭션 커밋 모드 - autocommit

커밋(COMMIT)이란 데이터베이스에 데이터가 쓰여지는 것을 말한다.

• autocommit = True : 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰임
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT) / ROLLBACK으로 처리
• autocommit = False : 모든 레코드 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 커밋되지 않음

트랜잭션 방식 - Google Colab

기본적으로 모든 SQL문이 바로 커밋된다(autocommit=True). 이를 바꾸고 싶다면, BEGIN;END; 혹은 BEGIN;COMMIT;, ROLLBACK;을 사용하면 된다.

트랜잭션 방식 - psycopg2

autocommit이라는 파라미터로 조절 가능하다. autocommit=True면, 기본적으로 PostgreSQL의 커밋 모드와 동일하게 작동한다. 또한 autocommit=False면, 커넥션 객체의 .commit()과 .rollback()함수로 트랜젝션 조절이 가능하다.

DELETE FROM vs TRUNCATE

• DELETE FROM : 테이블에서 모든 레코드를 삭제, WHERE을 사용해서 특정 레코드만 삭제 가능, 속도 느림
• TRUNCATE : 테이블에서 모든 레코드를 삭제, WHERE/트랜잭션을 지원하지 않음

기타 고급 문법 소개

• UNION, EXCEPT, INTERSECT
• COALESCE, NULLIF
• LISTAGG
• LAG
• WINDOW 함수(ROW_NUMBER OVER, SUM OVER, FIRST_VALUE, LAST_VALUE)
• JSON Parsing 함수

UNION, EXCEPT, INTERSECT

• UNION(합집합) : 여러 개의 테이블이나 SELECT 결과를 하나의 결과로 합쳐줌
  • UNION은 중복 제거, UNION ALL은 중복 제거 X
• EXCEPT(MINUS, 차집합) : 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능
• INTERSECT(교집합) : 여러 개의 SELECT 결과에서 같은 레코드들만 찾아줌

COALESCE, NULLIF

• COALESCE(exp1, exp2, ...) : exp1부터 NULL이 아닌 것이 나오면 그 값을 리턴, 모두 NULL이면 NULL 리턴
• NULLIF(exp1, exp2) : exp1과 exp2의 값이 같으면 NULL을 리턴

LISTAGG

GROUP BY에서 사용되는 Aggregate 함수 중 하나이다. 아래의 코드는 userid 별로 채널을 순서대로 리스트화한 것이다.

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
GROUP BY 1
LIMIT 10;

-- 68 YoutubeGoogleInstagramYoutubeInstagramInstagramInstagramOrganicInstagramYoutube...

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
GROUP BY 1
LIMIT 10;

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

WINDOW

• Syntax : function(expression) OVER ( [ PARTITION BY expression] [ ORDER BY expression ] )
• 함수 : ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG + Math(AVG, SUM, COUNT, ...)

WINDOW - LAG

LAG 함수는 어떤 사용자 세션에서 시간 순으로 봤을 때, 앞 / 다음 세션의 채널이 무엇인지 알고 싶을 때 사용할 수 있다.

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;

JSON Parsing Functions

JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수이다.

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

/*
{
 "f2":{
 "f3":"1"
 },
 "f4":{
 "f5":"99",
 "f6":"star"
 }
}
*/