[TIL] 25.03.05 SQLD와 SQL 그리고 구미시 버스 프로젝트 시작

Prologue

오늘 한 것: SQL 코드카타 89,90번 / SQLD 이론 끝, SQLD 기출 1회차 35번까지 / 구미시 버스 도착시간 예측 논리 정리, 필요한 자료 정리, 자료 요청 전화

SQL 코드카타

---

89번 문제 정의

목표: 5명 이상의 direct reports(다른 사람을 거치지 않고 바로 보고하는 사람)를 갖고 있는 사원의 이름 구하기
= 사원 테이블에서 5개 이상 managerId에 있는 Id의 name = 5명 이상의 manager라는 의미

필요 데이터: Employee 테이블

해결방법

SELECT
    name
FROM Employee
WHERE id IN 
    (SELECT 
        managerId
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(managerID) >= 5)

0. 5개 이상 -> 요약 정보, 그 조건을 갖는 사람의 이름 -> 행 정보 --> 2개의 테이블이 필요(Join이나 서브쿼리 써야 함)
1. managerId로 묶었을 때, 개수가 5개 이상인 mangaerId를 찾는다.
2. 찾은 managerId를 employee 테이블의 id에서 찾아서 일치하는 name을 리턴한다.

90번 문제 정의

목표: 각 User의 Confirmation rate를 구하기
Confirm = get confirmed messages
모든 action = request confirm + get confirmed messages

• sign up 하고 confirm 요청 안할 수도 있음 -> confirmation rate = 0으로 생각

필요 데이터: User id, Sign up, Confirmation request and get

해결 방법

SELECT
    S.user_id
    ,ROUND(COALESCE(SUM(IF(action = 'confirmed', 1, 0)) / COUNT(action),0),2) AS confirmation_rate
FROM Signups S
LEFT JOIN Confirmations C
ON S.user_id = C.user_id
GROUP BY S.user_id

두번째 풀이 방법(AVG를 활용해서 전체 개수와 confirmed 수의 비율 구하기)

SELECT
    S.user_id
    ,ROUND(AVG(IF(action = 'confirmed',1,0)),2) AS confirmation_rate
FROM Signups S
LEFT JOIN Confirmations C
ON S.user_id = C.user_id
GROUP BY S.user_id

0. 구해야 하는 User - Sign up: 모든 유저는 sign up 해야 함 -> 기본 테이블
1. Confirmation rate 구하기

-> LEFT JOIN으로 Confirmation 테이블 가져와서 집계하기
2. 유저 별 Confirmation rate
-> User로 그룹화하기 -> Confirmed 수 / 각 유저별 action 수
-> 각 행마다 confirmed인지 아닌지를 판단해서 1또는 0으로 만들기: IF문
-> 그걸 각 User 수로 나누기 : COUNT(action-아무거나 상관없음) 혹은 AVG

SQLD 이론, 기출 1회차

SQLD 이론을 1회독했는데 애매했던 개념이 정리돼서 좋았고,,
특히 쿼리가 한 행 마다 실행된다는 걸 알고나니 쿼리가 좀 더 잘 이해됐다..! 예를 들어 FROM 절에서 테이블을 가져오고 WHERE 절이 실행되면 한 행마다 조건을 검사해서 조건에 일치하는 행을 가져오는 것.

그러나..기출

아직 제대로 머리에 이해되지 않았거나 외워지지 않은 것이 꽤 있었다..
한 30-40% 정도?^^ 그래서 여러 번 반복해서 이해하고 외워야 할 것 같다.

구미시 버스 도착시간 예측

구글 슬라이드에 조사한 내용을 정리하면서 조금씩 진행하고 있다.
어제 버스 도착 시간 로그 데이터를 찾아서 진행할 수 있다고 확신하게 됐고, 거기에 오늘 구미 시청에서 버스 데이터를 제공해줄 수 있다고..!
정보공개청구 사이트로 청구 신청을 하면 더 수월할거라고 말씀해주셔서
청구 신청도 했다. 그 과정에서 도착 시간을 예측하는데 필요한 로직과 데이터를 정리할 수 있는 건 뜻밖의 큰 수확이었다!!