풀이

풀이

풀이

풀이

미완

풀이

풀이

풀이

풀이

풀이

풀이

미완

풀이

풀이

트랜잭션이란 (1)

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법
  • 이는 DDL이나 DML 중 레코드를 수정/추가/삭제한 것에만 의미가 있음
    • SELECT에는 트랜잭션을 사용할 이유가 없음
    • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
    • ROLLBACK
• 은행계좌이체가아주좋은예
  • 계좌 이체: 인출과 입금의 두 과정으로 이뤄짐
  • 만일 인출은 성공했는데 입금이 실패한다면?
  • 이 두 과정은 동시에 성공하던지 실패해야함 ➡️ Atomic하다는 의미
  • 이런 과정들을 트랜잭션으로 묶어주어야함
  • 조회만 한다면 이는 트랜잭션으로 묶일 이유가 없음

    트랜잭션이란 (2)

    BEGIN;
    A의 계좌로부터 인출; ➡️ 이 명령어들은 마치 하나의 명령어처럼 처리됨
    B의 계좌로 입금; ➡️ 다 성공하던지, 다 실패하던지
    END;

• END와 COMMIT은 동일
• 만일 BEGIN전의 상태로 돌아가고 싶다면 ROLLBACK 실행
• 이 동작은 commit mode에 따라 달라짐

  트랜잭션 커밋 모드: autocommit

• autocommit = True
  • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. 이를 커밋(Commit)된다고 함
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 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'
• TRUNCATE table_name도 테이블에서 모든 레코드 삭제
  • DELETE FROM은 속도가 느림
  • TRUNCATE이 전체 테이블의 내용 삭제시에 유리
  • 단점 존재
    • TRUNCATE는 WHERE절을 지원하지 않음
      • TRUNCATE는 Transaction을 지원하지 않음

        기타 고급 문법

        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 (1)

• 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;

  [결과] 68YoutubeGoogleInstagramYoutubeInstagramInstagramInstagramOrganicInstagramYoutube...

  LISTAGG (2)

  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;

  [결과] 68 Youtube->Google->Instagram->Youtube->Instagram->Instagram->Instagram->...

  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

• https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html
• JSON의 포맷을 이미 아는 상황에서만 사용 가능한 함수
  • JSON String을 입력으로 받아 특정 필드의 값 추출 가능 (nested 구조 지원)
• 예제
  • SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');

    이 글에서 소개된 내용은 프로그래머스 데이터분석1기에서 진행된 한기용 강사님의 온라인 강의를 참조하여 작성되었습니다.

JOIN이란

SQL조인은두개혹은그이상의테이블들을공통필드를가지고머지하는데 사용된다. 이는 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용된다.

왼쪽 테이블을 LEFT라고 하고 오른쪽 테이블을 RIGHT이라고 하자. JOIN의 결과는 방식에 상관없이 양쪽의 필드를 모두 가진 새로운 테이블을 만들어내게 된다.

조인의 방식에 따라 다음 두 가지가 달라진다:

1. 어떤 레코드들이 선택되는지?
2. 어떤 필드들이 채워지는지?

JOIN 문법

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2 WHERE A.ts >= '2019-01-01';
INNER, FULL, LEFT, RIGHT, CROSS

밑줄친 곳에 들어갈 코드 : INNER, FULL, LEFT, RIGHT, CROSS

JOIN시 고려해야할 점

• 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
  • 아주 중요함!!!
• 조인하는 테이블들간의 관계를 명확하게 정의
  • One to one
    • 완전한 one to one: user_session_channel & session_timestamp
    • 한쪽이 부분집합이 되는 one to one: user_session_channel & session_transaction
  • One to many? (order vs order_items)
    • 이 경우 중복이 더 큰 문제됨 -> 증폭!!
  • Many to one?
    • 방향만 바꾸면 One to many로 보는 것과 사실상 동일.
  • Many to many?
    • 이런 경우는 많지 않으며 이는 one to one이나 one to many로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 덜 위험
• 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야함

다양한 종류의 JOIN

JOIN의 종류

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN

  INNER JOIN

1. 양쪽 테이블에서 매치가 되는 레코드들만 리턴함
2. 양쪽 테이블의 필드가 모두 채워진 상태로 리턴됨

   LEFT JOIN

3. 왼쪽 테이블(Base)의 모든 레코드들을 리턴함
4. 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴됨

   FULL JOIN

5. 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴함
6. 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴됨

   CROSS JOIN

7. 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함

   SELF JOIN

8. 동일한 테이블을 alias를 달리해서 자기 자신과 조인함

이 글에서 소개된 내용은 프로그래머스 데이터분석1기에서 진행된 한기용 강사님의 온라인 강의를 참조하여 작성되었습니다.

GROUP BY & Aggregate 함수

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산
• 이는 두 단계로 이뤄짐
  • 먼저 그룹핑을 할 필드 결정 (하나 이상의 필드가 될 수 있음)
    • GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
  • 다음 그룹별로 계산할 내용 결정
    • 여기서 Aggregate함수 사용
      • COUNT, SUM, AVG, MIN, MAX, LISTAGG, ...
        • 보통 필드 이름을 지정하는 것이 일반적 (alias)

CTAS와 CTE

CTAS : SELECT를 가지고 테이블 생성

• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블들이 있다면 이를 CTAS를 사용해서 조인해두면 편리해짐

항상 시도해봐야하는 데이터 품질 확인 방법들

• 중복된 레코드들 체크하기
• 최근 데이터의 존재 여부 체크하기 (freshness)
• Primary key uniqueness가 지켜지는지 체크하기
• 값이 비어있는 컬럼들이 있는지 체크하기

이 글에서 소개된 내용은 프로그래머스 데이터분석1기에서 진행된 한기용 강사님의 온라인 강의를 참조하여 작성되었습니다.

풀이

풀이

풀이

풀이

풀이