TIL_day25 Transaction

24.01.31 수

 

 

트랜잭션이란?

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

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

 

은행 계좌 이체가 좋은 예시

• 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐
• 만일 인출은 성공했는데 입금이 실패한다면?
• 이 두 과정은 동시에 성공하던지 실패해야 함 -> Atomic 하다는 의미
• 이런 과정들을 트랜잭션으로 묶어주어야 함
• 조회만 한다면 이는 트랜잭션으로 묶일 이유가 없음

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

-- END와 COMMIT은 동일
-- BEGIN 전으로 돌아가고 싶다면 ROLLBACK

 

트랜잭션 커밋모드: autocommit

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

 

DELETE FROM vs TRUNCATE

DELETE FROM table_name (not DELETE * FROM)

• 테이블에서 모든 레코드를 삭제(느림)
• DROP TABLE table_name
• WHERE 사용해 특정 레코드만 삭제 가능

TRUNCATE table_name

• 테이블에서 모든 레코드를 삭제(빠름)
• WHERE 절 지원 x
• Transaction 지원 x ⇒ Rollback x

 

알아두면 좋은 SQL 문법

UNION

• 합집합
• 여러 개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
• 다른 소스에서 생긴 레코드들을 묶어서 새로운 테이블들을 만들 때 아주 유용함
• 예를 들면 물건 판매를 Shopify와 Amazon에 동시에 한다면 각 사이트에서 판매 레코드들을 UNION으로 묶어서 새로운 테이블을 생성가능 (CTAS)
• 각 SELECT 문의 필드의 수와 타입이 동일해야 함
• UNION ALL (중복 제거 x)

EXCEPT

• 차집합
• 하나의 SELECT 결과에서 다른 SELECT 결과를 빼줌
• 기존 요약 테이블의 로직을 수정하는 경우, 수정 전후를 비교하거나 하는데 많이 사용됨 ⇒ QA용으로 아주 유용함
• 각 SELECT 문의 필드의 수와 타입이 동일해야 함
• EXCEPT 대신에 MINUS 사용 가능

INTERSECT

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

COALESCE (expression1, expression2,...)

• NULL 값을 다른 값으로 바꾸고 싶을 때 사용
• 첫 번째 expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고, 모두 NULL 이면 NULL을 리턴

NULLIF (expression1, expression2,...)

• expression1과 2의 값이 같으면 NULL을 리턴

LISTAGG

• GROUP BY에서 사용되는 Aggregate 함수 중에 하나

-- LISTAGG 예제
-- 사용자 ID 별로 채널을 순서대로 리스트
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;

-- 결과
-- Youtube->Google->Instagram->Youtube->Instagram->Instagram->Instagram->...

WINDOW 함수

• function(expression) OVER ([PARTITION BY expression] [ORDER BY expression])
• https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html
• 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

• ROW_NUMBER OVER
• SUM OVER
• FIRST_VALUE, LAST_VALUE

JSON Parsing 함수

• https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html
• JSON의 포맷을 이미 아는 상황에서만 사용 가능한 함수
• JSON String을 입력으로 받아 특정 필드의 값을 추출 가능 (nested 구조 지원)
• JSON_EXTRACT_PATH_TEXT

JSON_EXTRACT_PATH_TEXT function - Amazon Redshift

 

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

# 결과
# star