240606_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타 174번
having절에 조건이 복잡하게 들어가는 문제.
이 문제에서 구해야 하는 것은 크게 세 가지다.

1. hacker_id별로 생성한 challenge_id의 숫자를 세되,
2. 가장 많은 챌린지를 생성한 경우에는 모든 hacker_id를 출력해야 하고
3. 가장 많은 챌린지를 생성한 경우가 아니라면 동일한 숫자의 챌린지를 생성한 hacker_id는 출력되지 않아야 한다.

기본이 되는 뼈대는 아래와 같다.

SELECT h.hacker_id,
       h.name,
       Count(c.challenge_id) AS challenges_created
FROM   challenges c
       LEFT JOIN hackers h
              ON c.hacker_id = h.hacker_id
GROUP  BY 1,2

이렇게 출력하면 모든 경우의 수가 다 나오는데, 여기서 2,3번 조건을 만족시켜야 하므로 group by 뒤에 having 절을 추가해 주면 된다. 먼저 비교적 간단한 2번부터 해결해 보면

SELECT h.hacker_id,
       h.name,
       Count(c.challenge_id) AS challenges_created
FROM   challenges c
       LEFT JOIN hackers h
              ON c.hacker_id = h.hacker_id
GROUP  BY 1,2
HAVING challenges_created = (SELECT Count(challenge_id)
                             FROM   challenges
                             GROUP  BY hacker_id
                             ORDER  BY 1 DESC
                             LIMIT  1) 

이렇게 하면 challenge_created가 최대값인 경우만 출력된다. 이제 3번 조건을 추가해야 하므로 먼저 작성한 having절 뒤에 or 조건을 줘서 두 번째 having 절을 추가한다.

SELECT h.hacker_id,
       h.name,
       Count(c.challenge_id) AS challenges_created
FROM   challenges c
       LEFT JOIN hackers h
              ON c.hacker_id = h.hacker_id
GROUP  BY 1,2
HAVING challenges_created = (SELECT Count(challenge_id)
                             FROM   challenges
                             GROUP  BY hacker_id
                             ORDER  BY 1 DESC
                             LIMIT  1)
        OR challenges_created IN (SELECT cnt
                                  FROM   (SELECT hacker_id,
                                                 Count(*) AS cnt
                                          FROM   challenges
                                          GROUP  BY 1) a
                                  GROUP  BY 1
                                  HAVING Count(*) = 1) 

두 번째 having 절까지 추가된 버전.

여기서 쿼리가 갑자기 복잡해지는 이유는 having절의 in 조건 뒤에 서브쿼리가 두 번 들어가기 때문. 먼저 hacker_id별로 count한 결과를 a라는 인라인뷰로 쓰고, 그걸 다시 컬럼값으로 받아서 count(*)가 1인 경우만을 challenge_created에 포함시켜야 한다. 즉, count가 2가 됐든 3이 됐든간에 특정 챌린지 횟수를 1회만 가진 경우를 count하기 위해 이렇게 쿼리를 쓰게 되는 것.

SELECT h.hacker_id,
       h.name,
       Count(c.challenge_id) AS challenges_created
FROM   challenges c
       LEFT JOIN hackers h
              ON c.hacker_id = h.hacker_id
GROUP  BY 1,2
HAVING challenges_created = (SELECT Count(challenge_id)
                             FROM   challenges
                             GROUP  BY hacker_id
                             ORDER  BY 1 DESC
                             LIMIT  1)
        OR challenges_created IN (SELECT cnt
                                  FROM   (SELECT hacker_id,
                                                 Count(*) AS cnt
                                          FROM   challenges
                                          GROUP  BY 1) a
                                  GROUP  BY 1
                                  HAVING Count(*) = 1)
ORDER  BY 3 DESC,1 

마지막으로 order by를 써서 challenge 생성 횟수 기준으로 내림차순하고, 동률인 경우에는 hacker_id를 기준으로 오름차순 정렬하게 해 주면 완성.

---

머신러닝 기초강의 리뷰_로지스틱 회귀
로지스틱 모형을 평가하는 지표는 정확도와 F1 Score로 나뉨

<정확도를 도출하는 빌드업>

1. 정밀도(Precision) : 모델 관점
   • 모델이 양성(1)으로 예측한 샘플 중, 실제로 양성인 샘플의 비율
   • $\text{Precision} = \frac{TP}{TP + FP}$
2. 재현율(Recall) : 데이터 관점
   • 실제로 양성(1)인 샘플 중, 모델이 양성으로 예측했던 샘플의 비율 (모델의 기여도 같은 느낌)
   • 실제 데이터 기준이라 계산식이 헷갈릴 수 있음
   • $\text{Recall} = \frac{TP}{TP + FN}$
3. F1-Score : 1, 2의 조화평균
   • $F1 \, \text{Score} = 2 \times \frac{\text{Precision} \times \text{Recall}}{\text{Precision} + \text{Recall}}$
4. 정확도(Accuracy) : 모델이 올바르게 예측한 샘플의 비율
   • $\text{Accuracy} = \frac{TP + TN}{TP + TN + FP + FN}$
   • Positive든 Negative든 관계없이 True로 예측한 경우 / 전체 경우의 수

암 환자의 사례처럼 Y값이 unbalance할 경우 특히 정확도가 제 기능을 못하게 되는데, 이를 보정하기 위해 Y 범주의 비율을 5:5로 맞춰주거나 평가 지표(F1-Score)를 사용한다. Y 범주의 비율을 5:5로 맞춰주기 위한 방법으로는 언더샘플링(Undersampling, 많은 수의 샘플을 가진 클래스에서 일부 샘플을 제거), 오버샘플링(Oversampling, 적은 수의 샘플을 가진 클래스의 샘플을 복제하거나 생성), 클래스 가중치 적용 등이 있지만 커리큘럼에서 다루지는 않는 듯.

기초 강의에서는 머신러닝 중에서도 지도학습, 그 중에서도 가장 기본적인 회귀와 분류 모형을 다루었고, 조금 더 다양한 모형들을 심화 강의에서 배우게 될 예정이라고 한다. 분석가의 업무 중 화려한 모델링이나 인사이트 뽑기보다는 전처리 과정의 비중이 90% 이상이라는 말도 매우 와닿았다. 밖에서 봤을 때 품는 환상과 현실은 다르겠지.