📒 SQL(5)

📌 트랜잭션

• SQL들을 묶어서 하나의 작업처럼 처리하는 방법
• 더 이상 쪼갤 수 없는 업무 처리의 최소 단위이다.
• 거래내역이라고도 한다.
  • 이는 DDL이나 DML중에서 레코드를 수정/추가/삭제한 것에만 의미가 있다.
  • SELECT에는 트랜잭션을 사용할 이유가 없다.
  • BEGIN, END 혹은 BEGIN, COMMIT 사이에 해당 SQL들을 사용한다.
  • ROLLBACK

---

📌 트랜잭션(커밋 모드) - AUTOCOMMIT

• autocommit = True
  • 모든 레코드 수정/추가/삭제 작업이 기본적으로 바로 DB에 쓰여진다.
  • 이를 커밋된다고 한다.
  • 만약 특정 작업을 트랜잭션으로 묶고 싶다면 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 vs. TRUNCATE

• DELETE FROM table_name (not DELETE* FROM)
  • 테이블에서 모든 레코드를 삭제
  • vs. DROP TABLE table_name
  • WHERE을 사용하여 특정 레코드만 삭제가 가능하다.
    • DELETE FROM raw_data.user_session_channel WHERE channel = 'Google'
• TURNCATE table_name
  • 테이블에서 모든 레코드를 삭제한다.
  • DELETE FROM은 속도가 느리다.
  • TRUNCATE는 전체 테이블의 내용 삭제시에는 여러모로 유리하다.
  • 단점
    • WHERE을 지원하지 않는다.
    • Transction을 지원하지 않는다.

---

📌 UNION, EXCEPT, INTERSECT

• UNION(합집합)
  • 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐준다.
  • UNION vs. UNION ALL
    • UNION은 중복을 제거한다.
• EXCEPT(MINUS)
  • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능하다.
• INTERSECT(교집합)
  • 여러 개의 SELECT문에서 같은 레코드들만 찾아준다.

---

📌COALESCE, NULLIF

• COALESCE(Expression1, Expression2,...)
  • 첫 번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다.
  • NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.
• NULLIF(Expression1, Expression2)
  • Expression1, Expression2의 값이 같으면 NULL을 리턴한다.

---

📌LISTAGG

• GROUP BY에서 사용되는 Aggregate함수 중의 하나이다.
• 사용자 ID별로 채널을 순서대로 나열

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

• 리스트를 ,로 나열하기

SELECT
	userid,
    LISTAGG(channel, ',') WITHIN GROUP (ORDER BY ts) channels
FROM raw_data.user_session_channel usc
JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
GROUP BY 1
LIMIT 10;

---

📌 WINDOW

• Syntax
  • function(expression) OVER([PARTITION BY expression][ORDER BY expression])
• Useful functions
  • ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
  • Math functions : AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

📌 WINDOW - 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

📌 JSON Parsing Functions

• JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"},"f4":{"f5":"99","f6":"star"}}','f4','f6');

{
  	"f2":{
      	"f3":"1"
    },
    "f4":{
      	"f5":"99",
        "f6":"star"
    }
}