SQL 함수와 Union

WITH A AS(), B AS(),C AS()
: A와 B와 C라는 임시 테이블을 생성

ROW_NUMBER() OVER(PARTITION BY A)
: 윈도우 함수중 하나로 결과 집합의 행 일련번호를 매겨주는 함수
  1부터 차례대로 넘버링 된다.

ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])

 

FIRST_VALUE/LAST_VALUE함수
: 처음값/마지막값을 리턴해주는 함수

FIRST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...)

 

트랜잭션이란?
: Atomic(동시에 성공하거나 동시에 실패해야 함)하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
- DDL이나 DML중 레코드를 수정/추가/삭제한 것에만 의미가 있다.
- SELECT에는 트랜잭션을 사용할 이유가 없다.
- BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
- ROLLBACK에 사용
ex) 은행 계좌 이체

트랜잭션 커밋 모드 : 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과 TRUNCATE의 차이
- DELETE FROM table_name(not DELETE* FROM)
  - 테이블에서 모든 레코드를 삭제
  - where 사용해 특정 레코드만 삭제 가능
- TRUNCATE table_name도 테이블에서 모든 레코들 삭제
  - DELETE FROM보다 속도가 빠르다
  - 특정 레코드만 삭제 불가(WHERE을 지원하지 않는다)
  - 복구 불가능 = Rollback불가능 (Transaction을 지원하지 않는다.)

UNION(합집합)
- 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐준다.
- 각 SELECT문의 필드들의 수와 타입이 동일해야한다.
- UNION(중복 존재X) vs UNION ALL(중복 존재 가능)
  - UNION은 중복을 제거함.
- EXCEPT(MINUS, 차집합)
  : 하나의 SELECT결과에서 다른 SELECT결과를 빼주는 것이 가능하다.
- INTERSECT(교집합)
  : 여러개의 SELECT문에서 같은 레코드들만 찾아줌.

COALESCE(A1,A2,...)
: 첫번째 A1부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다.
-NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
NULLIF(A1,A2)
: A1과 A2의 값이 같으면 NULL을 리턴한다. 다를 경우 그대로 리턴

LISTAGG
: 컬럼의 레코드들을 전체 그대로, 또는 그룹별로 1개 칸 속에 나열한다.
- GROUP BY 에서 사용되는 Aggregate함수 중의 하나
LISTAGG([합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명]) 

WINDOW - LAG 함수
: 이전 행의 값을 리턴
LAG(A,m,n) OVER(ORDER BY B)
- 컬럼B를 기준으로 레코드를 정렬한 뒤
- 컬럼A의 값을 기준으로,
- 정렬된 레코드 중 직전 m번째 레코드 값 출력
- 출력할 값이 없으면(컬럼B 기준 정렬 시 첫번째 행) n 출력

WINDOW - LEAD 함수(LAG의 ORDER BY DESC와 같음)
: 다음 행의 값을 리턴
LEAD(A,m,n) OVER(ORDER BY B)
- 컬럼B를 기준으로 레코드를 정렬한 뒤
- 컬럼A의 값을 기준으로,
- 정렬된 레코드 중 직후 m번째 레코드 값 출력
- 출력할 값이 없으면(컬럼B 기준 정렬 시 첫번째 행) n 출력

JSON Parsing Functions
: JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
ex)JSON_EXTRACT_PATH_TEXT
  - JSON String을 입력으로 받아 특정 필드의 값을 추출가능(nested 구조 지원)