SQL 4. Transaction, 고급문법

Transaction

Transaction이란

Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법이다. 이는 DDL이나 DML 중 레코드를 Update, Create, Delete한 것에만 의미가 있다.(SELECT에는 트랜잭션을 사용할 이유가 없음) BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 SQL들을 사용하고 하나라도 실패하면 ROLLBACK한다.

Autocommit

• Autocommit = True
  모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여지며 이를 commit된다고 한다. 만일 특정 작업을 transaction으로 Atomic하게 실행하고 싶다면 BEGIN과 END(COMMIT) 묶어주면된다.

• Autocommit = False
  모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여지지 않고 COMMIT이 호출될때 비로소 쓰여진다. 이 상태로 쓸일은 별로 없을 것이다.

  트랜잭션 방식

• Google Colab의 트랜잭션
  기본적으로 모든 SQL statement가 바로 커밋되고 이를 바꾸고 싶다면 BEGIN;END; 혹은 BEGIN;COMMIT;을 사용 (혹은 ROLLBACK;)

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

  DELETE FROM vs TRUNCATE

• DELETE FROM table_name
  기본적으로 테이블의 모든 레코드를 삭제하는 명령어이며 특정 레코드만 삭제하고 싶다면 WHERE문을 활용하면된다. DROP TABLE table_name과 달리 DELETE FROM은 삭제하여도 Table은 남아있다. 그리고 Transaction으로 묶어주면 도중에 ROLLBACK가능하다.

• TRUNCATE table_name
  TRUNCATE또한 모든 레코드를 삭제하는 명령어이다. DELETE FROM 보다 속도가 빠르지만 TRUNCATE는 WHERE문을 지원하지 않고 Transaction 도 지원하지 않는다. Transaction을 지원하지 않아 수정후 ROLLBACK이 불가능하다.

  UNION, EXCEPT,INTERSECT

• UNION (합집합)
  여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌
  UNION 은 중복을 제거해주고 UNION ALL은 중복이 있더라도 내버려둔다.

• EXCEPT/MINUS (차집합)
  하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것

• INTERSECT (교집합)
  여러개의 SELECT 문에서 같은 레코드들만 찾아줌
  EXCEPT와 INTERSECT는 SQL을 수정하였을때 기존의 SQL과 새 SQL의 차이를 확인하고자 할때 자주 사용함, UNION EXCEPT,INTERSECT는 field가 동일해야 사용할 수 있다.(type, 이름)

  COALESCE, NULLIF

• COALESCE(EXP1, EXP2,...)
  NULL이 아닌 값을 가지는 EXP을 만나면 그 값을 가짐, 모든 EXP가 NULL이면 NULL을 가짐, 보통 NULL값을 다른 값으로 바꾸고 싶을때 많이 사용한다 ex) COALESCE(EXP1,0)

• NULLIF(EXP1,EXP2)
  EXP1과 EXP2가 같으면 NULL값을 가진다. 분모의 값이 0일때 error를 방지하기 위해 사용할 수 있다. ex)NULLIF(EXP1,0)

  LISTAGG

  GROUP BY 에서 사용되는 Aggregate 함수 중의 하나로 필드의 내용을 이어준다.(PSQL 에서는 STRING_AGG가 같은 역할)

  SELECT
  field_1
  LISTAGG(field_2) WITHIN GROUP (ORDER BY field)
  FROM ~
  GROUP BY 1
  LIMIT 10;

  WITHIN GROUP(ORDER BY)와 OVER(ORDER BY ROWS BETWEEN 전체)가 비슷한 역할을 하는듯?

  WINDOW-LAG

 LAG(field,1) OVER(ORDER BY ~) -- 이전 필드의 값
 LAG(field,1) OVER(ORDER BY ~ DESC) -- 이후 필드의 값

JSON Parsing Functions

JSON의 포맷을 이미 아는 상황에서만 사용 가능한 함수
ex) JSON_EXTRACT_PATH_TEXT(JSON_data,key1,key2..)
JSON_data 의 key1,key2..의 형태로 밖에서부터 읽어서 값을 반환
JSON의 포맷을 모르는 상황에서는 UDF(UserDefinedFunction)을 만들어 사용해야한다. AGG,WINDOW함수는 못만들고 일반함수만 만들 수 있다. 보통 파이썬으로 작성함.