2024-02-08

SQL코드카타

 

Placements(SQL)(with, case when, left join 같은 테이블 2개 더 가져오기)(join하는데 A테이블에 B테이블 두번 각각 열에 대해 join해주고 그것의 결과끼리 비교하여 판단결과 컬럼으로 추가하기)

Placements | HackerRank

가장 친한 친구가 자신보다 더 높은 연봉을 제안 받은 학생의 이름을 가장 친한 친구가 받게되는 연봉의 금액에 따라 오름차순 정렬하는 문제이다.

SELECT 
    f.id, f.friend_id, pid.salary, pfid.salary friend_salary,
    CASE 
        WHEN pid.salary < pfid.salary THEN "friend"
        WHEN pid.salary > pfid.salary THEN "owner"
        ELSE "same" #문제에서는 같은경우 없다고 했지만 혹시몰라서
    END comparison
FROM 
    friends f
LEFT JOIN 
    packages pid ON f.id = pid.id
LEFT JOIN
    packages pfid ON f.friend_id = pfid.id

위는 임시테이블로 정의할 쿼리이다 간단히 설명하면 우선 friends 테이블에서 packages 테이블을 left join하는데 각각 id와 같은 것으로 한번 friend_id와 같은것으로 한번해서 총 두번 left join해주었다. 그런 뒤 id와 friend_id, id의 salary, 친구의 salary, 그리고 id와 친구의 salary를 비교하여 친구가 더 크면 friend로 표시하고 id가 더 높을 경우 owner라고 표시해주며 (문제에서는 없다라고 전제해주었지만 혹시나 하는 생각에 넣어준) 같을 경우 same으로 표시해주는 컬럼comparison을 표시하도록 하였다

이 쿼리를 임시테이블 compare_salary로 선언해주었다

그리고 전에도 적을까하다 빼먹었던 것 같은데 hackerrank에서는 한글로 주석달아놓으면 에러가 나서 실행시에는 한글로 주석달아놓은 부분은 지우고 실행해야한다.

WITH compare_salary AS(
    SELECT 
        f.id, f.friend_id, pid.salary, pfid.salary friend_salary,
        CASE 
            WHEN pid.salary < pfid.salary THEN "friend"
            WHEN pid.salary > pfid.salary THEN "owner"
            ELSE "same"
        END comparison
    FROM 
        friends f
    LEFT JOIN 
        packages pid ON f.id = pid.id
    LEFT JOIN
        packages pfid ON f.friend_id = pfid.id
)
SELECT 
    s.name
FROM 
    compare_salary cs
LEFT JOIN 
    students s ON cs.id = s.id
WHERE 
    cs.comparison = 'friend'
ORDER BY 
    cs.friend_salary

그리고 이어서 메인쿼리에 대해서도 간단히 설명하자면 우선 둘을 id를 기준으로 left join(계속 left join하는 이유는 혹시나 빠진 데이터가 있으면 확인하기 위해서 해주는 것으로, 원래는 나눠서 따로 체크해본 뒤 의도에 따라 join이나 left join을 골라서 하면되는데 매번 나눠서 바꿔가며 하기는 버거로운 감이 있어서 한번에 해줄려고 left join부터 사용하는 것이다) 해주었다

그런 뒤 comparison이 friend인 것만 데이터(친구가 salary가 더 큰 경우)를 가져온다 그런 뒤 id의 이름을 출력해주는데 정렬을 친구의 salary를 기준으로 정렬해주는 쿼리이다.

 

Symmetric Pairs(SQL)(with, case when, group by, distinct, join) (먼저 전체로 그룹핑해서 그러한 형태의 데이터가 몇개있지 세고 그것을 기준으로 활용할지 안할지를 추가로 체크하는 조건으로써 사용)

Symmetric Pairs | HackerRank

(x1,y1), (x2,y2)에 대하여 x1=y2, x2=y1이 되면 대칭쌍이라고 하는데 모든 대칭쌍을 찾아서 x를 기준으로 정렬하여(x≤y되는 식으로) 출력하는 문제이다

SELECT 
    *
FROM 
    functions f
LEFT JOIN 
    functions f2 ON f.y = f2.x AND f.x = f2.y

우선 데이터를 한번 살펴보기 위해 러프한 가장 간단한 조건만 걸어서 확인해주니

예상되로 짝이 없는 데이터의 경우는 null로 표시되고, x=y인 경우 총 데이터에 하나 밖에 없을 경우(짝은 없는 경우)는 한번만 나오고, 두개가 있을 경우는 각 2번에 join이 2개씩되어서 총 4번나오게 되었다. (문제에는 그런 경우는 없는 것 같지만 만약 x=y인 데이터가 같은 값으로 3개가 존재하면 지금 쿼리로는 9개가 나올 것으로 예상되는데 이런 경우가 있다면 이 문제에서는 어떻게 해야할지는 모르겠다 하지만 없는 듯하여 나중에 복습할 때 고민해보는게 좋을 것같다)

한쌍에 대해서 한번만 표시하기 떄문에 distinct를 써도 될 것 같기는 하다

x=y가 같은 경우는 세어서 값을 표시해준다 아닌경우는 0이나 -1이나 no로

SELECT 
    f.x, f.y,
    CASE
        WHEN f.x != f.y THEN 0
        WHEN f.x = f.y THEN COUNT(*)
    END cnt
