2024-01-15

SQL코드카타

 

1280. Students and Examinations(SQL) (CROSS JOIN, INNER JOIN, 다중 JOIN) (나중에 복습 필수)

https://leetcode.com/problems/students-and-examinations/

각 학생 테이블, 과목 테이블, 시험테이블을 주고 각 학생이 과목별로 몇번씩 시험을 봤는지 학생 아이디, 학생 이름, 과목, 시험친 횟수로 하여 출력하는 문제이다

SELECT stu.student_id, stu.student_name, sub.subject_name, 
        count(ex.student_id) attended_exams
FROM Students stu 
CROSS JOIN Subjects sub
LEFT JOIN Examinations ex ON stu.student_id = ex.student_id
    and sub.subject_name = ex.subject_name
GROUP BY stu.student_id, stu.student_name, sub.subject_name
ORDER BY stu.student_id, stu.student_name;

이번 것은 CROSS JOIN을 처음 써보게 되서 그런지 많이 해멧다(풀 때 몸상태가 안 좋아서 그럴 수도 있다.) CROSS JOIN하면 서로 모든 경우에 대해 곱해서 컬럼이 나오게 되므로 반복하는 효과가 있다고 해야하나..?(뭔가 말로 잘 표현을 못하겠다) student 테이블에 대해 subject 테이블을 cross join해주면 한 행에 대하여 subject테이블 만큼 반복되게 된다 그리고 그 테이블에 exam~ 테이블을 student_id와 subjectname에 대해 공통컬럼으로 left join해준 뒤 stu.student_id, stu.student_name, sub.subject_name에 대해 그룹을 만들어 준 뒤 stu.student_id, stu.student_name, sub.subject_name을 출력하고 ex.student_id를 세어준 값을 출력하여 stu.student_id, stu.student_name을 기준으로 오름차순 정렬해주는 쿼리이다.

사실 머리속에 제대로 정리가 안되서 살짝 쓰면서도 긴가민가하고 있다. 따라서 나중에 다시 복습을 확실히 해줘야 할 듯하다.

 

570. Managers with at Least 5 Direct Reports(SQL)(having, cross join, primary key, 복잡한듯?)

https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/

관리하는 대상이 5명 이상인 사람의 이름을 출력하는 문제이다.

SELECT e.name
FROM Employee e
HAVING count(managerId)>=5

우선 제일 처음은 단순히 전체 그룹에 대해(따로 그룹 만들어 준 것이 없으므로 전체가 하나의 그룹인 셈이다) mangerId의 수가 5이상이면 이름을 표시하게 만들었는데 아마 따로 조건 등을 넣어 주지 않았으므로 조건을 만족할 경우 젤 첫 행의 이름을 출력하게 된 것 같다. 쿼리의 오류를 발견한 첫 반례는 아래와 같다

| id  | name  | department | managerId |
| --- | ----- | ---------- | --------- |
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 100       |
| 103 | James | A          | 100       |
| 104 | Amy   | A          | 100       |
| 105 | Anne  | A          | 100       |
| 106 | Ron   | B          | 100       |

이런 입력 정보에 대하여 100의 id를 가진 사람에 대해 이름이 나와야하는데 id목록에 나와 있지 않으므로 null값으로 아무 값도 표시되지 않아야하는데 위에 적어 놓은대로 되어있어 제일 첫 행인 101의 이름인 John이 나오게 된다.

아래와 같이 cross join를 해주니

SELECT e_s.id ch1, e_s.name , e_s.managerId , e.id ch2, e.name , e.managerId
FROM Employee e
CROSS JOIN Employee e_s

크로스조인 하니 왜인지 모르겠지만

| id  | name  | department | managerId |
| --- | ----- | ---------- | --------- |
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
#결과
| ch1 | name  | managerId | ch2 | name  | managerId |
| --- | ----- | --------- | --- | ----- | --------- |
| 101 | John  | null      | 106 | Ron   | 101       |
| 101 | John  | null      | 105 | Anne  | 101       |
| 101 | John  | null      | 104 | Amy   | 101       |
| 101 | John  | null      | 103 | James | 101       |
| 101 | John  | null      | 102 | Dan   | 101       |
| 101 | John  | null      | 101 | John  | null      |
| 102 | Dan   | 101       | 106 | Ron   | 101       |
| 102 | Dan   | 101       | 105 | Anne  | 101       |
| 102 | Dan   | 101       | 104 | Amy   | 101       |
| 102 | Dan   | 101       | 103 | James | 101       |
| 102 | Dan   | 101       | 102 | Dan   | 101       |
| 102 | Dan   | 101       | 101 | John  | null      |
| 103 | James | 101       | 106 | Ron   | 101       |
| 103 | James | 101       | 105 | Anne  | 101       |
| 103 | James | 101       | 104 | Amy   | 101       |
| 103 | James | 101       | 103 | James | 101       |
| 103 | James | 101       | 102 | Dan   | 101 ...

이렇게 순서가 뒤집혔었다 기준 테이블? from 바로 뒤에 적은 테이블이 순서가 거꾸로 불러와서 붙어있는 듯했다. (좀 더 많은 테스트를 해보진 못하여 차후 질문 또는 테스트를 더 해봐야 할 듯하다.)

SELECT e_s.name
FROM Employee e
CROSS JOIN Employee e_s
WHERE e.managerId = e_s.id
GROUP BY e_s.name
HAVING count(e.managerId)>=5

