SQL을 이용한 데이터 분석 Day 5 트랜잭션과 기타 고급 SQL 문법

강의

트랜잭션

"트랜잭션"(Transaction)은 Atomic하게 실행되어야 하는 SQL을 묶어서 하나의 작업처럼 처리하는 방법 입니다.

BEGIN과 END 혹은 BEGIN과 COMMIT 사이에서 사용합니다.

ROLLBACK을 사용하여 BEGIN 전의 상태로 돌이킬 수 있습니다.

대표적인 예시로 은행 계좌 이체가 있습니다.

BEGIN;
    A 계좌로부터 인출;
    B 계좌로 입금;
END 혹은 COMMIT;

 

Commit Mode : autocommit

• True : 모든 레코드 수정/삭제/추가 작업이 기본적으로 데이터베이스에 바로 쓰여짐. → BEGIN;COMMIT; 과 ROLLBACK; 사용 필요 
• False : 모든 레코드 수정/삭제/추가 작업이 COMMIT 호출 전에 commit 불가 → .commit()과 .rollback()함수 사용 결정 필요

 

트랜잭션 방식

• Google Colab
  • SQL statement autocommit = True
  • BEGIN;END; 혹은 BEGIN;COMMIT; 과 ROLLBACK; 사용 필요 
• psycopg2
  • autocommit 파라미터 조절 가능
  • autocommit = False 인 경우, .commit()과 .rollback()함수로 조절 가능

 

---

DELETE FROM vs TRUNCATE

• DELETE FROM table (not DELETE * FROM)
  • 테이블의 모든 레코드 삭제
  • WHERE 문으로 특정 레코드만 삭제 가능
• TRUNCATE table
  • 테이블의 모든 레코드 삭제
  • 속도 빠름 → 전체 삭제시에 유리
  • WHERE 문 사용 불가
  • 트랜잭션 사용 불가 → ROLLBACK 불가

 

---

기타 SQL 문법

UNION, EXCEPT, INTERSECT

• UNION (합집합)
  • 다수의 테이블이나 SELECT 결과를 하나로 합쳐줌
  • 중복 제거
• UNION ALL
  • 다수의 테이블이나 SELECT 결과를 하나로 합쳐줌
  • 중복 존재
• EXCEPT (MINUS)
  • 하나의 SELECT 결과에서 다른 SELECT 결과를 제외 가능
• INTERSECT (교집합)
  • 여러 개의  SELECT 문에서 같은 레코드 찾기

 

COALESCE, NULLIF

• COALESCE(exp1, exp2, ...)
  • exp1부터 차례로 확인하여 NULL이 아닌 값을 리턴
  • 모두 NULL인 경우, NULL 리턴
• NULLIF(exp1, exp2)
  • exp1과 exp2의 값이 같을 경우 NULL 리턴

 

LISTAGG

• GROUP BY에서 사용되는 Aggregate 함수
• Listing
• 순서 지정 불가 → WITHIN 이용하여 순서 지정 가능
• 구분자 사용 가능 → LISTAGG(field, "구분자")

 

WINDOW 함수 : 행 간의 관계 정의

• LAG
  • 현재 행의 이전 값 리턴

-- 이전 찾기
SELECT LAG(field, 1) OVER (PARTITION BY primary key ORDER BY ts) AS prev_field
FROM table
;

-- 다음 찾기
SELECT LAG(field, 1) OVER (PARTITION BY primary key ORDER BY ts DESC) AS next_field
FROM table
;

• ROW_NUMBER OVER
  • 특정 순서로 행 번호 지정
• SUM OVER
  • 순서에 따라 누적 합계 계산
• FIRST_VALUE, LAST_VALUE
  • 처음 혹은 마지막 값 리턴
• Math functions : AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

 

JSON Pasing 함수

• JSON의 포맷을 이미 아는 경우에만 사용 가능
• JSON String을 입력받아 특정 필드이 값 추출 가능 (nested 구조 지원)