2024-01-23

SQL코드카타

 

1978. Employees Whose Manager Left the Company(SQL)(where 서브쿼리 조건, NOT EXISTS)

https://leetcode.com/problems/employees-whose-manager-left-the-company/

각 직원들에 대해 직원의 관리자가 퇴직한 상태이면서 급여이 30000미만인 직원을 출력하는 문제이다.

SELECT e.employee_id
FROM Employees e
WHERE e.salary < 30000
 AND e.manager_id IS NOT NULL
 AND NOT EXISTS(
            SELECT e2.employee_id
            FROM Employees e2
            WHERE e2.employee_id = e.manager_id
            )
ORDER BY e.employee_id

처음에는 깜박하고 e.manager_id IS NOT NULL을 빠트려 관리자의 아이디도 같이 출력되었는데 추가해주니 바로 해결되었다

쿼리에 대해 간단히 설명하면 우선 Employee테이블로 부터 가져와서 급여가 30000미만이고 관리자가 아닌 데이터(관리자 id가 null로 되어있으면 관리자)에 대해서만 가져온 뒤 직원아이디를 출력해주는데 Where절에서 EXISTS문?을 통해 더이상 관리자 정보가 직원데이터테이블에 없는경우에 대해서만 출력해주는데 직원id로 오름차순정렬해준다. 추가로 EXISTS문관련해서 조금 더 설명하면 우선 직원테이블에서 직원아이디를 가져오는데 메인쿼리의 관리자아이디와 같은 직원아이디를 가져오게 된다 따라서 이는 각 직원에 해당하는 관리자아이디를 출력해주는 서브쿼리가 되고 이미 퇴직하고 없다면 관리자정보가 남아있지않아 아무것도 나오지 않아야 하기에 아무것도 존재하지 않는 경우만 출력하도록 NOT EXISTS를 사용해주었다

 

626. Exchange Seats(SQL) (세개 left join, with, 몫연산 div, 나머지 연산 % mod, case when, 순서의 홀짝여부에 따라 다르게 규칙적용해서 표시, 다른사람 풀이 if 인덱스에 해당하는 값들만 순서바꿔주고 정렬, lag,lead윈도우함수, coalesce, ifnull)

https://leetcode.com/problems/exchange-seats/description/

앞에서부터 홀수번째 자리와 짝수번째 자리를 바꿔주는 문제이다, 단 마지막번호가 홀수면 그 대상은 바꿀 대상이 없기에 그대로 둔다

WITH process_seat AS (
    SELECT s.id, s.student,
        s.id DIV 2 quotient,
        (s.id)%2 remainder
    FROM Seat s
)
SELECT ps.id,
    CASE 
        WHEN MAX(ps.id) != ps.id AND ps.remainder = 1 THEN s_next.student
        WHEN ps.remainder = 0 THEN s_pre.student
        ELSE ps.student
    END student
FROM process_seat ps
LEFT JOIN Seat s_next ON s_next.id = ps.id + 1
LEFT JOIN Seat s_pre ON s_pre.id = ps.id - 1

#out
| id | student |
| -- | ------- |
| 1  | Doris   |

WITH process_seat AS (
    SELECT s.id, s.student,
        s.id DIV 2 quotient,
        (s.id)%2 remainder
    FROM Seat s
)
SELECT *
-- ps.id,
--     CASE 
--         WHEN MAX(ps.id) != ps.id AND ps.quotient = 1 THEN s_next.student
--         WHEN ps.quotient = 0 THEN s_pre.student
--         ELSE ps.student
--     END student
FROM process_seat ps
LEFT JOIN Seat s_next ON s_next.id = ps.id + 1
LEFT JOIN Seat s_pre ON s_pre.id = ps.id - 1

#out
| id | student | quotient | remainder | id   | student | id   | student |
| -- | ------- | -------- | --------- | ---- | ------- | ---- | ------- |
| 1  | Abbot   | 0        | 1         | 2    | Doris   | null | null    |
| 2  | Doris   | 1        | 0         | 3    | Emerson | 1    | Abbot   |
| 3  | Emerson | 1        | 1         | 4    | Green   | 2    | Doris   |
| 4  | Green   | 2        | 0         | 5    | Jeames  | 3    | Emerson |
| 5  | Jeames  | 2        | 1         | null | null    | 4    | Green   |

WITH process_seat AS (
    SELECT s.id, s.student,
        s.id DIV 2 quotient,
        (s.id)%2 remainder
    FROM Seat s
)
SELECT ps.id,
    CASE 
        WHEN s_next.id IS NOT NULL 
                AND ps.remainder = 1 THEN s_next.student
        WHEN ps.remainder = 0 THEN s_pre.student
        ELSE ps.student
    END student
