[TIL] 데이터 웨어하우스와 SQL 기본에 대해 배우고 이를 바탕으로 데이터 분석에 대해 학습 (5)

학습내용

---

1. 트랜잭션 소개와 실습

2. 기타 고급 문법 소개와 실습
   
   

1. 트랜잭션 소개와 실습

---

• 트랜잭션: Atomic하게 실행되어야 하는 SQL을 묶어서 하나의 작업처럼 처리하는 방법

  • DDL, DML을 사용해 레코드를 추가/수정/삭제할 때만 사용

  • BEGIN-END / BEGIN-COMMIT 사이에 SQL 적어줌

    • END와 COMMIT은 동일한 기능

    • 하나의 SQL이라도 잘못될 경우, ROLLBACK
      
      

• 계좌 이체 예시

  • 입금과 인출 2개의 과정으로 이루어짐

  • 2개의 과정이 동시에 성공하던지 실패해야하므로, 트랜잭션으로 묶어줘서 처리

  • 계좌 조회는 트랜잭션으로 묶을 필요 없음
    
    

• 커밋 모드

  • autocommit = True

    • 모든 레코드 추가/수정/삭제 작업이 바로 DB에 반영

    • 특정 작업을 트랜잭션으로 묶고 싶을 경우, BEGIN-END(COMMIT) / ROLLBACK 으로 처리
      
      

  • autocimmit = False

    • 모든 레코드 추가/수정/삭제 작업이 COMMIT 호출 전까지 반영되지 않음
      
      

• 트랜잭션 방식

  • Google Colab

    • 기본적으로 모든 SQL 명령이 바로 반영됨(autocommit = True)
      
      

  • psycopg2

    • autocommit 파라미터를 사용해 조절 가능

    • autocommit = True: 기본적으로 PostgreSQL의 커밋 모드와 동일

    • autocommit = False: .commit()과 .rollback() 함수로 트랜잭션 조절
      
      

• DELETE FROM vs TRUNCATE

  • DELETE FROM table_name

    • 테이블에서 모든 레코드 삭제

    • WHERE 사용해 특정 레코드만 삭제 가능
      
      

  • TRUNCATE table_name

    • DELETE FROM 보다 빠름

    • 전체 테이블 내용을 삭제할 때 유리

    • WHERE과 트랜잭션을 지원하지 않는 단점 존재

      • 연산을 되돌릴 필요가 없을 경우에만 사용

2. 기타 고급 문법 소개와 실습

---

• 알아두면 유용한 SQL 문법들

  • UNION, EXCEPT, INTERSECT

    • UNION(합집합): 여러 개의 테이블이나 SELECT 결과를 하나로 합쳐줌
      - UNION ALL: 중복 데이터 제거
      
      

    • EXCEPT(= MINUS, 차집합): 하나의 SELECT 결과에서 다른 SELECT 결과를 빼줌

    • INTERSECT(교집합): 여러 개의 SELECT 문의 결과에서 같은 레코드를 찾아줌
      
      

  • COALESCE, NULLIF

    • NULLIF(exp1, exp2): exp1과 exp2가 같을 경우 NULL 반환
      
      

  • LISTAGG

    • GROUP BY에서 사용되는 Aggregate 함수 중 하나

    • ex) 사용자 ID별로 채널을 시간 순서대로 만들어 출력

      SELECT userId, LISTAGG(channel) WITHIN GROUP (ORDER BY ts) AS channels
      FROM raw_data.user_session_channel usc
      JOIN raw_data.session_timestamp st
      ON usc.sessionId = st.sessionId
      GROUP BY userId
      LIMIT 10;

      • WITHIN GROUP (ORDER BY ts): 시간 순서대로 정렬

      • LISTAGG(channel, '->') 처럼 두 번째 인자로 구분자 설정 가능
        
        

  • WINDOW 함수

    • 행 간의 관계를 쉽게 정의하고, 비교하기 위해 사용하는 함수

    • func(exp) OVER ([PARTITION BY exp] [ORDER BY exp])

    • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, ...

    • 수학 함수: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE
      
      

    • LAG

      SELECT usc.*, st.ts,
          LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts) prev_channel
      FROM raw_data.user_session_channel usc
      JOIN raw_data.session_timestamp st
      ON usc.sessionId = st.sessionId
      ORDER BY usc.userId, st.ts

      • LAG(channel, 1): 첫 번째 인자로 주어진 필드의 이전값을 1개 가져옴
        
        

  • JSON Parsing 함수

    • 주어진 JSON의 포맷을 아는 경우에만 사용 가능

    • JSON String을 입력받아 특정 필드의 값 추출 가능

메모

---