SQL 학습 - Transaction(for Python) & 추가 SQL 문법 (TIL 230512)

📚 KDT WEEK 6 DAY 5 TIL

• Transaction
• Python에서 트랜잭션 사용하기
• 유용한 SQL 문법

---

 

🟥 Transaction

Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

 

• DDL 또는 DML 중 레코드를 수정, 추가, 삭제한 것에만 의미가 있음
• SELECT에는 트랜잭션 불가
• BEGIN ~ END or BEGIN ~ COMMIT 사이에 쿼리문 작성
• Atomic : 트랙잭션의 과정이 모두 성공되거나 모두 실패되어야함
• ROLLBACK : 이전 상태로 돌아감

ex) 은행의 계좌 인출과 입금이 묶여서 실행되어야 하므로 트랙잭션이 필요함

 

BEGIN;
    A의 계좌로부터 인출;
    B의 계좌로 입금;
END; -- COMMIT과 동일

• 계좌 인출과 입금이 마치 하나의 명령어처럼 처리된다.
• BEGIN 이전의 상태로 돌아가려면 ROLLBACK 실행

commit mode

• autocommit
  
  • True : 모든 레코드의 수정/삭제/추가 작업이 바로 DB에 쓰여짐
  • False : 모든 레코드의 수정/삭제/추가 작업이 COMMIT이 호출될 때까지 저장 X
    • False의 경우 .commit(), .rollback()으로 DB 저장 조율

 

---

 

DELETE FROM vs TRUNCATE

 

DELETE FROM table_name (DELETE * FROM 과는 다름)

• 테이블에서 모든 레코드를 삭제 가능
• WHERE 사용가능 ➡️ 특정 레코드만 삭제 가능 (DROP TABLE과의 차이점!)

 

TRUNCATE table_name

• 테이블에서 모든 레코드를 삭제
• DELETE FROM 보다 속도가 빠르다
• WHERE, Transaction 사용불가 ➡️ 롤백불가

 

---

🟦 실습

 

Redshift Connet in Python

.connect()에 생성했던 Redshift 정보를 채워넣으면 된다. (autocommit = False)

import psycopg2

conn = psycopg2.connect("dbname={dbname} user={user} host={host} password={password} port={port}".format(
    dbname=dbname,
    user=redshift_user,
    password=redshift_pass,
    host=host,
    port=port
))
conn.set_session(autocommit=autocommit)
cur = conn.cursor()
cur.execute("쿼리문")
res = cur.fetchall()
for r in res:
  print(r)

cur.execute() 안의 쿼리문을 통해 데이터를 가져와서 출력하는 코드이다.

 

1. autocommit = False이므로 DB 데이터 조작이 현재 세션에서는 이뤄지나, 실제 저장되지는 않는다.

2. conn.commit() 으로 실제 DB에 저장할 수 있다.

3. conn.rollback() 으로 실행되기 이전 상태로 돌아갈 수 있다.

 

다음은 transaction의 예시이다.

try:
  cur.execute("DELETE FROM test_name_gender;") 
  cur.execute("INSERT INTO test_name_gender VALUES ('Claire', 'Female');")
  conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
  print(error)
  conn.rollback()
finally :
  conn.close()

test_name_gender 테이블의 데이터를 삭제, 추가하는 등 데이터를 조작하였다.

하지만 try 과정에서 오류가 발생한다면 conn.commit()이 실행되지 않고 conn.rollback()이 실행된다.

 

 

autocommit=True인 경우, 데이터 조작어는 자동으로 commit되므로 트랜잭션을 다루려면 다음의 방식을 따른다.

cur.execute("BEGIN;")
cur.execute("DELETE FROM test_name_gender;")
cur.execute("INSERT INTO test_name_gender VALUES ('Benjamin', 'Male');")
cur.execute("END;")

• cur.execute()를 사용하여 한 줄씩 명령어를 실행한다.
• BEGIN과 END(COMMIT)으로 트랜잭션의 range를 설정한다.

---

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

테이블 연산

• UNION : 여러개의 테이블이나 SELECT 결과를 하나의 결과로 합쳐준다.
  • UNION ALL의 경우 중복을 제거하지 않고 모두 결과로 합친다.

• EXCEPT : 하나의 SELECT 결과에서 다른 SELECT 결과를 빼준다.
• INTERSECT : 여러개의 SELECT문에서 같은 레코드만 찾아준다.

NULL 처리

• COALESCE(A, B, C, ...) : A부터 차례대로 NULL값이 나오면 그 값을 리턴하고 모두 NULL이면 NULL 리턴
  • NULL값을 다른 값으로 바꾸고 싶을 때 사용
• NULLIF(A, B) : A와 B가 같으면 NULL을 리턴한다.

 

LISTAGG

결과를 보면 알 수 있듯이 쭉 결과를 한 문자열로 합쳐준다.