FROM process_seat ps
LEFT JOIN Seat s_next ON s_next.id = ps.id + 1
LEFT JOIN Seat s_pre ON s_pre.id = ps.id - 1

 추가로 다른사람은 어떻게 했는지 궁금하여 확인한 결과 solution으로 올린 대부분의 사람은 student는 그대로 두고 id만 바꾼다음에 정렬해주는 방식을 사용하였다 아래는 몇가지 쿼리를 가져왔다

#case 이용방식
SELECT 
    CASE 
        WHEN id = (SELECT MAX(id) FROM seat) AND id % 2 = 1
            THEN id 
        WHEN id % 2 = 1
            THEN id + 1
        ELSE id - 1
    END AS id,
    student
FROM seat
ORDER BY id

#if로 해줄시
select 
    if( id%2 <> 0, if( id = ( select max(id) from seat ), id, id+1), id-1 ) as id, 
student 
from seat 
order by id

#동일방식 다른 표현
select 
	if(mod(id, 2) = 0, id - 1, 
												if (id < (select max(id) from seat), id + 1, id)) as id,
 student 
from seat 
order by id

발상의 전환 번호만 바꿔준 뒤 정렬을 해줌

select id, 
coalesce(case when id%2=0 then lag(student) over() else lead(student) over() end, student) as student
from seat

윈도우 함수 lag과 lead사용해서 했음 그런데 윈도우함수가 연산 많이 걸려서 되도록 사용하지 말라고 하셨는데 이 경우는 오히려 사용한 것이 빠를수도 있을 것 같기도해 애매한 것 같다. 질문을 해보면 좋을 듯하다.

 

1341. Movie Rating(SQL)(with 다른사람들은 굳이 안써준듯, 날짜 조건 ~년~월 내의, UNION ALL(compare union)과 ORDER BY, LIMIT)

https://leetcode.com/problems/movie-rating/description/

가장 평점을 많이 매긴 유저의 id와 2020년 2월에 가장 높은 평균 평점을 받은 영화를 출력하는 문제이다(동점의 경우 사전기준 오름차순해서 빠른 것으로 출력)

MAX(COUNT(mr1.user_id)) max_cm 같이 한번에 사용하면 Invalid use of group function 에러가 뜬다 그래서 임시테이블에서 한번에 하지는 못하고 나눠서 하였다

WITH rating_movie AS (
    SELECT mr1.movie_id, AVG(mr1.rating) avg_m
    FROM MovieRating mr1
    WHERE created_at <= '2020-02-29'
    GROUP BY mr1.movie_id
),
count_user AS (
    SELECT mr2.user_id, COUNT(mr2.movie_id) cnt_u
    FROM MovieRating mr2
    GROUP BY mr2.user_id
)
SELECT u.name AS results
FROM count_user cu
JOIN Users u ON cu.user_id = u.user_id
WHERE cu.cnt_u = (SELECT MAX(cu_s.cnt_u) FROM count_user cu_s)
ORDER BY results
LIMIT 1
UNION
SELECT m.title AS results
FROM rating_movie rm
JOIN Movies m ON rm.movie_id = m.movie_id
WHERE rm.avg_m = (SELECT MAX(rm_s.avg_m) FROM rating_movie rm_s)
ORDER BY results
LIMIT 1;

이렇게하면

#You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
#SELECT m.title AS results
#FROM rating_movie rm
#JOIN Movies m ON rm.movie_i' at line 19

이렇게 에러가 뜨는데 union을 쓸 때는 order한 채로 사용 할 수 없다고 한다 하지만 여전히 order을 빼주어도 똑같이 에러가 발생하였는데 limit때문일것 같기도하다

검색해보니 이런식으로 그리고 union은 distinct가 생략된 것이라 겹치지 않는다는 보장이 있다면 union all을 쓰는 것이 성능상 좋을 것이라고 한다.

 반례가 나왔는데 살짝 긴가민가했게 Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name. 이라고 하였기 때문에 2월까지 하라는 건가 하고 했었는데 역시 2월 데이터 만을 이라는 의미였던 것 같다

WITH rating_movie AS (
    SELECT mr1.movie_id, AVG(mr1.rating) avg_m
    FROM MovieRating mr1
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY mr1.movie_id
),
count_user AS (
    SELECT mr2.user_id, COUNT(mr2.movie_id) cnt_u
    FROM MovieRating mr2
    GROUP BY mr2.user_id
)
(SELECT u.name AS results
FROM count_user cu
JOIN Users u ON cu.user_id = u.user_id
WHERE cu.cnt_u = (SELECT MAX(cu_s.cnt_u) FROM count_user cu_s)
ORDER BY results
LIMIT 1)
UNION ALL
(SELECT m.title AS results
FROM rating_movie rm
JOIN Movies m ON rm.movie_id = m.movie_id
WHERE rm.avg_m = (SELECT MAX(rm_s.avg_m) FROM rating_movie rm_s)
ORDER BY results
LIMIT 1);

