HackerRank SQL - Ollivander's Inventory

 

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

Input Format

The following tables contain data on the wands in Ollivander's inventory:

• Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is). 
• Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs,  and , then  and .

---

Sample Input

Wands Table: 

 Wands_Property Table: 

Sample Output

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

 

 

Problem

id, age, coins_needed, power 컬럼을 추출하라.

단, 높은 power와 age이면서 non-evil의 가장 낮은 coins_needed 값만 추출하고 power, age 순으로 오름차순 정렬하라.

 

---

Answer1

SELECT W.id, WP.age, W.coins_needed, W.power
FROM Wands W 
       LEFT JOIN Wands_Property WP ON W.code = WP.code
WHERE W.id IN (SELECT W.id
                FROM (SELECT WP.age, W.power, W.id
                      ,ROW_NUMBER() OVER(PARTITION BY W.power, WP.age ORDER BY W.coins_needed) RNUM
                        FROM Wands W LEFT JOIN Wands_Property WP ON W.code = WP.code
                       WHERE WP.is_evil=0
                      ) W
               WHERE RNUM = 1
              )
ORDER BY W.power desc, WP.age desc;

Basic Join 문제 유형은 먼저 추출할 컬럼을 나열하고 그 다음 조건들 어떻게 적용할지 구상하는게 낫다라는 생각이 들었다. 이번 문제 역시 메인 쿼리를 먼저 짜두고 서브쿼리를 그 다음에 생각하는 식으로 풀어나갔다.

 

---

How to solve

우선 조건을 다시 살펴보면,

같은 power와 age일 때, 가장 낮은 coins_needed 값을 추출해야 한다.

 

where절 안에 중첩서브쿼리를 넣을 생각으로 머리 속으로 그려보았는데, 좀 혼란스러웠다.

가져와야 되는 값은 id인데 실제 기준이 되는 값은 MIN(W.coins_needed)이다.

 

처음에 짠 쿼리는 아래와 같은 형태였는데 이건 저번에 막혔던 group by 오류를 뱉어낸다.

2023.01.24 - [데이터/SQL 문제풀이] - HackerRank SQL - Top Competitors

SELECT W.id, WP.age, W.coins_needed, W.power
FROM Wands W 
       LEFT JOIN Wands_Property WP ON W.code = WP.code
WHERE W.id IN (SELECT W.id
                FROM (SELECT WP.age, W.power, W.id, MIN(W.coins_needed)
                        FROM Wands W LEFT JOIN Wands_Property WP ON W.code = WP.code
                       WHERE WP.is_evil=0
                    GROUP BY WP.age, W.power
                    ORDER BY W.power, WP.age desc) W
              )
ORDER BY W.power, WP.age desc

 

그래서 생각한 건, 윈도우함수를 이용해 첫 번째(first_value함수) 혹은 가장 작은 값(min함수)의 행 전체를 가져오는 것이다.

컨셉은 그렇게 잡았으나, 막상하려고 하니 더 복잡할 것 같아서 row_number을 이용해 파티션으로 나눈 행번호를 가져오는 걸로 변경했다.

윈도우 함수 안에서 power, age 어차피 같은 값만 가져오는 거라서 partition by 구문에서 순서는 상관없고 order by에서는 가장 작은 coins_needed가 필요하니 해당 기준으로 오름차순 정렬한다.

SELECT WP.age, W.power, W.id
       ,ROW_NUMBER() OVER(PARTITION BY W.power, WP.age ORDER BY W.coins_needed) RNUM
  FROM Wands W LEFT JOIN Wands_Property WP ON W.code = WP.code
 WHERE WP.is_evil=0

 

 

이제 각 power, age로 이루어진 파티션에서 행번호가 부여됐으니 여기서 첫 번째 행을 각각 가져온다.

SELECT W.id
FROM (SELECT WP.age, W.power, W.id
      ,ROW_NUMBER() OVER(PARTITION BY W.power, WP.age ORDER BY W.coins_needed) RNUM
        FROM Wands W LEFT JOIN Wands_Property WP ON W.code = WP.code
       WHERE WP.is_evil=0
      ) W
WHERE RNUM = 1

해당 쿼리를 where절 중첩서브쿼리로 넣어 해당 id값만 추출하면 끝!

 

---

[ 23-01-29 : only full group by 에 관해 ] 

only full group by는 MySQL 5.7버전부터 sql_mode로 추가된 설정이라고 한다.

 새롭게 알게 된 사실이 있었는데 실제 업무에서는 한 번도 본 적이 없었던 이 오류가 왜 자꾸 여기서 나는가 찾아봤더니 'only full group by'라는 설정때문이었다.

 이제껏 실제로 업무할 때는 대충 group by로 묶어놔도 오류가 없었는데, 생각해보니 값이 이상하고 명확하지 않는 것이란 걸 깨달았다. 아무튼 결론은 지금까지 ANSI SQL에 어긋난 방식으로 업무하고 있었고, 쿼리를 잘 짜면 만나지도 않을 오류라는 것이다.

 

출처

http://jason-heo.github.io/mysql/2014/03/05/char13-mysql-group-by-usage.html

https://developyo.tistory.com/20

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by

---

[ 23-01-29 : 추가 내용 ] 

먼저 사족을 달아야 할 것 같은데, 일단 이 문제를 풀기 전에 자꾸 문법 오류가 났는데 너무 고통스러웠다.

게다가 몇 일 전부터 hackerrank 사이트 내 mysql 속도가 너무 느려서 답답해 미칠 지경이었다.

문법오류인데 도대체 뭐가 잘 못인지 알 수가 없었다.

 

개인공부용이다보니 레퍼런스를 안찾아본다.

특히나 아예 막히지도 않았고 갈피를 못잡지도 않았다. 제대로 한 것 같은데 오류가 나니 찾아볼 생각도 안했다.

 

혹시나 하는 마음에 Oracle로 바꿔서 했더니 되더라. 도대체 뭐가 문제일까 아시는 분 있으시면 알려주셨으면 좋겠다.