7/ 11 - SQL 실습(타이타닉 데이터 활용)

---

-- Churn Rate(%) 구하기(이탈율)
-- 웹사이트: 사이트에 오늘 방문 -----> 90일 정도 후 방문 후 -- 
-- 우리 웹사이트는 1주일 기준, 3일 기준 등 이탈율에 기준일을 잡는 것은 사이트마다 상이함.
-- 분석하는 사람, 누가 이탈했는지 해서, 보고서를 상급자에 보고
-- 상급자 회의 소집, 마케팅 플랜, 개발자한테 지시, 각 사용자에게 push 메시지

use classicmodels;

-- 현재 테이블의 가장 최근 날짜
select max(orderdate) MX_order
from orders
;

-- 현재 테이블의 가장 오래된 날짜
select min(orderdate) MX_order
from orders
;

-- 각 고객의 마지막 구매일
select
	customernumber
    , MAX(orderdate) 마지막구매일
from orders
group by 1
;

-- 현재 시점은 2006-06-01
-- 각 고객의 
select '2006-06-01';

-- 구하고 싶은 건, 2006-06-01일 기준으로 가장 마지막 구매한 날짜를 빼서 기간 구하기
-- DATEDIFF()
select 
	* 
	, '2006-06-01' AS 오늘날짜
    ,  DATEDIFF('2005-06-01', 마지막구매일) DIFF
from ( select
	customernumber
    , MAX(orderdate) 마지막구매일						-- select * 에 서브쿼리를 넣어놓고 시작(기준점)
from orders
group by 1
) A
;

-- 오늘날짜 
select 
	* 
	, '2005-06-01' AS 오늘날짜
    ,  DATEDIFF('2024-07-11', 마지막구매일) DIFF
from ( select
	customernumber
    , MAX(orderdate) 마지막구매일						-- select * 에 서브쿼리를 넣어놓고 시작(기준점)
from orders
group by 1
) A
;

-- 금일날짜로 자동 갱신되도록 해주는 자동화 코드
SELECT 
    *,
    CURRENT_DATE AS 오늘날짜,
    DATEDIFF(CURRENT_DATE, 마지막구매일) AS DIFF
FROM (
    SELECT
        customernumber,
        MAX(orderdate) AS 마지막구매일
    FROM orders
    GROUP BY customernumber
) A;

-- DIFF를 90일 기준으로 Churn, Non-Churn
--                   이탈발생, 이탈미발생

SELECT 
	이탈유무
    , COUNT(DISTINCT customernumber) N_CUS
FROM (
	SELECT 
		*
		, CASE WHEN DIFF >= 90 THEN '이탈발생' 
		  ELSE '이탈미발생' 
		  END 이탈유무
	FROM (
		SELECT 
			*
			, '2005-06-01' AS 오늘날짜
			, DATEDIFF('2005-06-01', 마지막구매일) DIFF
		FROM (
			SELECT 
				customernumber 
				, MAX(orderdate) 마지막구매일
			FROM orders
			GROUP BY 1
		) A
	) A
) A
GROUP BY 1
;
-- 서브쿼리의 개념을 이해하기

CREATE TABLE CLASSICMODELS.CHURN_LIST AS
SELECT CASE WHEN DIFF >= 90 THEN 'CHURN' ELSE 'NON-CHURN' END CHURN_TYPE,
CUSTOMERNUMBER
FROM
(SELECT CUSTOMERNUMBER,
MX_ORDER,
'2005-06-01' END_POINT,
DATEDIFF('2005-06-01',MX_ORDER) DIFF
FROM
(SELECT CUSTOMERNUMBER,
MAX(ORDERDATE) MX_ORDER
FROM CLASSICMODELS.ORDERS
GROUP
BY 1) BASE) BASE
;

-- Churn 고객이 가장 많이 구매한 Productline을 구해보자.
-- 


select * from CHURN_LIST;

SELECT 
	D.churn_type
	, C.productline,
	COUNT(DISTINCT B.customernumber) BU
FROM orderdetails A
LEFT
JOIN orders B
ON A.ordernumber = B.ordernumber
LEFT
JOIN products C
ON A.productcode = C.productcode
LEFT JOIN CHURN_LIST D
ON B.customernumber = D.customernumber
GROUP
BY 1, 2
HAVING churn_type = 'CHURN'
;

