40일차) 내일배움캠프 데이터 분석 TIL - 머신러닝(5), QCC

SQL 코드카타

Q156. The Blunder

1. 문제 링크: https://www.hackerrank.com/challenges/the-blunder/problem

2. 정답 코드:

SELECT
    CEIL(AVG(e.Salary) - AVG(z.Salary_zero_x))
FROM EMPLOYEES as e
JOIN (SELECT REPLACE(Salary, 0, '') as Salary_zero_x FROM EMPLOYEES) as z

CEIL 함수로 올림 처리
0을 ''으로 REPLACE 하여 원하는 컬럼(Salary_zero_x) 만들어서 계산

 

Q169. New Companies

1. 문제 링크: https://www.hackerrank.com/challenges/the-company/problem

2. 정답 코드:

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT l.lead_manager_code),
    COUNT(DISTINCT s.senior_manager_code),
    COUNT(DISTINCT m.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM Company as c
JOIN Lead_Manager as l
  ON c.company_code = l.company_code
JOIN Senior_Manager as s
  ON l.company_code = s.company_code
JOIN Manager as m
  ON s.company_code = m.company_code
JOIN Employee as e
  ON m.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code;

DISTINCT + 집계컬럼이 아닌 나머지 컬럼은 GROUP BY로 조건 주기!

 

Q97. 1174. Immediate Food Delivery II

1. 문제 링크: https://leetcode.com/problems/immediate-food-delivery-ii/description/

2. 정답 코드:

SELECT
    ROUND(SUM(IF(order_date = customer_pref_delivery_date, 1, 0))/COUNT(*)*100, 2) as immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
                                    SELECT
                                        customer_id,
                                        min(order_date)
                                    FROM Delivery
                                    GROUP BY customer_id
                                    )

# 정답 쿼리

3. 오류 상황: 계속 비율 값이 다르게 나옴

4. 시도 방법: 생성형 AI를 활용해 힌트를 얻음

5. 최종 문제 해결 방법: 쿼리 구조 변경

 [문제 풀이 순서]
1. 첫번째 주문만을 먼저 뽑아 내기 위해서 WHERE 절에서 IN () 조건을 사용해서 필터링 해준다!
이 때, customer_id와 order_date 둘 다 가져와야 하므로 괄호()로 묶어준다.

2. WHERE 절이 아닌 SUM(IF()) 구조를 활용해서 order_date = customer_pref_delivery_date 인 조건을 SUM 해준다!
(WHERE 절로 조건을 걸게 되면 COUNT(*) 해도 똑같은 결과가 나와버려서 비율이 100%가 됨)
+ SUM(IF()) 구조도 있지만, AVG(order_date = customer_pref_delivery_date)를 사용한다면 참일 때 1, 거짓일 때 0으로 알아서 평균을 내주기 때문에 더 편하다!

SELECT
    ROUND(AVG(order_date = customer_pref_delivery_date, 1, 0)/COUNT(*)*100, 2) as immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN (
                                    SELECT
                                        customer_id,
                                        min(order_date)
                                    FROM Delivery
                                    GROUP BY customer_id
                                    )

# AVG로 재작성한 쿼리

SELECT
    ROUND(COUNT(immediate.customer_id) / COUNT(first_order.customer_id) * 100, 2) AS immediate_percentage
FROM (SELECT
        customer_id,
        order_date
    FROM Delivery
    GROUP BY customer_id
    HAVING order_date = MIN(order_date)
    ) AS first_order
LEFT JOIN
    (SELECT
        customer_id
    FROM Delivery
    WHERE order_date = customer_pref_delivery_date
    ) AS immediate
  ON first_order.customer_id = immediate.customer_id

# 내가 작성한 쿼리

 

---