• LISTAGG(channel) 을 LISTAGG(channel, '->') 로 바꿔주면, -> 가 문자열의 구분자로 추가된다.
• GROUP BY의 Agg함수이므로 GROUP BY가 필요하다.
• LISTAGG 뒤쪽에 선언된 WITHIN은 정렬을 담당한다. WITHIN이 없으면 무작위로 문자열이 생성된다.

 

WINDOW

• ex) FIRST_VALUE, LAST_VALUE, ROW_NUMBER...  > 밑에 숙제참고
• 함수이름() OVER ( PARTITION BY ... ORDER BY ...) 형태를 지닌 것이 특징

 

1. LAG : 이전 또는 다음 특정 필드 값을 가져올 수 있는 함수

LAG(channel, 1) OVER (PARTITION BY userId ORDER BY ts) prev_channel

ORDER BY userid, ts가 뒤쪽에 선언되어있다고 가정한다.

LAG(channel,1) : 내 앞 파티션의 채널을 가져온다.

PARTITION BY userId ORDER BY ts : userId가 파티션의 기준이 되고, ts가 정렬 기준이 되어준다.

 

 

JSON Parsing Functions

JSON 포맷을 활용하여 Parsing 가능한 함수

 

SELECT JSON_EXTRACT_PATH_TEXT('JSON 문자열', 'f4','f6');

--> f4 태그의 f6 요소를 찾아 값을 반환해준다.

JSON의 구조를 미리 알고있어야 한다는 것이 흠

 

---

 

숙제

1. 사용자별로 처음 채널과 마지막 채널 알아내기

 

내풀이

SELECT usc.userid, ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY ts.ts) as nn
FROM raw_data.session_transaction as trans
INNER JOIN raw_data.user_session_channel as usc ON trans.sessionid = usc.sessionid
INNER JOIN raw_data.session_timestamp as ts ON trans.sessionid = ts.sessionid

처음 채널과 마지막 채널뿐만 아니라 전체 채널이 모두 출력된다. 

 

FIRST_VALUE, LAST_VALUE 사용하기

SELECT DISTINCT
    A.userid,
    FIRST_VALUE(A.channel) over(partition by A.userid order by B.ts rows between unbounded preceding and unbounded following) AS First_Channel,
    LAST_VALUE(A.channel) over(partition by A.userid order by B.ts rows between unbounded preceding and unbounded following) AS Last_Channel
FROM raw_data.user_session_channel A
LEFT JOIN raw_data.session_timestamp B ON A.sessionid = B.sessionid;

rows between ~ following 구문은 윈도우의 범위를 전체로 하는 구절이다.

 

CTE 빌딩블록 사용하기

WITH first AS (
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
), last AS(
    SELECT userid, ts, channel, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY ts DESC) seq
    FROM raw_data.user_session_channel usc
    JOIN raw_data.session_timestamp st ON usc.sessionid = st.sessionid
)

SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM first
JOIN last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1

 

---

 

 

2.. Gross Revenue(Refund 포함매출)이 가장 큰 userId 10개 찾기

 

내 풀이(정답)

SELECT usc.userid, SUM(trans.amount) as GrossRevenue
FROM raw_data.session_transaction as trans
INNER JOIN raw_data.user_session_channel as usc ON trans.sessionid = usc.sessionid
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

 

SUM OVER 활용해보기

SELECT
    DISTINCT usc.userid,
    SUM(amount) OVER(PARTITION BY usc.userid)
FROM raw_data.user_session_channel AS usc
JOIN raw_data.session_transaction AS revenue ON revenue.sessionid = usc.sessionid
ORDER BY 2 DESC

위처럼 GROUP BY를 제거하고 SUM에서 PARTITION BY를 활용할 수 있다. 

하지만 이러면 SUM이 userid의 중복횟수만큼 몇 번씩 출력되기 때문에 DISTINCT를 활용했다.

사실 이보다 위에 있는 답이 훨씬 효율적이고 깔끔하다.

 

---

 

 

3. raw_data.nps 테이블을 바탕으로 월별 NPS 계산하기

 

내 풀이

%%sql

SELECT
    LEFT(created_at,7) as month,
    ROUND(SUM(CASE WHEN score >= 9 THEN 1 END) / COUNT(1)::float , 3) - ROUND(SUM(CASE WHEN score <= 6 THEN 1 END) / COUNT(1)::float , 3) as NPS
FROM raw_data.nps
GROUP BY 1

 

테이블 하나 만들어서 명시하기

SELECT
    month,
    ROUND((promoters-detractors)::float/total_count*100, 2) AS overall_nps
FROM (
    SELECT 
        LEFT(created_at, 7) AS month,
        COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
        COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
        COUNT(CASE WHEN score > 6 AND score < 9 THEN 1 END) AS passives,
        COUNT(1) AS total_count
    FROM raw_data.nps
    GROUP BY 1
    ORDER BY 1
)

0으로 나눌 때를 제외하기 위한 처리가 되어있지는 않다.

---

---