SELECT 
	C.productline,
	COUNT(DISTINCT B.customernumber) BU
FROM orderdetails A
LEFT
JOIN orders B
ON A.ordernumber = B.ordernumber
LEFT
JOIN products C
ON A.productcode = C.productcode
LEFT JOIN CHURN_LIST D
ON B.customernumber = D.customernumber
WHERE D.churn_type = 'CHURN'
GROUP BY 1
;

-- 4장을 이해하면, 6장, 7장도 쉽게 이해할 수 있음.

-- chapter2
-- mydata
use mydata;
select * from dataset2;

-- 쇼핑몰, 비류 데이터
-- 댓글 분석, 감정 분석

select 
	`Department Name`
    , AVG(RATING) AVG_RATE
from dataset2
GROUP BY 1
;

-- Trend의 평균평점이 3.85

-- 세부적으로 내용을 확인해보자!!
-- Department Name이 Trend인 것만 조회
-- Rating이 3점이하인것만 조회
select count(*)
from dataset2
where `Department Name` = 'Trend'
    and rating <= 3
;

-- 연령대를 10세 단위로 그룹핑
-- 10 - 19세 => 10대
-- case when age between 10 and 19 then '10대'
SELECT CASE WHEN AGE BETWEEN 0 AND 9 THEN '0009'
WHEN AGE BETWEEN 10 AND 19 THEN '1019'
WHEN AGE BETWEEN 20 AND 29 THEN '2029'
WHEN AGE BETWEEN 30 AND 39 THEN '3039'
WHEN AGE BETWEEN 40 AND 49 THEN '4049'
WHEN AGE BETWEEN 50 AND 59 THEN '5059'
WHEN AGE BETWEEN 60 AND 69 THEN '6069'
WHEN AGE BETWEEN 70 AND 79 THEN '7079'
WHEN AGE BETWEEN 80 AND 89 THEN '8089'
WHEN AGE BETWEEN 90 AND 99 THEN '9099' END AGEBAND,
AGE
FROM MYDATA.DATASET2
WHERE `DEPARTMENT NAME` = 'Trend'
AND RATING <= 3
;

-- 위의 코드를 쉽게 하는 방법
-- 나눗셈을 이용하자!! 절사
select 
	floor(AGE/10) * 10 AS 연령대 -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
	, AGE
from dataset2;

-- 연령대별 분포 : 평점 3점 이하 리뷰
select 
	floor(AGE/10) * 10 AS 연령대 -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
     , count(*) as cnt
from dataset2
where rating <=3 and `DEPARTMENT NAME` = 'Trend'
group by 1
order by 2 desc; -- 50대가 10명 / 40대 9명

select 
	floor(AGE/10) * 10 AS 연령대 -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
     , count(*) as cnt
from dataset2
where  `DEPARTMENT NAME` = 'Trend'
group by 1
order by 2 desc; -- 30대가 29명 / 40대 24명 / 50대는 23명

-- select 와 from이 큰 구절에서 한 개 하나하나씩 실행시키면서 해보기

-- 50대 3점 이하 Trend 리뷰만 추출
select 
	*
from dataset2
where AGE between 50 and 59
	And rating <= 3 
	AND `DEPARTMENT NAME` = 'Trend'
;	

-- Review text만 필요한 경우
select 
	`Review Text`
from dataset2
where AGE between 50 and 59
	And rating <= 3 
	AND `DEPARTMENT NAME` = 'Trend'
;	

-- 평점이 낮은 상품의 주요 Complain
select 
	*
    , ROW_NUMBER() OVER(partition by`Department Name` ORDER BY AVG_RATE) RNK
from (
select 
	`Department Name`
	, clothingID
    , avg(rating) avg_rate
FROM dataset2
group by 1, 2
) A
;

-- 위 결과를 토대로, 
-- 1-10위 데이터 조회
CREATE TABLE mydata.stat AS
select 
	*
from (
select *
    , ROW_NUMBER() OVER(partition by`Department Name` ORDER BY AVG_RATE) RNK
from (
select 
	`Department Name`
	, clothingID
    , avg(rating) avg_rate
FROM dataset2
group by 1, 2
 ) A
) A
WHERE RNK <= 10 -- RNK between 1 and 10으로 해도 동일하다.
;

