트랜잭션

트랜잭션이란 (1)

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는
  방법
  • 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음
  • SELECT에는 트랜잭션을 사용할 이유가 없음
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
  • ROLLBACK
• 은행계좌이체가아주좋은예
  • 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐
  • 만일 인출은 성공했는데 입금이 실패한다면?
  • 이 두 과정은 동시에 성공하던지 실패해야함 ➡️ Atomic하다는 의미
  • 이런 과정들을 트랜잭션으로 묶어주어야함
  • 조회만 한다면 이는 트랜잭션으로 묶일 이유가 없음

트랜잭션이란 (2)

BEGIN;
  	A의 계좌로부터 인출; ➡️ 이 명령어들은 마치 하나의 명령어처럼 처리됨
    B의 계좌로 입금; ➡️ 다 성공하던지, 다 실패하던지
END;

• END와 COMMIT은 동일
• 만일 BEGIN전의 상태로 돌아가고 싶다면 ROLLBACK 실행
• 이 동작은 commit mode에 따라 달라짐

트랜잭션 커밋 모드: autocommit

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

트랜잭션 방식

• Google Colab의 트랜잭션
  • 기본적으로 모든 SQL statement가 바로 커밋됨 (autocommit=True)
  • 이를 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT을 사용 (혹은 ROLLBACK;)
• 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을 사용해 특정 레코드만 삭제 가능
    • DELETE FROM raw_data.user_session_channel WHERE channel = ‘Google'
• TRUNCATE table_name도 테이블에서 모든 레코드 삭제
  • DELETE FROM은 속도가 느림
  • TRUNCATE이 전체 테이블의 내용 삭제시에 유리
  • 단점 존재
    • TRUNCATE는 WHERE절을 지원하지 않음
    • TRUNCATE는 Transaction을 지원하지 않음

기타 고급 문법

UNION, EXCEPT, INTERSECT

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

COALESCE, NULLIF

• COALESCE(Expression1, Expression2, ...):
  • 첫번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다.
  • NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
• NULLIF(Expression1, Expression2):
  • Expression1과 Expression2의 값이 같으면 NULL을 리턴한다

LISTAGG (1)

• GROUP BY에서 사용되는 Aggregate 함수 중의 하나
• 사용자 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;

[결과]
68YoutubeGoogleInstagramYoutubeInstagramInstagramInstagramOrganicInstagramYoutube...

LISTAGG (2)

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 ] )
• Useful functions :
  • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
  • Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

WINDOW - 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

• https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html
• JSON의 포맷을 이미 아는 상황에서만 사용 가능한 함수
  • JSON String을 입력으로 받아 특정 필드의 값 추출 가능 (nested 구조 지원)
• 예제
  - SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');

  이 글에서 소개된 내용은 프로그래머스 데이터분석1기에서 진행된 한기용 강사님의 온라인 강의를 참조하여 작성되었습니다.