📖 오늘의 학습
- SQL : 트랜잭션, UNION/EXCEPT/INTERSECT, LISTAGG, WINDOW-LAG
트랜잭션 (Transaction)
Atomic 하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법이다.
Atomic 이란? 묶인 SQL 작업들이 모두 성공하던지 실패하던지 둘 중 하나만 해야한다!
- DDL이나 DML 같이 레코드를 수정/추가/삭제한 것에만 의미가 있다
SELECT는 불필요하다BEGIN;과END;혹은BEGIN;과COMMIT;사이에 해당 SQL들을 사용한다.ROLLBACK;으로 커밋 되기 전에 SQL 실행을 되돌릴 수 있다.
예) 은행 계좌 이체
- 만약 인출은 성공했는데 입금이 실패한다면?
- 이 두 과정은 동시에 성공하던지 실패해야 한다.
-> Atomic = 마치 한 동작처럼 수행되어야 한다. - 아래와 같이 이 과정들을 트랜잭션으로 묶어줘야 데이터의 정합성이 유지된다.
BEGIN;
A의 계좌로부터 인출;
B의 계좌로 입금;
END; --COMMIT; 과 같음트랜잭션 COMMIT 모드
만일 BEGIN; 이전의 상태로 돌아가고 싶다면 ROLLBACK; 실행하면 된다. 하지만 이 동작은 COMMIT mode에 따라 달라진다.
만약 autocommit 이 true 라면 모든 수정/삭제/추가 작업이 COMMIT; 명령 없이 바로 데이터베이스에 쓰여지고,
false 라면 COMMIT; 이 나올 때까지 데이터베이스에 쓰여지지 않는다.
트랜잭션 방식
google colab과 psycopg2의 트랜잭션 방식이다.
- google colab의 트랜잭션
- 기본적으로 모든 SQL 이 바로 커밋됨
- 이를 바꾸고 싶다면
BEGIN;END;/COMMIT;또는ROLLBACK;사용
- psycopg2의 트랜잭션
- autocommit = true 가 디폴트값
SQL 심화
DELETE FROM vs. TRUNCATE
DELETE
- 테이블에서 모든 레코드 삭제
- WHERE를 사용해 특정 레코드들만 삭제 가능
- 속도가 느림
TRUNCATE
- 속도가 빠름
- 전체 테이블 레코드 삭제만 가능 (WHERE 불가)
- Transaction을 지원하지 않음
UNION, EXCEPT, INTERSECT
- UNION (합집합)
- UNION ALL은 중복 없이 모두 가져온다
- 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐준다
- EXCEPT (MINUS)
- 하나의 SELECT 결과에서 다른 SELECT 결과를 빼준다
- INTERSECT (교집합)
- 여러 개의 SELECT문에서 같은 레코드들만 찾아줌
COALESCE, NULLIF
- COALESCE
- 첫번째 인자부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다
- NULL을 다른 값으로 바꾸고 싶을 때 사용
- NULLIF
- 첫번째 인자와 두번째 인자의 값이 같으면 NULL을 리턴한다
LISTAGG
- GROUP BY 에서 사용되는 Aggregate함수 중 하나이다
- 사용자 ID별로 채널을 순서대로 리스트
- 그룹핑이 된 레코드 안에서 하나의 필드를 쭉 붙여준다.
- LISTAGG(channel, ‘구분자 입력’) WITHIN GROUP (ORDER BY ts) channels
WINDOW
syntax : function(expression) OVER ([PARTITION BY exp][ORDER BY exp])
자주 사용되는 함수
ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG
AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE
이 중에서 많이 쓰이는 LAG에 대해 알아보자
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.tsWINDOW 문법으로 위와 같이 작성해주면 이전 채널을 알 수 있다. 다음 채널을 알고 싶다면 간단하게 LAG(channel,1) OVER (PARTITION BY userId ORDER BY ts) 에서 ORDER BY 항목을 내림차순으로 변경해주면 된다.
JSON Parsing Functions
- JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수
- 뭐를 읽어야 하는지 알아야 함, 동적이지 않음
JSON_EXTRACT_PATH_TEXT
이렇게 JSON 파일이 주어졌다고 했을 때
{
"f2":{
"f3":"1"
},
"f4":{
"f5":"99",
"f6":"star"
}
}이렇게 함수를 사용한다. (f4-f6 값 가져오기)
SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');📝 주요메모사항
Colab 링크 : https://colab.research.google.com/drive/1B2qae6TKQj5_fXG6JsBo5cub0nWJ-3ok
😵 공부하면서 어려웠던 내용
