[MySQL] 3주차 (for coding test)

 

https://leetcode.com/problems/trips-and-users/

Trips and Users - LeetCode

a

https://leetcode.com/problems/second-highest-salary/

Second Highest Salary - LeetCode

[from절 서브쿼리, dense_rank, min() 사용]

SELECT MIN(salary) as SecondHighestSalary
FROM(
        SELECT id, salary
               ,DENSE_RANK() OVER(ORDER BY salary DESC)AS 'd_rank' 
        FROM Employee
    )AS t
WHERE d_rank = 2;

여기에서 MIN 함수를 사용한 이유는 d_rank = 2인 row가 없을때 null 처리를 해주기 위해서이다. 

아래는 보다 직관적인 풀이 방식으로 구현한 것이다. 

[where절 서브쿼리, max() 사용]

SELECT MAX(salary) as SecondHighestSalary
FROM Employee 
WHERE salary < ( SELECT MAX(salary) FROM Employee )

 

https://leetcode.com/problems/department-top-three-salaries/

Department Top Three Salaries - LeetCode

WITH E AS(
    SELECT id, name, salary, departmentId
           ,DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) as r
    FROM Employee
)
SELECT D.name as Department
       ,E.name as Employee
       ,E.salary as Salary
FROM E INNER JOIN Department D ON E.departmentId = D.id
WHERE E.r <= 3

 

 

https://www.hackerrank.com/challenges/symmetric-pairs/problem?isFullScreen=true 

Symmetric Pairs | HackerRank

ㅁ

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true 

Interviews | HackerRank

ㅁ

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true 

15 Days of Learning SQL | HackerRank

겁나 어려워서 못풀었다..... 흑흑

ㅁ

 

 

https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true 

Contest Leaderboard | HackerRank

[from절 서브쿼리, join 사용] ★

이렇게 조인과 집계함수를 동시에 사용하는 문제가 코딩테스트에서 자주 출제된다. 꼭 한 번 다시 풀어볼 것!

SELECT S.hacker_id, H.name, SUM(S.max_score) as total
FROM (
       SELECT hacker_id, MAX(score) as max_score
       FROM Submissions
       GROUP BY hacker_id, challenge_id
     )S INNER JOIN Hackers H ON S.hacker_id = H.hacker_id 
GROUP BY S.hacker_id, H.name 
HAVING SUM(S.max_score) > 0
ORDER BY total DESC, H.hacker_id

select 문의 동작순서는 from > where > group by > having > select > order by 순으로 진행된다. 

따라서 select절에서 사용한 alias는 원래 group by와 having절에서 사용하지 못하는 것이 원칙이다. 하지만 dbms 내부적으로 처리해주기 때문에 select 절에서 사용한 alias를 group by, having, order by 절에서 사용할 수 있다.  (mysql 기준)

 

단, where 절에서는 select 절에서 언급한 컬럼명만 접근할 수 있다. 즉 다시말해 select 절에서 사용한 alias를 where 절에서는 사용할 수없다. 실제 컬럼명으로만 접근 가능하다.

SELECT S.hacker_id, H.name, SUM(S.max_score) as total
FROM (
       SELECT hacker_id, MAX(score) as max_score
       FROM Submissions
       GROUP BY hacker_id, challenge_id
     )S INNER JOIN Hackers H ON S.hacker_id = H.hacker_id 
GROUP BY S.hacker_id, H.name 
-- having 절에서 select 절의 alias 사용
HAVING total > 0
ORDER BY total DESC, H.hacker_id

 

 

ex)

SELECT HOUR(DATETIME) as 'c1'

         , count(*) as 'COUNT'

FROM ANIMAL_OUTS

WHERE HOUR(DATETIME)>=9 AND HOUR(DATETIME)<=19

GROUP BY c1

ORDER BY c1;

 

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true 

Ollivander's Inventory | HackerRank

[where절 서브쿼리, 조인] ★★

SELECT W.id, P.age, W.coins_needed, W.power
FROM Wands W
 INNER JOIN Wands_Property P ON W.code = P.code
WHERE P.is_evil = 0
AND W.coins_needed = (SELECT MIN(W1.coins_needed)
                      FROM Wands W1
                       INNER JOIN Wands_Property P1 ON W1.code = P1.code
                      WHERE P1.is_evil = 0 
                      AND W1.power = W.power
                      AND P1.age = P.age)
ORDER BY W.power DESC, P.age DESC

이 문제는 풀지 못했다. 그 이유를 쿼리 결과를 확인하며 이해할 수 있었다.

 

Discussion에 가보니, 나와 비슷하게 쿼리를 작성하여 틀린 사람들이 제법 많아보였다. 그래서 틀린 이유를 한번 자세히 파봐야겠다고 생각했다.

틀린 풀이)

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

나는 이런식으로 GROUP BY 절에서 age와 power를 묶어 (age, power)쌍이 같다면 최소 MIN(coins_needed)만 출력되게끔 쿼리를 작성하였다. 여기서 문제는 MySQL 특성상 SELECT 절에 적힌 컬럼은 모두 GROUP BY절에 포함되어야 한다.

그렇지 않으면

ERROR 1055 (42000) at line 1: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_mzgsuvebnwq.W.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

이러한 에러를 맞닥뜨릴 것이다. 따라서 W.id를 GROUP BY절에 추가해주어야 하는데, 그렇게 될 경우 다음과 같은 문제가 발생한다.

정답인 코드를 돌려보면 위 사진과 같은 결과가 출력된다. 코드가 틀린 원인을 이해하기 위해 가져왔다. 

 

GROUP BY 절에서 age, power, id 세 컬럼을 적어준다면

age : 496 power : 10 일 때, min(coins_needed) = 4789이므로 (아래 사진 참고)

1303 496 6678 10

1303 496 4789 10

두 행에 대하여

1303 496 4789 10인 행만 출력되어야 한다. 하지만 

우리는 좀전에 (age, power, id) 세컬럼으로 그룹화를 진행했다. 따라서 두 행은 서로 고유의 min(coins_needed)으로 인식되고 두 행이 동시에 결과로 출력되는 대참사가 벌어지게 된다. 

 

[code, age로 그룹화한 다른 풀이]

위에서는 power와 age를 이용하여 min(coins_needed)을 구했지만 문제에서 주어진

이 개념을 활용하면 code와 age를 가지고도 min(coins_needed)를 구할 수 있다.

SELECT W.ID, WP.AGE, M.coins_needed, W.power 
FROM (SELECT code
            ,min(coins_needed) AS coins_needed
            ,power 
      FROM wands 
      GROUP BY code, power
     ) AS M 
INNER JOIN Wands AS W ON W.code = M.code AND W.power = M.power AND W.coins_needed = M.coins_needed 
INNER JOIN Wands_Property AS WP ON WP.code = W.code WHERE WP.is_evil = 0 
ORDER BY W.power DESC, WP.age DESC