Python 코드카타 (https://github.com/heeso0908/codekata.git)

Q52. 콜라 문제 ★

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/132267

2. 정답 코드:

def solution(a, b, n):
    answer = 0
    while n >= a:
        new = n // a * b
        answer += new
        n = new + (n % a)
    return answer

while 문을 사용해서 n >=a 를 만족하는 동안만 코드가 실행되도록 한다!  
새로 받는 콜라 병 수 → new = n // a * b
new 값을 answer에 계속해서 더해주고, n은 새로 받은 콜라 병 수(new) + 기존에 남아있는 병 수(n % a)로 계산한다!

---

라이브 세션) 머신러닝 실습 3일차: 분류 모델 평가지표

수치형 데이터 / 범주형 데이터 / 문자형 데이터는 각각 전처리 방법이 다르므로 처음부터 컬럼을 분리해주는 것이 좋다!

classification_report → precision(정밀도) / recall(재현율) / f1-score(조화평균) / support 다 볼 수 있음!

---

• Precision - 스팸 메일 필터링
• Recall - 암 진단과 같은 질병 관련
• 비용 관점에서 보는 것도 중요! 비용을 보수적으로 본다면 Precision, 비용을 떠나 모델 판단력이 중요하다면 Recall

---

---

라이브 세션) 머신러닝 이론 4일차: 분류

0) 회귀(Regression) vs 분류(Classification)

모델이 내놓는 결과값이 무엇이냐에 따라 이름이 달라진다.

• 회귀 (Regression): 예측값이 연속적인 숫자일 때
  • 값이 끊기지 않고 이어지며, '얼마나(How much)'를 묻는 문제
  • 예: 내일 기온(25.4도), 주식 가격(82,300원), 배송 소요 시간(42분)
• 분류 (Classification): 예측값이 정해진 카테고리(범주) 중 하나일 때
  • '무엇(Which one)'을 묻는 문제
  • 예: 스팸 여부(Yes/No), 동물 종류(개/고양이/토끼), 학점(A/B/C)

---

1) 로지스틱 회귀

: 입력을 점수(z)로 만들고, 그 점수를 sigmoid로 눌러 확률로 변환한 뒤, 분류를 수행하는 모델

→ 로지스틱 회귀는 두 클래스를 나누는 직선(경계선=Boundary)을 찾음

 

 

(1) Sigmoid 함수 - 점수를 확률로 바꿔주는 변환기

모델이 계산한 점수(z, 범위: 무한대)를 0~1 사이의 확률로 바꿔줌!

 

(2) 전처리 : Pipeline 무조건 사용해서 데이터 누수 방지하기!!!

 

(3) 실제 값과 확률을 비교해서 벌점을 매긴다 → Cross Entropy(확률 예측 오차)

 

---

임계값을 0.5에서 낮추면 → FP가 늘어남 → Precision이 작아짐 & Recall이 커짐

임계값을 0.5에서 높이면 → FN이 늘어남 → Recall이 작아짐 & Precision이 커짐

 

오탐 = FP

누락 = FN

---

모델을 잘 적용할 수록 겹치는 구간이 적음 → 임계값 설정하기 편함

겹치는게 많으면 임계값을 어떻게 설정할 것인지가 중요해짐!

---

QCC 1회차

Q1.

select
  invoice_id,
  total
from invoices
where billing_country = 'USA'
  and total >= 15
order by total desc;

 

Q2.

select
  genre_id,
  count(track_id) as track_count
from tracks
group by genre_id
order by track_count desc
limit 10;

 

Q3.

select
  first_name,
  last_name,
  email
from chinook_customers
where email like '%@gmail.com'
  and country <> 'Brazil'
order by first_name;

 

Q4.

select
  c.customer_id,
  sum(i.total) as total_amount
from (select * from chinook_customers where country = 'USA') as c
join (select * from invoices where billing_country = 'USA') as i
  on c.customer_id = i.customer_id
group by c.customer_id
having sum(i.total) > 37.62
order by c.customer_id;

with customer as (
  select * from chinook_customers where country = 'USA'
), invoice as (
  select * from invoices where billing_country = 'USA'
), customer_invoices as (
  select c.customer_id, sum(i.total) total 
  from chinook_customers c
  join invoices i 
  on c.customer_id  = i.customer_id 
  group by 1
)
select * 
from customer_invoices
where total > 37.62 
order by customer_id