오늘 배운 것🤓

Transaction

• Atomic하게 실행되어야 하는 SQL들을 하나의 작업처럼 처리하는 방법
• atomic : 여러 SQL이 동시에 성공/실패해야 데이터의 정합성을 유지할 수 있는 경우
  (예. 은행 계좌 이체)
• 이는 DDL이나 DML 중 레코드를 수정/삭제/추가 했을 때만 적용(즉, SELECT에는 관여 X)
• BEGIN; ~ END; 혹은 BEGIN; ~ COMMIT; 사이에 트랜잭션을 적용하고 싶은 SQL을 사용
• BEGIN과 END/COMMIT(둘은 같다.) 사이에 여러 SQL은 마치 하나의 명령어처럼 동작한다. 모두 성공하던지 모두 실패하던지 둘 중 하나로만 동작
• BEGIN전으로 돌아가고 싶다면 ROLLBACK을 사용한다.

BEGIN;

INSERT INTO ~~~;
UPDATE SET ~~~;
...

END;  -- COMMIT;

• commit mode에 따라서 BEGIN의 사용 여부 결정

  • commit mode는 autocommit의 값에 의해 결정
  • autocommit=TRUE
    모든 작업이 기본적으로 바로 DB에 쓰여진다.
    특정 작업을 트랜잭션으로 묶고 싶을때 BEGIN을 사용한다.
  • autocommit=FALSE
COMMIT을 호출할 때까지 커밋되지 않는다.
    내가 보는 DB에서는 바로 적용된 것 같아 보이지만 다른 사용자의 DB에는 적용이 되지 않으므로 COMMIT 필수!
    python(psycopg1 라이브러리 사용)처럼 외부 언어에서 db의 데이터를 수정해야할 때 유용하다.
  • 구글 colab에서는 무조건 autocommit=TRUE

• TRUNCATE
  : DELETE 보다 빠르다는 장점이 있지만 WHERE문과 트랜잭션에 적용하지 못한다.
  다시는 필요없는 데이터를 빠르게 삭제하고 싶을 때만 사용한다.

알아두면 좋은 SQL 문법

• UNION(합집합), EXCEPT/MINUS(차집합), INTERSECT(교집합)
• COALESCE(), NULLIF()
• window 함수
  • 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수
  • 기본 형태 : WINDOW_FT OVER ([PARTITION BY field_name][ORDER BY field_name])
  • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG, LISTAGG
  • math : SUM, AVG, MEDIAN, COUNT, MAX, MIN, NTH_VALUE
• LISTAGG
  : grouping된 데이터 중 특정 field의 값을 쭈욱 붙혀서 반환
• LAG
  : 특정 field를 기준으로 원하는 lag만큼 이동하여 비교
• JSON Parsing Function
  • JSON 포맷을 이미 아는 상황에서만 사용 가능
  • JSON_EXTRACT_PATH_NEXT(json, key1, key2, ...) : 첫 번째 인자에는 json을 두 번째 인자부터는 outer부터 inner순으로 원하는 값을 도출하기 위한 key를 넣어준다.
  • key는 하나만 넣어도 되고 꼭 하나의 value가 나올 때까지 넣지 않아도 된다. json자체가 남는다면 그 형태대로 str로 반환

느낀 점😊

PostgreSQL에서 적용되는 함수와 RedShift에서 적용되는 함수가 조금씩 다른 것 같다. 이 점은 모아서 정리해야할 것 같다. PostgreSQL에서 제공하는 샘플 데이터를 찾았다. 이 데이터를 사용해서 더 다양한 예제를 직접 만들고 비교해봐야지.