SELECT 
	ClothingID FROM stat
    where `department name` = 'Bottoms'; 
    
-- 불만족 1위-10위인 제품의 불만족 리뷰를 가져오는 것이 포인트
select * 
from dataset2;

-- 문제 Department에서 bottoms 불만족 1위 - 10위인 리뷰 텍스트를 가져오세요.
-- 해당 ClothingID에서 해당하는 리뷰 텍스트
-- 메인쿼리: dataset2에서 review text만 가져오기
-- 서브쿼리: stat테이블에서 department가 bottoms인 clothingID
-- 메인쿼리와 서브쿼리 나누는 연습하기
SELECT `review text`
FROM dataset2
WHERE clothingid IN (
	SELECT clothingid 
	FROM stat
	WHERE `department name` = 'bottoms'
)
;

-- 0과 1로 레이블을 매긴것
select 
	`REVIEW TEXT`
    , CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END SIZE_YN
FROM dataset2
;

-- SIZE가 나온 댓글은 총 몇 개 일까?
SELECT 
    SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) N_SIZE
    , COUNT(*) N_TOTAL
    , SUM(CASE WHEN `REVIEW TEXT` LIKE '%SIZE%' THEN 1 ELSE 0 END) / COUNT(*) AS ratio
FROM dataset2
;

-- 사이즈와 관련된 다른 키워드도 한 번 확인하자,
SELECT SUM(CASE WHEN 'REVIEW TEXT' LIKE '%SIZE%' THEN 1 ELSE 0 END )
N_SIZE,
SUM(CASE WHEN 'REVIEW TEXT' LIKE '%LARGE%' THEN 1 ELSE 0 END ) N_LARGE,
SUM(CASE WHEN 'REVIEW TEXT' LIKE '%LOOSE%' THEN 1 ELSE 0 END ) N_LOOSE,
SUM(CASE WHEN 'REVIEW TEXT' LIKE '%SMALL%' THEN 1 ELSE 0 END ) N_SMALL,
SUM(CASE WHEN 'REVIEW TEXT' LIKE '%TIGHT%' THEN 1 ELSE 0 END ) N_TIGHT,
SUM(1) N_TOTAL  -- 쭈욱 1로 나열했다는 소리
FROM MYDATA.DATASET2
;


-- select clothingid, 1 from dataset2;
use titanic;
select * from titanic;

-- 중복값 유무 확인
select
	count(passengerid) N_PASS
    , count(distinct passengerid) N_UNIQUE_PASS
FROM titanic
;

-- 요인별 생존 여부 집계
select survived from titanic; -- 0은 사망 / 1은 생존

-- 성별에 따른 승객수와 생존자 수 구하기
select 
	sex as 성별
    , count(*) as 승객수
    , SUM(CASE WHEN survived = 1 THEN 1 ELSE 0 END) AS 생존자수
    , SUM(CASE WHEN survived = 1 THEN 1 ELSE 0 END) / count(*) as ratio
from
	titanic
group by 1
;

-- 연령별, 성별 승객수, 생존자수, 비율 구하기
select 
	floor(AGE/10) * 10 AS 연령대 -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
    , sex as 성별
    , count(*) as 승객수
    , sum(survived) as 생존자수
    , round(sum(survived) / count(Age), 3) as 비율
from 
	titanic
group by 1, 2
having sex = 'male'
order by 1, 2
;


-- 인라인뷰에 조인문 만들기
SELECT 
	A.AGEBAND
    , A.ratio AS M_RATIO
    , B.ratio AS F_RATIO
    , ROUND(B.ratio - A.ratio, 2) AS DIFF
FROM (
	SELECT 
		FLOOR(AGE/10) * 10 AGEBAND 
		, sex
		, COUNT(passengerid) AS 승객수
		, SUM(survived) AS 생존자수
		, ROUND(SUM(survived) / COUNT(passengerid), 3) AS ratio
	 FROM titanic
	 GROUP BY 1, 2
	 HAVING sex = 'male'
	 ORDER BY 1, 2
) A
LEFT JOIN 
(
	SELECT 
		FLOOR(AGE/10) * 10 AGEBAND 
		, sex
		, COUNT(passengerid) AS 승객수
		, SUM(survived) AS 생존자수
		, ROUND(SUM(survived) / COUNT(passengerid), 3) AS ratio
	 FROM titanic
	 GROUP BY 1, 2
	 HAVING sex = 'female'
	 ORDER BY 1, 2
) B
ON A.AGEBAND = B.AGEBAND
;

