아무리 사소하더라도 배움이 없는 날은 없다.
SQL 코드카타
WITH result
AS
(
SELECT sqrt(power((p1.x-p2.x),2)+power((p1.y-p2.y),2)) AS "distance"
FROM Point2D p1,
Point2D p2)
SELECT round(distance,2) AS "shortest"
FROM result
WHERE distance <> 0
ORDER BY 1 ASC
LIMIT 1;두 점 사이의 최소 유클리드 거리를 묻는 문제.
우선 Point2D 테이블을 스스로에 대해 cross join해서 모든 경우의 수에 대한 유클리드 거리(distance)를 구하고 그 결과를 result라는 이름의 CTE에 저장한다.
그리고 본 쿼리에서는 result에서 가장 작은 거리를 불러오기 위해
order by와 limit를 조합해서 distance를 출력하되,
distance가 0인 경우는 cross join의 결과 같은 점끼리 매칭된 결과이므로
where 절을 써서 0인 경우는 제외한다.
문제 링크
leetcode에서 난이도 hard쯤 되면
재귀적 CTE와 window함수의 조합이 거의 필수인 것 같다..
이 문제에서는 각id별, 월별로 최근 3개월의 salary합을 구해야 하는데,
(각 id별로 가장 최근 월들은 제외하고) 문제는 중간중간 빠진 월들이 있다는 것이다.
예를 들어, id 1번의 경우 7월을 기준으로 한다면 7,6,5월의 salary가 더해져야 하는데 6,5월이 없으므로 90이 된다. 4월을 기준으로는 4,3,2월의 데이터가 모두 있으므로 60+40+30 = 130이 나오는 것.
따라서 이런 방식으로 쿼리를 짜면
SELECT id,
month,
salary,
SUM(salary)
over (
PARTITION BY id
ORDER BY month ROWS BETWEEN 2 preceding AND CURRENT ROW) AS "sum"
FROM employee
ORDER BY id,
month DESC;
결과가 저렇게 나오게 된다. 무조건 최근 2개 행과 현재 행을 더해버리게 되므로.
이 문제를 해결하려면
- 각 id별로 month의 최소값과 최대값을 구해서
- 그 사이에 빠진 월들을 채워넣은 후 (id 1번이라면 5월, 6월)
- 빠진 월들의 salary를 0으로 처리하고 위의 작업을 수행하면 될 것 같다.
먼저 각 id별로 month의 최소값과 최대값을 구한다.
그 결과는 idmonth라는 CTE에 저장한다.
나중에 재귀적CTE를 만들기 위해 이 테이블에 RECURSIVE 키워드를 붙였다.
MySQL에서 RECURSIVE CTE를 만들기 위해서는 첫 번째 CTE에 키워드를 미리 붙여줘야 한다는 것도 이 문제를 풀면서 처음 알았다. 이 CTE 자체에는 재귀적 문법이 쓰이지 않았지만 선언만 미리 해 둔다는 개념인 듯.
WITH recursive idmonth
AS
(
SELECT id,
min(month) AS minmonth,
max(month) AS maxmonth
FROM employee
GROUP BY id ),그리고 진짜 재귀적CTE를 써서, minmonth와 maxmonth사이의 빠진 월들을 다 채워준다.
이 때 먼저 만들었던 idmonth와 join해서 id는 같되 recursivemonth의 month는 idmonth에서 확인한 maxmonth보다는 작다는 조건을 추가한다.
WITH recursive idmonth
AS
(
SELECT id,
min(month) AS minmonth,
max(month) AS maxmonth
FROM employee
GROUP BY id ),
rm
AS
(
SELECT id,
minmonth AS month
FROM idmonth
UNION ALL
SELECT rm.id,
rm.month + 1
FROM rm
JOIN idmonth im
ON rm.id = im.id
AND rm.month < im.maxmonth )다음으로 이 recursivemonth에 문제에서 받은 employee 테이블을 left join해서, employee 테이블에서 빠진 월의 salary는 0으로 대체한다.
WITH recursive idmonth
AS
(
SELECT id,
min(month) AS minmonth,
max(month) AS maxmonth
FROM employee
GROUP BY id ),
rm
AS
(
SELECT id,
minmonth AS month
FROM idmonth
UNION ALL
SELECT rm.id,
rm.month + 1
FROM rm
JOIN idmonth im
ON rm.id = im.id
AND rm.month < im.maxmonth )
SELECT rm.id,
rm.month,
coalesce(e.salary, 0) AS salary
FROM rm
LEFT JOIN employee e
ON rm.id = e.id
AND rm.month = e.month여기까지 작성하고 출력한 결과는 아래와 같다.
id 1번의 케이스에서 빠졌던 5월, 6월 데이터가 0으로 추가되었다!
이제 이 결과까지 CTE로 묶어주고 맨 처음과 같은 방식으로 본 쿼리를 만들어 볼 수 있겠다.
WITH recursive idmonth
AS
(
SELECT id,
min(month) AS minmonth,
max(month) AS maxmonth
FROM employee
GROUP BY id ),
rm
AS
(
SELECT id,
minmonth AS month
FROM idmonth
UNION ALL
SELECT rm.id,
rm.month + 1
FROM rm
JOIN idmonth im
ON rm.id = im.id
AND rm.month < im.maxmonth ),
result
AS
(
SELECT rm.id,
rm.month,
coalesce(e.salary, 0) AS salary
FROM rm
LEFT JOIN employee e
ON rm.id = e.id
AND rm.month = e.month),
final_result
AS
(
SELECT id,
month,
salary,
sum(salary) over ( partition BY id ORDER BY month rows BETWEEN 2 preceding AND current row) AS "sum"
FROM result
ORDER BY id,
month DESC)
SELECT id,
month,
sum AS "Salary"
FROM final_result fr
WHERE salary <> 0salary가 0일 경우(id 1번의 5,6월)는 결과 출력에서 제외되어야 하므로
final_result CTE를 한 번 더 만들어서 where 조건을 추가했다.
결과는 아래와 같이 나온다.
이제 각 id별로 최고 월을 제외시켜야 하므로
id별 최고 월을 출력한 temp CTE까지 만들어서 inner join으로 최고 월을 제외시켰다. 완성된 정답 쿼리는 아래와 같다.
WITH recursive idmonth
AS
(
SELECT id,
min(month) AS minmonth,
max(month) AS maxmonth
FROM employee
GROUP BY id ),
rm
AS
(
SELECT id,
minmonth AS month
FROM idmonth
UNION ALL
SELECT rm.id,
rm.month + 1
FROM rm
JOIN idmonth im
ON rm.id = im.id
AND rm.month < im.maxmonth ),
result
AS
(
SELECT rm.id,
rm.month,
coalesce(e.salary, 0) AS salary
FROM rm
LEFT JOIN employee e
ON rm.id = e.id
AND rm.month = e.month),
final_result
AS
(
SELECT id,
month,
salary,
sum(salary) over ( partition BY id ORDER BY month rows BETWEEN 2 preceding AND current row) AS "sum"
FROM result
ORDER BY id,
month DESC),
temp
AS
(
SELECT id,
max(month) AS "max"
FROM employee
GROUP BY 1)
SELECT fr.id,
fr.month,
sum AS "Salary"
FROM final_result fr
INNER JOIN temp
ON fr.id = temp.id
AND fr.month <> temp.max
WHERE salary <> 0
ORDER BY 1,
2 DESC;무려 5개의 CTE를 만들어가며 🥳병을 떨었지만 어쨌든 답이 나오긴 나왔다ㅠㅜ
1시간 30분 정도가 걸렸으니 이런 문제가 코딩테스트에 나오면 광탈하겠네?^^
일단은 해결했다는 데 의의를 두...어도 되나ㅠㅠ
WITH result
AS (SELECT project_id,
Count(*) AS cnt
FROM project
GROUP BY 1)
SELECT project_id
FROM result
WHERE cnt IN (SELECT Max(cnt)
FROM result);WITH follower
AS (SELECT followee,
Count(*) AS cnt_follower
FROM follow
GROUP BY 1),
followee
AS (SELECT follower,
Count(*) AS cnt_followee
FROM follow
GROUP BY 1)
SELECT f2.follower,
cnt_follower AS "num"
FROM follower f1
INNER JOIN followee f2
ON f1.followee = f2.follower
ORDER BY 1;WITH result
AS (SELECT seller_id,
Sum(price) AS total
FROM sales
GROUP BY 1)
SELECT seller_id
FROM result
WHERE total = (SELECT Max(total)
FROM result);