1934. Confirmation Rate

로그인 성공율을 계산하는 문제이다. 처음엔 아래처럼 접근했었지만 너무 코드가 더러워지는 듯 하여 최적화를 하며 풀이를 진행해보려고 한다.

• 첫 접근 방법

# Write your MySQL query statement below
# Signups테이블은 유저 로그인 시간
# Confimations테이블은 (유저, 시간)별 이벤트 ENUM(confirmed, timeout)
# 유저 별 로그인 성공률을 소수점 두자리까지 출력.

# 전체 유저 아이디를 Signups테이블에서 가져옴
SELECT DISTINCT user_id FROM Signups;
# 유저 아이디 별 그룹하고 이벤트 수를 count함
SELECT user_id, COUNT(*) FROM Confirmations GROUP BY user_id;
# 유저 아이디 별 그룹하고 having으로 confirmed수를 가져옴
SELECT user_id, COUNT(*) FROM Confirmations WHERE action = 'confirmed' GROUP BY user_id;
# 나누어 유저별 성공율을 계산함. 이 때 0.0~1.0으로 표시
SELECT total.user_id, IFNULL(confirms.confirm_count/total.total_count, 0) as confirmation_rate
FROM (SELECT user_id, COUNT(*) as total_count FROM Confirmations GROUP BY user_id) as total
    LEFT JOIN (SELECT user_id, COUNT(*) as confirm_count FROM Confirmations WHERE action = 'confirmed' GROUP BY user_id) as confirms
    ON total.user_id=confirms.user_id
;

JOIN을 이용해 두 테이블을 합친 후 계산해보면 어떨까? 그리고 최대한 필터링 해보자.

• Signups와 Confirmations테이블 합치기(user_id 누락 없이)

SELECT * 
FROM Signups 
    LEFT JOIN Confirmations 
    ON Signups.user_id=Confirmations.user_id;

• 바로 action별 COUNT를 시도해보자.

SELECT Signups.user_id, COUNT(*) as event_count
FROM Signups 
    LEFT JOIN Confirmations 
    ON Signups.user_id=Confirmations.user_id
GROUP BY Signups.user_id
;

그러자 이벤트가 발생하지 않은 user_id 6번의 이벤트가 1건 잡히는 문제가 발생했다.

원인은 아래에 있었다.

처음에 Signups와 Confirmations테이블을 LEFT JOIN하는 과정에서 생긴 user_id 6 데이터가 Confirmations.user_id NULL값을 가지지만 Signups.user_id를 이용하며 COUNT가 된 것이다.

어떻게 하면 이를 최소의 코드로 해결할 수 있을까? 더럽게 하면 JOIN남발해서 되겠지만..
우선 지금까지의 접근 경험 상 total count용 테이블과 Confirm용 테이블이 있어야 할 듯 하다.

• 코드가 더럽더라도 맨 처음 접근한 방식에서 누락된 user_id만 추가해주기 위해 JOIN을 또 수행해보았다.

SELECT t1.user_id, IFNULL(t2.confirmation_rate, 0) as confirmation_rate FROM Signups as t1 LEFT JOIN (
    SELECT total.user_id, IFNULL(confirms.confirm_count/total.total_count, 0) as confirmation_rate
    FROM ((SELECT user_id, COUNT(*) as total_count FROM Confirmations GROUP BY user_id) as total
        LEFT JOIN (SELECT user_id, COUNT(*) as confirm_count FROM Confirmations WHERE action = 'confirmed' GROUP BY user_id) as confirms
        ON total.user_id=confirms.user_id)
) as t2
ON t1.user_id=t2.user_id
;

• 소수점 2자리 반올림 처리도 진행해주자.

ROUND(IFNULL(t2.confirmation_rate, 0),2) as confirmation_rate

통과는 했지만 확실히 코드가 너무 느리다. 공부해보자.

• 최적화
  여러 leetcode제출 안 중 가장 내 스타일에 맞는(가독성 중시..) 코드를 분석해보자.

select s.user_id , round(avg(if(c.action="confirmed",1,0)),2) confirmation_rate 
from Signups  as s
left join Confirmations as c
on s.user_id=c.user_id
group by user_id

거지같은 내 코드와 딱봐도 비교된다.
통계 함수 내에서 if를 사용하는 코드를 처음 봤다.

우선 left join만 수행한 상태는 위에서 봤다시피 아래와 같다.

그 후 통계함수를 이용해 마찬가지의 코드를 작성해낼 수 있었다.

다만 성능은 비슷한거보니 내 네트워크 문제일 듯 하다.

• 주안점
  IFNULL, ROUND, AVG 함수 내부의 IF사용
  항목을 조건에 따라 1과 0으로 설정한 후 AVG를 이용한 통계 계산
  GROUP BY후 SELECT에서 IF문을 사용하여 조건에 따른 값들의 처리