SQL_Analysis 5

트랜잭션이란?

• Atomic하게 실행되야하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
  • atomic하다는 의미 모든 과정이 다성공하던지 다실패해야됨 즉 하나라도 실패하면 모든과정이 실패되는 처리가 되야됨
  • 이는 DDL이나 DML중 레코드를 수정/추가/삭제한 것에만 의미가 있음
  • SELECT에는 트랜잭션을 사용할 이유가 없음
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
  • ROLLBACK
• 은행 계좌 이체가 아주 좋은예

  • 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐

  • 만일 인출은 성공했는데 입금이 실패한다면?

  • 이과정은 동시에 성공하던지 실패해야됨 → Atomic

  • 이러한 과정들은 모두 트랜잭션으로 묶어서 처리해야됨

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

    END와 COMMIT은 동일

    만일 BEGIN전의 상태로 돌아가고싶다면 ROLLBACK실행

    이 동작은 commit mode에 따라 달라짐

트랜잭션 커밋 모드: autocommit

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

트랜잭션 방식

• Google Colab의 트랜잭션
  • 기본적으로 모든 SQL statement가 바로 커밋됨(autocommit = True)
  • 이를 바꾸고 싶다면 BEGIN; END; 혹은 BEGIN; COMMIT을 사용
• psycopg2의 트랜잭션
  • autocommit 이라는 파라미터로 조절가능
  • True가 되면 기본적으로 postgreSQL의 커밋모드와 동일
  • Flase가 되면 커넥션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절가능

DELETE FROM vs TRUNCATE

• DELETE FROM table_name (not DELETE * FROM)
  • 테이블의 모든 레코드를 삭제
  • DROP TABLE table_name은 테이블 자체가 날라감
  • WHERE을 사용하여 특정 레코드만 삭제 가능
• TRUNCATE table_name 도 모든 레코드 삭제
• DELETE FROM은 속도가 느림
• TRUNCASTE이 전체 테이블의 내용 삭제시 여러모로 유리
• 하지만 WHERE을 지원하지 않고 Transaction을 지원하지않음(롤백이 안먹음)

알아두면 유용한 SQL문법

• 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 vs UNION ALL
    • UNION은 중복을 제거
• EXCEPT(MINUS)
  • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는것이 가능
• INTERSECT(교집합)
  • 여러개의 SELECT 문에서 같은 레코드들만 남김

COALESCE, NULLIF

• COALESCE(Exp1, Exp2,….)
  • 첫번째 Exp부터 NULL이 아닐때까지 찾아값을리턴 전부다 NULL이면 NULL을 리턴
  • NULL값을 다른값으로 바꾸고싶을때 사용
• NULLIF(Exp1, Exp2)
  • Exp1이랑 Exp2가 같으면 NULL을 리턴

LISTAGG

• GROUP BY에서 사용되는 Aggregate함수중의 하나
• 사용자 ID별로 채널을 순서대로 리스트:

SELECT
	userid,
	LISTAGG(channel, '->') WITHIN GROUP (ORDER BY time) channles
FROM channel_table
GROUP BY 1

예시 결과

id | channels

68 Youtube→Google→Instagram→Youtube→Intstagram

WINDOW

• Syntax :
  • function(exp) OVER([PATRTITION BY exp][ORDER BY exp])
• Useful functions:

  • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG

  • math functions : AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH, VALUE

    LAG

  • 어떤 사용자 세션에서 시간순으로 봤을때

    • 앞 세션의 채널이 무엇인지 알고싶다면

    • 혹은 다음세션의 채널이 무엇인지 알고 싶다면

      SELECT 
      	channel_table.*, time_table.time,
      	LAG(channel, 1) OVER(PARTITION BY userid ORDER BY time) prev_channel
      FROM channel_table JOIN time_table on channel_table.id = time_table.id
      ORDER BY userid, time

      id | userid | channel | time | prev_channel

      1 | 1 | google | 2019|

      2 | 2 | Youtube| 2020| google

JSON Parser functions

• JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
  • JSON String을 입력으로 받아 특정 필드의 값을 추출가능
• 예제) JSON_EXTRACT_PATH_TEXT
  • SELECT JSON_EXTRACT_PATH_TEXT(’{”F2”:{”F3”:”1”}’})