프로그래머스 데이터분석 데브코스 1기 - 25일차

주제

1. 지난 숙제 풀이
2. 트랜잭션
3. DELETE vs. TRUNCATE
4. 고급 SQL 함수들

 

지난 숙제 풀이

1. 사용자별 처음과 마지막 채널 찾기

• 일반적으로 FROM 테이블의 조건을 WHERE에 붙이는 게 좋다
• 이 경우 두 테이블이 INNER JOIN을 해도 똑같기 때문에 WHERE에 두 조건을 모두 넣어도 된다

-- 첫번째 풀이
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;

-- 두번째 방식은 서브쿼리(인라인 뷰) 사용

-- 세번째 방식
SELECT userId,
	MAX(CASE WHEN rn1 = 1 THEN channel END) first_touch,
	MIN(CASE WHEN rn2 = 1 THEN channel END) last_touch
FROM (
	SELECT userId,
		channel,
		(ROW_NUMBER() OVER(PARTITION BY usc.userId ORDER BY st.ts ASC)) AS rn1,
		(ROW_NUMBER() OVER(PARTITION BY usc.userId ORDER BY st.ts DESC)) AS rn1
	FROM raw_data.user_serssion_channel usc
	JOIN raw_data.session_timestamp st ON usc.sessionId = st.sessionId
)
GROUP BY 1;


-- 네번째 방식 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.channerl) 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;

 

 

2. Gross Revenue가 가장 큰 UserID 10개 찾기

-- 첫번째 방식(userid 하나별 하나의 결과만 출력)
SELECT 
	userId,
	SUM(amount)
FROM raw_data.session_transaction st
LEFT JOIN raw_data.user_session_channel usc ON st.sessionId = usc.sessionId
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

-- 두번째 방식
SELECT DISTINCT (SUM OVER는 userid 별 모든 합이 다 나와서 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
LIMIT 10;

 

 

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

-- 첫번재 방식
SELECT month,
	ROUND((promoters - detractors)::float / total_count * 100, 2) AS overall_nps
FROM (
	SELECT LEFT(created, 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
);

-- 두번째 방식
SELECT LEFT(created, 7) AS month,
	ROUND(SUM(CASE
		WHEN score >= 9 THEN 1
		WHEN score <= 6 THEN -1 END)::float*100 / COUNT(1), 2)
FROM raw_data.nps
GROUP BY 1
ORDER BY 1;

 

 

 

트랜잭션이란?

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 법
  • 이는 DDL / DML 중 레코드를 수정 / 추가 / 삭제한 것에만 의미가 있음
  • SELECT는 트랜잭션을 사용할 이유가 없음
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL을 사용
  • ROLLBACK
• Atomic(원자성) : all or nothing, 모두 성공하거나 실패해야하는 성질

BEGIN;
	A의 계좌로부터 인출;
	B의 계좌로 입금;
END;

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

 

트랜잭션 커밋 모드 : autocommit

• autocommit = True
  • 모든 레코드 수정 / 삭제 / 추가 작업이 기본적으로 바로 데이터베이스에 쓰여짐. → 커밋
  • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 begin과 end / rollback 처리
• autocommit = False
  • 모든 레코드 수정 / 삭제 / 추가 작업이 커밋 호출될 때까지 커밋되지 않음

 

트랜잭션 방식

• Google Colab
  • 기본적으로 모든 SQL statement가 바로 커밋됨(autocommit = True)
  • 이를 바꾸고 싶다면 begin; end; 혹은 begin; commit;을 사용
• psycopg2
  • autocommit이라는 파라미터로 조절 가능
  • True가 되면 PostgreSQL의 커밋모드와 동일
  • False가 되면 커넥션 객체의 .commit()과 .rollback() 함수로 트랜잭션 조절 가능

 

DELETE FROM vs. TRUNCATE

• DELETE FROM table_name (not DELETE * FROM)
  • 테이블에서 모든 레코드 삭제
  • WHERE 절을 사용해 특정 레코드만 삭제 가능
• TRUNCATE table_name
  • 테이블에서 모든 레코드를 삭제
  • DELETE FROM보다 속도가 빠름
  • 전체 테이블의 내용 삭제 시에는 유리
  • 단점 :
    • WHERE를 지원하지 않음
    • 트랜잭션을 지원하지 않음(롤백 불가)

 

 

고급 SQL 함수들

• UNION
  • 합집합, 여러 개의 테이블을 하나의 결과로 합쳐줌
• EXCEPT(MINUS)
  • 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것
• INTERSECT
  • 교집합, 여러 개의 SELECT문에서 같은 레코드만 찾아줌
• 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;

 

• WINDOW

-- 문법
function(expression) OVER ([PARTITION BY expression] [ORDER BY expression])

• LAG : 이전 결과값을 알고 싶을 때 사용
• JSON Parsing
  • JSON의 포맷을 이미 아는 상황에서만 사용 가능
  • JSON string을 입력으로 받아 특정 필드의 값을 추출 가능
  • 예. JSON_EXTRACT_PATH_TEXT 함수

 

 

---

느낀점

 

이번주에 숙제를 통해서 평소에 써보지 못했던 고급 함수들도 써보고, 쿼리를 작성할 때 깊게 생각해볼 수 있어서 좋았다. 확실히 직접 사용해봐야 이해가 빠른 것 같다. 다음주는 방학인데 그동안 분석 공부도 하고 판다스도 좀 공부해놔야겠다.