이렇게 하니 해결되었다.

오늘은 SQL코드카타가 생각보다 쉽거나 풀만했던 것 같았다(비록 한 문제가 왜 에러가 나는지 짐작못해서 조금 시간을 날려버린 감이 없지 않아있지만).

그리고 오늘은 데이터 전처리&시각화 강의를 들었는데 그중 전처리 부분까지만 다들었다

원래 계획은 오늘 강의를 다 듣고, 통계부분까지 어느정도 복습하고 저녁시간에 강의해주신 튜터님들께 찾아가서 질문드리는게 계획이였는데, 전처리부분의 강의 중 사용된 예시 데이터 등이 강의자료와 달라서 직접 손으로 다 치다 보니 생각보다 시간이 더욱 걸렸다, 그외 에도 이번에는 VSCode환경으로 실습하게 되었는데 마침 내가 하고 있던 환경? 개발툴? 프로그램? 흠.. 표현이 다 애매한 것 같아서 정확히 뭐라고 해야할지 모르겠지만 대강tool이라고 하면 들어맞는듯하니 tool이라고 하면 tool이 최근에 vscode에 jupyter note기능을 가져와서 사용하고 있었는데 마침 동일하게 나왔길래 여러 vscode관련 기능을 새로 알게 되거나 새로 알아볼만한 단서가 생겨 추가로 검색하여 확인하거나 하는 등 (주로 초반) 중간에 시간 소모가 추가로 있었다
오늘 vscode관련해서 새로 알 수 있던 방식은 옆에 탐색기 부분 아이콘을 직관적으로 바꿀 수 있는 방법, vscode 테마 커스텀할 수 있는 방법(이 부분은 확인만하고 적용은 하지 않았다 함수, 변수 등에 따라 글씨 색 다르게 하는 것이 대표적인 듯하다. 원래 조금 밋밋했는데 csv파일을 vscode에서 열시 컬럼별로 색깔을 다르게 표시해주는 확장프로그램이 있길래 설치했더니 조금 다양하게 바뀐 느낌이 들어서 일단은 그대로 냅뒀다) 그리고 드디어 마크다운을 활용하여 전체 ipynb파일을 작성하였다! 그동안 튜터님들이 강의 하실 때 마크다운으로 하신 것 같았는데 대강 혼자서 시도해봐도 튜터님들이 하신 것 처럼 되지 않았었는데 드디어 실마리를 잡고 튜터님들이 하신 것처럼 따라 할 수 있었다(사실 마크다운 문법이라는게 존재하는 것은 알았지만 그전에는 마크다운도 실행을 해줘야 줄글처럼 표시된다는 것을 몰랐다.. 그리 간단한데 왜 못해봤을까라는 생각도 들었지만 후회한들 이건 달라지지 않으니 드디어 하는법을 알았다는 사실에 기뻐할 것이다!) 마크다운을 활용하면 좋은 점이  ipynb파일도 노션에서 할때처럼 목차를 만들 수 있게 되고 목차별로 접을 수 도 있어 짧을 때는 큰 의미가 없을 수 있지만 내용이 길고 복잡해질수록 의미가 커질 것이라 예상된다!

그리고 흠 대강 다 적으면서 직접 테스트해보면서 했기에 강의 들은 내용은 바로 적어도 될 수준은 되는 듯하나, 코드가 대부분이고 테스트 느낌처럼 과하게 출력하는 부분이 중복되게 많기 때문에 이 부분을 어떻게 블로그에 정리하여 올리면 좋을지는 추가로 생각해보고 올리는 것이 좋겠다 생각하여 오늘은 올리지 않았다.

그리고 그동안 채용공고를 데이터분석가 쪽으로는 한번도 찾아본적이 없었는데(사실 그 전에도 내가 아직 취업할 수 있는 준비가 됬다고 생각되지 않아 별로 찾아본적이 없기는 했다) 오늘 처음으로 깊이 살펴보거나 엄청 많이 찾아 본 것은 아니지만 국가취업지원제도 상담 때문에기도하고 오늘들은 강의 첫부분에서 관련하여 찾아보자고 나왔어서 겸사겸사 찾아봤다 하지만 시간이 너무 늦기도 했고 모르는 부분이 많아서 시간 소모가 좀 될듯하여 스크랩해두고 내일 마저 확인하고 추가로 찾아서 스크랩할 필요성이 있다면 할려고 한다