FROM 
    functions f
GROUP BY 
    f.x, f.y

위 쿼리는 임시테이블로선언할 쿼리이다

간략히 설명하면 functions 테이블에서 x와 y에 대해 그룹핑해주고 x, y 그리고 x와 y가 다를 경우는 0으로 같을 경우는 그 갯수를 표시하도록 표시해주었다

그런 뒤 임시테이블의 이름을 cnt_check_functions로 선언해주었다

WITH cnt_check_functions AS(
    SELECT 
        f.x, f.y,
        CASE
            WHEN f.x != f.y THEN 0
            WHEN f.x = f.y THEN COUNT(*)
        END cnt
    FROM 
        functions f
    GROUP BY 
        f.x, f.y
)
SELECT 
    DISTINCT cf.x, cf.y
FROM 
    cnt_check_functions cf
JOIN 
    functions f2 ON cf.y = f2.x AND cf.x = f2.y
WHERE 
    cf.x < cf.y
    OR (cf.cnt > 1 AND cf.x = cf.y)
ORDER BY 
    cf.x

메인 쿼리에 대해서도 간단히 설명하면 위 임시테이블과 function테이블을 x1=y2, x2=y1의 식으로 만족하는 경우로 join해주고(아닌 것들은 제외해주기 위해서) 그러한 것들 중 x<y이거나 x=y인데 한개만 있는 경우가 아닌 경우 중복없이 x와 y를 출력하여 x를 기준으로 정렬해주는 쿼리이다

오늘은 프로젝트 관련하여 오전은 코딩을 하여 살짝 기존 구현해놓은 부분들을 보완하는 작업 위주로 해주었고, 오후부터는 평가지표 관련하여 공부했는데 공부한 내용은 지금 정리하여 적기에는 많은 듯하여 내일 외가댁을 갔다와서 추가해주도록 하겠다

 

코드 패치한 부분

2/7

• X,y 초기화기능 함수로 구현하여 추가 reset_X_y(data:pd.DataFrame = train_df, y_col='대출등급')
• 이상치 제거 함수로 구현
• 결측치 처리 함수 부분 아직 작업중

2/8

• 0인경우 에대해 범주형으로 추가해준것 비교 + 해당하는 row 데이터 조회할 수있게 아래 코드로 구현해뒀음
• train과 test 데이터 분리후 reset_index기능 추가 꼭 사용해야할까 생각이 되어 일반 주석처리
• df에서 ID부분 제외시키던 것 다시 주석처리해서 포함시킴
• 인코딩하는 부분 일단 전부 get_dummies로 인코딩하게 주석해제시켜놓음

→ 차후 계획:

고정적으로 함수안에 어떤 컬럼에 대해서 어떤 방식으로 할지 먼저해서 각 인코딩방식(여러개 쓰면 조금 복잡해지는 부분있었던 것으로 기억) 적용해서 먼저 구현해보고

그 뒤 입력인자로 컬럼 리스트를 4가지로 받아서 각 순서, 레이블, get_dummies, 원핫으로 사용할 수 있게 구현시켜볼 예정

(창완님 하신 부분)

주어진 열들에 대하여 IQR 방식 기준으로 이상치 확인하는 함수 주어진 열들에 대하여 IQR 방식 기준으로 이상치 제거하는 함수

평가표에 평가지표 accuracy, recall, precision, f1-score로 출력되게 추가

confusioin matrix와 multilabell_confusioin matrix 출력 선택기능과 함께 추가

 

다중분류 평가지표 (노션에 적은 내용 옮겨적기에는 많고 힘들 것 같아 일단 노션 문서 자체 링크를 연결해 둠)

 

그리고 SQL코드카타는 많은 시간을 투자할 수는 없었기에 비교적 풀만하다 싶은 문제 위주로 앞에서 부터 풀어서 두문제 풀었다(나름 문제 난이도도 적당하고 괜찮았던 것 같다) 원래 계획으로는 3문제 풀려고 하였는데 앞에서 부터 순서대로 첫순서인 문제가 어제도 풀려다가 복잡하여 오래 걸릴 것 같아 타협하여 그전 문제까지만 풀고 마무리하였는데 오늘도 시간이 나지 않을 듯하여 우선 패스를 하였다 그리고 그 문제를 패스하고 순서대로 3번째문제도 피곤해서 그런지 눈에 잘 들어오지않고 시간도 늦어서 마무리를 지었다

추가로 SQL관련해서 간단한 질문을 추가적으로 할 기회가 되어 질문드렸는데 간단한 내용들이라 질문들이라 평소 올리듯 하나하나 하지않고 공통되는 부분이 많아 공통적으로 결론을 적는 방식으로 간단히 적어둘려고 한다

SQL은 말 그대로 1차적으로 데이터베이스에서 데이터들을 가져오고 하는 역할 등에만 주로 사용되고(그래서 해봤자 max, min, average 등 간단한 함수들만 사용되는 편) sqrt나 pow,  median 같이 수학적인 계산이나 통계적인 계산은 주로 다른 파이썬 등에 데이터를 옮겨서 작업하는 편이라는 답변을 들었으니 그렇게 생각하면 될듯하다

피곤하기도 하고 내일은 일찍일어나서 출발할 준비를 해야하는 관계로 평소보다 일찍 마무리하도록 하겠다