05/12 25일차 데이터 웨어하우스와 SQL과 데이터분석 (5)

데이터 웨어하우스와 SQL과 데이터분석

---

주요 메모 사항

---

4일차 리뷰

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

• 아래 두 개의 코드는 같은 결과를 나타냄, 다만 필자는 아래의 서브쿼리 방법으로 푸는게 더 익숙함
• OVER : 윈도우 함수를 사용해 연산, 상위 N개의 결과 등의 계산이 가능함 (쿼리 결과 내에서) 
• PARTITION BY : 특정 열을 기준으로 데이터를 나눔
• SELECT ROW_NUMBER() OVER(PARTITION BY 그룹핑 컬럼 ORDER BY 정렬 컬럼)

%%sql

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;


%%sql

SELECT first.userid AS userid, first.channel AS first_channel, last.channel AS last_channel
FROM (
  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
) first
JOIN (
  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
)  last ON first.userid = last.userid and last.seq = 1
WHERE first.seq = 1;


+-----+----------+-----------------+----------------+
|     |   userid | first_channel   | last_channel   |
|-----+----------+-----------------+----------------|
|   0 |       27 | Youtube         | Instagram      |
|   1 |       29 | Naver           | Naver          |
|   2 |       33 | Google          | Youtube        |
|   3 |       40 | Youtube         | Google         |
|   4 |       44 | Naver           | Instagram      |
|   5 |       59 | Instagram       | Instagram      |
|   6 |       68 | Youtube         | Organic        |
|   7 |       87 | Youtube         | Google         |
|   8 |       97 | Organic         | Organic        |
|   9 |      113 | Organic         | Organic        |
|  10 |      114 | Instagram       | Youtube        |

-- 이후 생략

 

 

Gross Revenue가 가장 큰 UserID 10개 찾기

• 위의 코드가 더 편하다고 느낌 (OVER PARTITION BY 사용을 안해봐서 그런지 어려움)

%%sql
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;


%%sql
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 
LIMIT 10;

+----+----------+-------+
|    |   userid |   sum |
|----+----------+-------|
|  0 |      989 |   743 |
|  1 |      772 |   556 |
|  2 |     1615 |   506 |
|  3 |      654 |   488 |
|  4 |     1651 |   463 |
|  5 |      973 |   438 |
|  6 |      262 |   422 |
|  7 |     1099 |   421 |
|  8 |     2682 |   414 |
|  9 |      891 |   412 |
+----+----------+-------+

 

 

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

• NPS : 고객추천지수

%%sql
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);


%%sql
SELECT LEFT(created_at, 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;


+----+---------+---------+
|    | month   |   round |
|----+---------+---------|
|  0 | 2019-01 |    2.36 |
|  1 | 2019-02 |   30.54 |
|  2 | 2019-03 |   52.91 |
|  3 | 2019-04 |   53    |
|  4 | 2019-05 |   54.52 |
|  5 | 2019-06 |   65.02 |
|  6 | 2019-07 |   64.51 |
|  7 | 2019-08 |   67.71 |
|  8 | 2019-09 |   37.95 |
|  9 | 2019-10 |   53.29 |
| 10 | 2019-11 |   61.29 |
| 11 | 2019-12 |   65.99 |
+----+---------+---------+

 

 

---

트랜잭션

• Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업으로 처리하는 방법
  • 이는 DDL이나 DML 중 레코드를 수정 / 추가 / 삭제한 것에만 의미가 있음
  • SELECT에는 트랜잭션을 사용할 이유가 없다
  • BEGIN과 END 혹은 BEGIN과 COMMIT 사이에 해당 SQL들을 사용
  • ROLLBACK

 

• 은행 계좌 이체 -> 아주 좋은 예시
  1. 계좌 이체: 인출과 입금 두 과정
  2. 만약 인출은 성공했는데 입금이 실패한다면?
  3. 이 두 과정은 동시에 성공하던지 실패해야함, -> Atomic하다는 의미
  4. 이러한 과정을 트랜잭션으로 묶어줘야함
  5. 조회만 하는 것은 트랜잭션이 필요 없음

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

• END 는 COMMIT임
• COMMIT 이전 명령어가 ROLLBACK

 

트랜잭션 커밋 모드 : autocommit

• autocommit = True, 기본적으로 모든 레코드 수정 삭제 추가 시에 DB에 바로 COMMIT 됨
• autocommit = False, COMMIT 를 명령할 때 까지 자동 커밋을 안함

 

트랜잭션 방식

• Google Colab 의 트랜잭션
  • 기본적으로 모든 SQL statement가 바로 커밋됨, autocommit = True
  •  이를 바꾸고 싶으면 BEGIN; END; 혹은 BEGIN; COMMIT 를 사용하면 됨

 

• psycopg2의 트랜잭션
  • autocommit를 설정해서 사용

 

 

DELETE FROM vs. TRUNCATE

• DELETE FROM table_name (not DELETE * FROM)
  • 테이블에서 모든 레코드를 삭제
  • VS. DROP TABLE
  • WHERE 사용해 특정 레코드만 삭제 가능

 

• TRUNCATE table_name 도 테이블에서 모든 레코드 삭제
  • DELETE FROM은 속도가 느림
  • TRUNCATE이 전체 테이블의 내용 삭제시에는 유리
  • 다만, WHERE와 Transaction을 지원하지 않

 

 

---

알아두면 유용한 SQL 문법들

• UNION, EXCEPT, INTERSECT
• COALESCE, NULLIF
• LISTAGG
• LAG
• JSON Parsing 함수
• WINDOW 함수
  • ROW_NUMBER OVER
  • SUM OVER
  • FIRST_VALUE, LAST_VALUE

 

---

SQL 문법

 

● UNION (합집합)

    ○ 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌

    ○ UNION vs. UNION ALL (UNION은 중복을 제거)

 

● 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별로 채널을 순서대로 리스트:

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;

 
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

 

 

 

JSON Parsing Functions

● JSON의 포맷을 이미 아는 상황에서만 사용가능한 함수

    ○ JSON String을 입력으로 받아 특정 필드의 값을 추출가능 (nested 구조 지원)

● 예제) JSON_EXTRACT_PATH_TEXT

 

SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":"1"},"f4":{"f5":"99","f6":"star"}}','f4', 'f6');

{
 "f2":{
 	"f3":"1"
	 },
 "f4":{
	"f5":"99",
 	"f6":"star"
	 }
}

 

 

 

---

공부하며 어려웠던 내용