Q. 문제
You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.
Inpur Format
- Hackers : The hacker_id is the id of the hacker, and name is the name of the hacker.
| Column | Type |
|---|---|
| hacker_id | Integer |
| name | String |
- Submissions : The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.
| Column | Type |
|---|---|
| submission_id | Integer |
| hacker_id | Integer |
| challenge_id | Integer |
| score | Integer |
시행착오 및 새롭게 알게된 내용
- 먼저 집계함수의 중첩이 불가능 하다는 것을 알게되었다. 서브쿼리도 짜기전에 간단하게 의식의 흐름대로 한번 일부 쿼리문을 만들어 보자하고 만든것이 아래 쿼리였다.
하지만, 위와 같이 쿼리를 작성하게 되면group function과 관련된 에러가 생겼다.
열심히 구글링을 하게되었고 group by 하고나서 집계함수를 여러개를 사용할 수 없다고 한다...
- Group by 와 SELECT 간의 상호작용
sql 기본 문법을 학습하고 프로그래머스 문제 전부와 해커랭크 40문제를 바로 풀기 시작해서 베이스가 되는 규칙이 부족할지도 모른다고 생각했지만 문제를 풀면서 배워나가자고 했었다. 하지만 그결과 왜 틀린지 모르는 작은 규칙에 막혀 하루종일 시간을 소비했다. 바로 GROUP BY 와 SELECT의 관계이다. GROUP BY 에 없는것은 SELECT를 할 수없지만 집계함수(예컨데 SUM,COUNT등) 같은 것을 이용하면 가능하다. 또한 GROUP BY 에 있다고 해서 무조건 SELECT에 사용할 필요는 없다. 다음 사진이 이 규칙의 일부가 반영된 쿼리중 하나이며, 해당문제의 서브쿼리를 만들때 쓰는 핵심 문장이기도 하다.
- 서브쿼리의 활용 용도
이전에 여러 문제들을 풀면서도 서브쿼리를 활용했지만 1번에서 언급한 중첩이 불가능한 규칙때문에 서브쿼리를 활용하여 풀 수 있다는것을 알게되었다. 서브쿼리의 활용은 무궁무진 하다는 것을 알게되었다. 1번에서 중첩이 되지않아 SUM(MAX(score))를 쓰지못해 서브쿼리를 통해서 MAX(score)를 뽑아주면 SUM이라는 한개의 집계함수를 통해서 쿼리문을 작성할 수 있어서 1번 에러에 걸리지 않고 쿼리가 작동하게 된다.
문제 풀이
- 정답
SELECT H.hacker_id, H.name, SUM(sq.m_score)
FROM (SELECT hacker_id, MAX(score) as m_score FROM Submissions GROUP BY hacker_id, challenge_id ) AS sq
LEFT JOIN Hackers H ON sq.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name
HAVING SUM(sq.m_score) != 0
ORDER BY SUM(sq.m_score) DESC, H.hacker_id- 풀이
먼저 나의 쿼리 작성 습관?방법? 은 모든 쿼리 문을 한줄에 작성하는 것이다.(SELECT 한줄 FROM한줄)
먼저 FROM절을 봐야한다.
위에서 1번 에러로 인해 집계함수 중첩을 사용할 수 없어 서브쿼리로 MAX값을 뽑아주어야 하기때문에 FROM절에 서브쿼리를 만들어 주었다. 이 문장에서는 GROUP BY를 hacker_id와 challenge_id로 해주었는데 이렇게해야 사람에 따른 그리고 각각의 도전문제에 따라 score가 나타난다 그중에서 MAX(score)를 뽑아주면되는것이다. 여기서 SELECT에 hacker_id, MAX(score)를 사용한것은 위의 2번 에서 설명한 GROUP BY와 SELECT의 관계때문이다.
두번째로 SELECT문을 완성시켜준다.
서브쿼리문을 sq(서브쿼리에서 가져왔다)로 저장해 주었고 MAX(score)를 m_score로 저장해준것을 기억하자!! 문제에서는 해커id, 이름, 총점수 를 출력해야하기 때문에 Hackers를 H로 만들어준다고 생각하고 H.hacker_id와 H.name그리고 서브쿼리문에서 변수를 가져와서 SUM(sq.m_score)를 통해 SELECT문을 완성 시킬 수 있다.
세번째로 JOIN을 시켜준다
이제 두 테이블 합쳐줘야한다. Hackers를 Submissions 테이블에 LEFT JOIN 을 시킨고 hacker_id로 묶어준다. 여기서 처음 테이블을 sq로 저장한것을 잊지말자.
네번째로 합친테이블을 GROUP BY한다
합쳐진 테이블의 형태를 떠올려보고 SELECT문에 있는 해커ID와 이름에 따라서 묶어주어야 SUM을 통해서 합계를 출력할 수 있다. 여기서 한가지 더 GROUP BY와 SELECT관계 잊지말자
마무리
이제 남은것은 조건과 정렬이다. GROUP BY 를 해주었기 때문에 WHERE이 아닌 HAVING절을 이용하여 0인경우 제외를 하고 내림차순,오름차순을 정의해준다.
짜잔!!
문제 후기
group by 를 어떻게 해주느냐 그리고 집계함수의 중첩이 불가능하다는것이 이번 문제의 핵심이었던것 같다. 파이썬도 뛰어나진 않지만 sql도 파이썬 만큼만 활용을 잘하면 좋겠는데 그래도 점점 늘어가는것 같다. ㅎㅎㅎ 더 어려운 문제 나와라!!