출처: SQL로 맛보는 데이터 전처리 분석

---

위 코드 중 마지막 코드는 인라인뷰에 조인을 건다는 좋은 문제이므로, 마지막 코드의 작성방식을 리뷰하겠습니다.

 

select 
from (


) A
LEFT JOIN 
(


)
ON
;

- 인라인뷰, 조인을 보고 위와 같이 코드의 큰 틀을 잡아줍니다.

 

---

select 
from (
	select 
		floor(AGE/10) * 10 AS AGEBAND -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
		, sex as 성별
		, count(*) as 승객수
		, sum(survived) as 생존자수
		, round(sum(survived) / count(Age), 3) as 비율
	from 
		titanic
	group by 1, 2
	having sex = 'male'
	order by 1, 2
	
) A
LEFT JOIN 
(


)
ON
;

위에서 작성한 남성의 생존 비율코드를 활용해서 서브쿼리로 넣어줍니다.

---

select 
from (
	select 
		floor(AGE/10) * 10 AS AGEBAND -- CAST() 활용하면 형변환, 문자 ==> 숫자, 숫자 ==> 문자
		, sex as 성별
		, count(*) as 승객수
		, sum(survived) as 생존자수
		, round(sum(survived) / count(Age), 3) as 비율
	from 
		titanic
	group by 1, 2
	having sex = 'male'
	order by 1, 2
	
) A
LEFT JOIN 
(
	SELECT 
			FLOOR(AGE/10) * 10 AGEBAND 
			, sex
			, COUNT(passengerid) AS 승객수
			, SUM(survived) AS 생존자수
			, ROUND(SUM(survived) / COUNT(passengerid), 3) AS ratio
		 FROM titanic
		 GROUP BY 1, 2
		 HAVING sex = 'female'
		 ORDER BY 1, 2
) A
ON
;

같은 방식으로 여성의 생존비율 코드를 서브쿼리로 작성해줍니다.

---

 

SELECT 
	A.AGEBAND
    , A.ratio AS M_RATIO
    , B.ratio AS F_RATIO
    , ROUND(B.ratio - A.ratio, 2) AS DIFF
FROM (
	SELECT 
		FLOOR(AGE/10) * 10 AGEBAND 
		, sex
		, COUNT(passengerid) AS 승객수
		, SUM(survived) AS 생존자수
		, ROUND(SUM(survived) / COUNT(passengerid), 3) AS ratio
	 FROM titanic
	 GROUP BY 1, 2
	 HAVING sex = 'male'
	 ORDER BY 1, 2
) A
LEFT JOIN 
(
	SELECT 
		FLOOR(AGE/10) * 10 AGEBAND 
		, sex
		, COUNT(passengerid) AS 승객수
		, SUM(survived) AS 생존자수
		, ROUND(SUM(survived) / COUNT(passengerid), 3) AS ratio
	 FROM titanic
	 GROUP BY 1, 2
	 HAVING sex = 'female'
	 ORDER BY 1, 2
) B
ON A.AGEBAND = B.AGEBAND
;

그 후 key-value는 AGEBAND에 해당함을 판단하고 ON 뒤에 코드로 작성해줍니다.

위로 올라가 SELECT문 밑을 완성할때 ratio만을 작성하면 ratio가 중복으로 존재하므로 오류가 발생합니다.

중복 방지를 위해 A.ratio와 B.ratio로 명확히 명시해줌을 주의해줍니다.

---

SELECT 
	A.embarked
    , A.sex
    , A.승객수
    , B.승객수
    , ROUND(A.승객수 / B.승객수, 2) AS ratio
FROM (
	SELECT 
		embarked
		, sex
		, COUNT(passengerid) 승객수
	FROM titanic
	GROUP BY 1, 2
	ORDER BY 1, 2
) A 
LEFT JOIN (
	SELECT 
		embarked
		, COUNT(passengerid) 승객수
	FROM titanic
	GROUP BY 1
	ORDER BY 1
) B
ON A.embarked = B.embarked
;