그래서 CROSS JOIN를 이용한 뒤 뒷 열의 managerId = 앞 열의 id의 만족하는 데이터에 대해서만 앞 열의 name으로 group을 지어주었고, 처음 했던 것과 동일하게 뒷 열의 managerId수가 5이상인 데이터에 대해서만 앞 열의 이름을 출력하도록 하는 쿼리로 작성하였다.

| id  | name  | department | managerId |
| --- | ----- | ---------- | --------- |
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
| 111 | John  | A          | null      |
| 112 | Dan   | A          | 111       |
| 113 | James | A          | 111       |
| 114 | Amy   | A          | 111       |
| 115 | Anne  | A          | 111       |
| 116 | Ron   | B          | 111       |

하지만 위와 같은 반례로 인하여 오류를 발견하게 되었다. 위의 경우 각 id가 101 , 111인 경우에 대해 이름인 John, John 동명이인이겠지만 두개를 표시해줘야하는데 이름으로 그룹을 지어주었기에 같은 이름이 있는 경우에도 하나의 값만 표시되게 되었다 따라서 그룹을 짓는 기준을 인 id로 primary key 지어줘야 한다는 것을 깨달았다.

SELECT e_s.name
FROM Employee e
CROSS JOIN Employee e_s
WHERE e.managerId = e_s.id
GROUP BY e_s.id
HAVING count(e.managerId)>=5

그래서 group을 짓는 기준을 primary key인 id로 해주었다. 그러니 올바르게 작동하였다.

 

1934. Confirmation Rate(SQL) (round, sum, case when)

https://leetcode.com/problems/confirmation-rate/

전체 보낸 요청 메세지 중 확정으로 응답한 비율을 구하는 문제이다(요청 수 자체가 0일 경우는 0으로 표시한다)

SELECT s.user_id, 
        CASE 
        WHEN 
        ROUND(SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0
        END)/count(c.action),2) IS NULL 
                                        THEN 0
        ELSE 
        ROUND(SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0
        END)/count(c.action),2) 
        END confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

Signups테이블과 Confirmations테이블을 user_id를 공통컬럼으로 left join해주고 user_id으로 그룹을 지어준 뒤 user_id와 함께 값이 null일 때는 0으로 표시해주고, 아닐 때는 각 Confirmations테이블에 대해 action이 confirmed 인 경우 1로 하고 아닌 경우 0으로 해서 합을 구한 뒤 전체 action 갯수로 나눈 뒤 소수점 둘째짜리까지 반올림하여 표시해주는 쿼리이다.

처음에는 case when 가장 바깥부분이 없었는데 아래와 같았다.

SELECT s.user_id, 
        SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0
        END) sum_val,
        ROUND(SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0
        END)/count(c.action),2) confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

#input이거일때
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+
#왜 이렇게??
| user_id | sum_val | confirmation_rate |
| ------- | ------- | ----------------- |
| 3       | 0       | 0                 |
| 7       | 3       | 1                 |
| 2       | 1       | 0.5               |
| 6       | 0       | null              |

count부분에서는 0으로 나옴 null로 안 나옴 그럼 왜..? 아 설마 0/0이라 그런 듯한데 그래서 문제에서도 경우에 대해 어떻게 하라고 지시를 한듯하다(확실한 것은 질문을 통해 확인해봐야할 듯하다.)

 

620. Not Boring Movies(SQL)

https://leetcode.com/problems/not-boring-movies/

홀수id이고 description이 boring이 아닌 영화에 대해 id, 영화이름, descripition, rating을 표시하는데 rating을 기준으로 내림차순 정렬하는 문제이다.

SELECT c.id, c.movie, c.description, c.rating
FROM Cinema c
WHERE c.id%2 = 1 AND c.description !='boring'
ORDER BY c.rating DESC

 간단한 문제라고 판단되어 주어진 그대로 쿼리를 작성하였다

오늘은 프로젝트 관련 작업과 데이터 처리를 주로 하였고, 저녁까지 계속 몸상태가 좋지 못하여서(감기 같기도하고 체한 것 같기도하고 정확한 이유를 모르겠다. 밤이 되니 글을 쓰는 현재는 잠깐 상태가 괜찮아져서 후딱 작성하고 있다) 틈틈히 남는 시간에 코트카타를 조금 풀고, 저녁 회의가 마친 후 잠깐 틈이 날때 추가로 코드카타를 풀어주었다. 계획했던 대로 파이썬은 SQL코드카타 풀 여유도 적었기에 패스해주었고, 코트카타 관련만 약간 작성해주었다.

주말동안 프로젝트 관련하여 필요하다 생각되는 내용들을 혼자 찾아서 독학해두었는데 오늘도 주말에 비해 양이 적지만 약간이라도 있고, 하지만 프로젝트 관련하여 공부한 내용들은 노션 다른 페이지 들에 정리는 해두었지만, 따로 TIL용으로 정리해서 적어둔 부분이 없어 지금 따로 옮겨서 정리해서 적기에는 시간이 너무 부족할 듯하고 불필요할 듯하여 필요하다면 나중에 따로 블로그에 정리하는 편이 나을 것이라 생각되 여기서 글을 마무리한다.

(그리고 여담으로 아직까지는 마무리 보완부분까지 끝마쳐봐야 알겠지만 그래도 프로젝트는 무난히 진행되고 있는 듯하다.)