25. 트랜잭션 및 SQL 고급 문법

학습주제

• 트랜잭션 소개와 실습
• 기타 고급 문법 소개와 실습

주요 메모 사항 소개

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

       BEGIN;
       	A의 계좌로부터 인출;   ## 두 명령어들을 마치 하나의 명령어처럼 처리가된다.
       	B의 계좌로 입금;       ## 다 성공하던지 다 실패하던지 둘중의 하나가 된다.
       END;

  • END와 COMMIT은 동일하다.
  • BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK 실행한다.
• 트랜잭션 커밋 모드 : autocommit
  • autocommit = True
    
    1. 모든 레코드 수정 / 삭제 / 추가 작업이 기본적으로 바로 데이터베이스에 쓰여진다. 이를 Commit이라한다.
    2. 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN과 END(COMMIT) / ROLLBACK으로 처리한다.
  • autocommit = False
    1. 모든 레코드 수정 / 삭제 / 추가 작업이 COMMIT 호출될 때까지 커밋되지 않는다.
• 트랜잭션 방식
  • Goggle Colab의 트랜잭션
    1. 기본적으로 모든 SQL statement가 바로 커밋된다 (autocommit = True)
    2. 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT을 사용(or ROLLBACK;)
  • psycopg2의 트랜잭션
    1. autocommit이라는 파라미터로 조절가능
    2. autocommit=True가 되면 기본적으로 PostgreSQL 커밋 모드와 동일
    3. autocommit=False가 되면 커네션 객체의 .commit()과 .rollback()함수로 트랜잭션 조절 가능하다.
    4. 사용은 개인의 취향
• DELETE FROM vs TRUNCATE
  • DELETE FROM table_name (not DELETE * FROM)
    1. 테이블에서 모든 레코드를 삭제한다.
    2. vs. DROP TABLE table_name
    3. WHERE 사용해 특정 레코드만 삭제 가능하다.
  • TRUNCATE table_name
    1. 테이블에서 모든 레코드를 삭제한다.
    2. DELETE FROM은 속도가 느리다.
    3. TRUNCATE가 전체 테이블의 내용 삭제시에는 여러모로 유리하다.
    4. 하지만 두가지 단점이 존재한다.
       • TRUNCATE는 WHERE를 지원하지 않는다.
       • TRUNCATE는 Transaction을 지원하지 않는다.

---

• 알아두면 유용한 SQL 문법들
  • UNION : 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐준다.
    1. UNION vs. UNION ALL ( UNION은 중복을 제거한다.)
  • EXCEPT(MINIS) : 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는것이 가능하다.
  • INTERSECT (교집합) : 여러 개의 SELECT 문에서 같은 레코드들만 찾아준다.
  • COALESCE(Expression1, Expression2, ...)
    1. 첫 번째 Expression 부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴
    2. NULL 값을 다른 값으로 바꾸고 싶을 때 사용한다.
  • NULLIF (Expression1, Expression2)
    1. Expression1과 Expression2의 값이 같으면 NULL을 리턴한다.
  • LISTAGG
    1. GROUP BY 에서 사용되는 Aggregate 함수 중의 하나이다.
    2. 사용자 ID별로 채널을 순서대로 리스트

       SELECT
       	userid,
       	LISTAGG(channel) WITHIN GROUP (ORDER BY ts) channels
       FROM ---
       
       ## -> YoutubeGoogleInstagram
       
       SELECT
       	userid,
       	LISTAGG(channel,'->') WITHIN GROUP (ORDER BY ts) channels
       FROM --
       
       ## -> Youtube->Google->Instagram​

  • WINDOW 함수
    • ROW_NUMBER OVER
    • SUM OVER
    • FIRST_VALUE, LAST_VALUE
  • JSON parsing 함수
    • JSON의  포맷을 이미 아는 상황에서만 사용가능한 함수
    • JSON String을 입력으로 받아 특정 필드의 값을 추출할 수 있다.

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

       

공부하며 어려웠던 내용

• JSON parsing을 보면서 이렇게도 쓸 수 있다고 생각했고 연습좀 해봐야 겠다.