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

SQL_Analysis_Day 5-1

 트랜잭션이란 Atomic(All-Or-Nothing)하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법이다. 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있으며 SELECT에는 트랜잭션을 사용할 이유가 없다. BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들은 마치 하나의 명령어처럼 사용되며 다 성공하거나 실패한다. END와 COMMIT은 동일하며 만약 BEGIN 전의 상태로 돌아가고 싶다면 ROLLBACK을 실행하면 된다. 이 동작은 commit mode에 따라 달라진다. autocommit = True라면 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰이고 이를 커밋(Commit)된다고 한다. 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
  • 테이블 자체를 삭제하는 함수인 DROP TABLE table_name과 달리 레코드 전체나 일부를 삭제
  • WHERE을 사용해 특정 레코드만 삭제 가능(DELETE FROM 테이블이름 WHERE 조건) 
• TRUNCATE table_name
  • DELETE FROM에 비해 속도가 빠름
  • 전체 테이블의 내용 삭제시 유리
  • WHERE을 지원하지 않음
  • Transaction을 지원하지 않음

 

SQL_Analysis_Day 5-2

 지금까지 사용한 SQL 함수뿐만 아니라 알아두면 유용한 함수들이 있다.

• UNION(합집합)
  • 여러개의 테이블이나 SELECT 결과를 하나의 결과로 합쳐줌
  • UNION은 중복을 제거해주나 UNION ALL은 제거하지 않음
• EXCEPT(MINUS)
  • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능
• INTERSECT(교집합)
  • 여러 개의 SELECT문에서 같은 레코드들만 찾아줌
• COALESCE(Expression1, Expression2, ...)
  • 첫 번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴
  • NULL값을 다른 값으로 바꾸고 싶을 때 사용
• NULLIF(Expression1, Expression2)
  • Expression1과 Expression2의 값이 같으면 NULL을 리턴
• LISTAGG
  • GROUP BY에서 사용되는 Aggregate 함수 중의 하나
  • 예) 사용자 ID별로 채널을 순서대로 리스트(YoutubeGoogleInstagramYoutubeInstagramInstagram...)
  • 구별자를 넣을 수도 있음 (예) LISTAGG(channel,'->') → (Youtube->Google->Instagram->Youtube->Instagram...)

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 
  • 형식은 function(expression) OVER ( [ PARTITION BY expression] [ ORDER BY expression ] )으로 function에 ROW_NUMBER, FIRST_VALUE, LAST_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.ts
-- 다음 채널 찾으려면 ORDER BY ts DESC

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

 

공부하며 어려웠던 내용

 트랜잭션의 개념은 이해되었지만 아직 사용법이 와닿진 않았다. JSON Parsing Functions에서 JSON을 알고 있지만 해당 함수가 어떤 함수인지 이해되지 않았다.