[𝟐𝟓.𝟎𝟐.𝟐𝟓] 𝐓𝐈𝐋 𝐍𝐎𝐓𝐄 𝟕

0. 오늘 이커머스 알고리즘 짠 내용에 대해서 정리하려고 한다. 쉽지 않다....

---

1. 오늘 구매계산식과 관련된 계산식

select user_id,a.viewer,a.buyer,ROUND((a.buyer/a.viewer) * 100, 2) as rate
FROM
(
SELECT user_id,COUNT(DISTINCT CASE WHEN EVENT_TYPE = 'VIEW' THEN event_time END) AS viewer,
COUNT(DISTINCT Case When Event_type = 'purchase' then event_time END) as buyer
FROM quest.2019_dec
group by user_id
order by user_id
) a
having rate > 0
order by user_id desc

2. 구매 포기율

select user_id,a.in_cart,a.remove,ROUND((a.remove/a.in_cart) * 100, 2) as rate
FROM
(
SELECT user_id,COUNT(DISTINCT CASE WHEN EVENT_TYPE = 'cart' THEN event_time END) AS in_cart,
COUNT(DISTINCT Case When Event_type = 'remove_from_cart' then event_time END) as remove 
FROM quest.2019_dec
group by user_id
order by user_id
) a
having rate > 0
order by user_id desc

select user_id,a.buyer,a.in_cart,a.remove,a.viewer,ROUND((a.remove/a.in_cart) * 100, 2) as rate
FROM
(
SELECT user_id,
COUNT(distinct case when EVENT_TYPE = 'purchase' then event_time END) as buyer,
COUNT(distinct case when EVENT_TYPE = 'view' then event_time END) as viewer,
COUNT(DISTINCT CASE WHEN EVENT_TYPE = 'cart' THEN event_time END) AS in_cart,
COUNT(DISTINCT Case When Event_type = 'remove_from_cart' then event_time END) as remove 
FROM quest.2019_dec
group by user_id
order by user_id
) a
having rate > 0
order by user_id desc

3. 버린 수랑 총 합의 과정

select user_id,SUM(price) as '가격 총합', AVG(price) as '평균 가격' , COUNT(price) as '버린 수'
from quest.2019_dec
where event_type = 'remove_from_cart'
group by user_id
order by user_id asc
limit 10

4. 유저 세선 한계

select user_id,sum(price) as '매출액',count(distinct user_session) as '유저 유입수'
from quest.2019_dec
group by user_id,price
having count(user_id)>1
and count(user_session)>1
order by max(price) desc, user_id asc

일단은 이렇게 SQL을 코드로 중점적으로 짰는데 가장 큰 문제가 NULL 값을 잡아내고 있지 못하다는 거였다. 이 4개의 코드는 모두 NULL 값이 출력되면 무의미해서 이번에 다시 한번 가긴 했는데... 

피드백을 다시 받았다. Datediff 함수랑 with 함수.
With 함수를 사용하면 첫번째와 두번쨰에 넣으면, 정의를 더 내리지 않고 할 수가 있다고 한다. 그리고 Datediff 는 시간을 뺄 수 있는 기능 포함.

그래서 피드백 받은 내용은 2가지 였다.

SELECT user_id,
       DATEDIFF(COALESCE(carttime),COALESCE(viewtime)) AS diff1,
       DATEDIFF(COALESCE(ptime, carttime), COALESCE(carttime, ptime)) as diff2,
       DATEDIFF(COALESCE(ptime, viewtime), COALESCE(viewtime,ptime)) as diff3
FROM (
    SELECT user_id, 
           CASE WHEN event_type = 'view' THEN event_time END AS viewtime,
           CASE WHEN event_type = 'cart' THEN event_time END AS carttime,
           CASE WHEN event_type='purchase' then event_time end as ptime
    FROM quest.2020_jan
    GROUP BY user_id
) AS a
ORDER BY user_id;

지금 NULL 값이 나와서 coalesce를 사용했는데, 한쪽이 아닌 양쪽을 사용해야 되더라..
Datediff는 Null 이면 그대로 Null 을 출력하는 경향이 있어서 그렇다고.. 그래서 밑에서 Else Null 을 사용하는데 하나도 안먹혀서 난감했다. Null 을 0 값으로 치환해야 하나?

select round(count(distinct c)/count(distinct v)*100,2)
from(	
		select user_id, 
			case when event_type='view' then user_id else null end as v,
			case when event_type='cart' then user_id else null end as c,
			case when event_type='purchase' then user_id else null end as p
		from(	select * 
				from 2020.jan
				
				union all
				
				select *
				from 2020.feb
				union all 
			)as a
	)as a

다른건 구매 전환율인데 union all 을 사용하고 있다. 복수의 값을 추출해야하니까 이때는 중복값은 넣는게 맞겠고... 문제는 내 컴퓨터에서 시간이 걸려도 너~무 걸린다. 그래서 밤새 아무래도 이 부분에 대해서 좀.... 고쳐야 할 거 같다. 그래야 내일 문제 없이 만들 수 있을 거 같기도 하고...

일단 COUNT(DISTINCT) 부분을 사용했을 때, 괜찮았으니까 다시 한번 해봐야지.. ㅜㅜ

---

오늘 전반적으로 SQL 코드를 짜느라고 계속 힘들었다. 틈만나면, 오류가 나는 통에 지속적인 수정이 필요했다. 오늘 좀 특별히 배운 점이라고 한다면....

cast(문자열 as 치환을 원하는 것), case when + case when = 다중 조건 칼럼, case when + when = 1개의 칼럼 을 사용할 때, 이 경우에 사용한다고 한다.

이 점을 참고해서 다시 한번 상기해서 짜야될 거 같다.