아무리 사소하더라도 배움이 없는 날은 없다.
SQL 코드카타
SELECT p.firstname,
p.lastname,
a.city,
a.state
FROM person p
LEFT JOIN address a
ON p.personid = a.personid; 문제 링크
5월에 풀고 네 달 여만에 다시 만난 문제.
이 문제의 킥은 offset인데,
첫 번째 행을 건너뛴다는 의미다.
즉, salary를 내림차순으로 정렬하고 (order by ~ desc)
행 하나만 출력하되 (limit 1),
원래대로 출력했을 때 기준으로 제일 첫 행은 건너뛰게 되므로 (offset 1)
두 번째로 높은 급여가 반환되는 원리다.
아래는 완성된 정답 쿼리.
SELECT (SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 offset 1) AS "SecondHighestSalary"; 이 때, 괄호 안에 있는 서브쿼리를 CTE로 빼게 되면
문제에서 요구하는 대로 해당 값이 없을 때 null을 반환하지 못하게 된다.
예컨대 아래의 쿼리를 실행하면
WITH result
AS
(
SELECT DISTINCT salary AS "SecondHighestSalary"
FROM employee
ORDER BY salary DESC
LIMIT 1
offset 1)
SELECT *
FROM result그냥 빈 값이 출력된다.
이런 차이가 생기는 이유는 서브쿼리의 작동 방식 때문인데,
만약 정답 쿼리처럼 스칼라 서브쿼리를 사용할 경우
이 서브쿼리는 단일 값을 반환하도록 설계되어 있다.
그런데 서브쿼리가 아무 값도 반환하지 않으면
(즉, 두 번째로 높은 salary가 없는 경우라면)
SQL 표준에 따라 null이 반환된다.
반면,
두 번째처럼 CTE를 쓰게 되면 이 결과가 하나의 '테이블'처럼 취급된다.
테이블은 빈 결과 집합을 포함할 수 있으므로
아무 행도 반환하지 않지만 그렇다고 null이 뜨는 것도 아닌 셈.
그냥 '빈 행 집합'이 출력됐다고 이해하면 될 듯.
정답을 내는 것 자체가 어려운 건 아니었지만,
CTE를 써서 문제를 푸는 게 습관이 들었던 터라
왜 null이 안 뜨는지가 궁금했는데 깔끔하게 해결됐다.
문제 링크
공동 등수 이후 틈 없이 채우려면 dense_rank,
공동 등수 이후 1등, 1등, 3등 이런 식으로 틈을 두려면 그냥 rank
기억해 두자.
SELECT score,
Dense_rank()
OVER(
ORDER BY score DESC) AS "rank"
FROM scores; 문제 링크
이 문제는 5/8, 6/22에 이어 세 번째 보는 문제다.
세 번 이상 연속해서 등장하는 숫자가 뭔지를 물어보는 문제인데,
예전 두 번의 풀이에서는 모두 cross join을 써서 해결했었다.
다만 cross join을 쓰게 되면
모든 경우의 수 조합을 다 찾게 되는데,
이번 문제의 경우 숫자가 세 번 이상 연속되어야 하므로
세 개의 테이블에 대해 cross join을 수행해야 한다.
테이블 하나당 n개의 행을 갖고 있다면
join의 결과는 n의 세제곱이 되는데,
이는 비용이 매우 크게 드는 연산 방식이다.
데이터셋이 크면 클수록 성능이 떨어질 우려가 있는 셈.
(만일 10번 이상 연속된 숫자를 찾는 문제라고 한다면...?)
따라서 오늘은 cross join 대신
window함수 중 rank와 group by를 통해 해결했다.
아래는 완성된 정답 쿼리.
WITH result
AS (SELECT id,
num,
Rank()
OVER(
ORDER BY num, id) AS "cal"
FROM logs)
SELECT DISTINCT num AS "ConsecutiveNums"
FROM result
GROUP BY num,
Cast(id AS SIGNED) - Cast(cal AS SIGNED)
HAVING Count(*) >= 3;먼저 CTE에서 rank를 써서
num, id 기준 오름차순으로 cal이라는 임시 컬럼을 만들어 준다.
그리고 본 쿼리에서 num과 함께
id에서 cal을 뺀 숫자를 기준으로 그루핑하고,
그루핑된 수가 3개 이상인 num만 불러오면 된다.
이 때 주의할 점은 id와 cal을 곧바로 빼는 연산에서 오류가 생기므로
cast as signed를 써서 연산이 가능한 데이터 타입으로 변경해줘야 한다는 것.
문제 링크
6월 말쯤 처음 마주쳤던 문제.
저번에는 이렇게 풀었었다.
SELECT e1.name AS Employee
FROM employee e1
INNER JOIN employee e2
ON e1.managerid = e2.id
WHERE e1.salary > e2.salary;where절을 쓸 필요 없이
join을 할 때 한꺼번에 기준을 적용하면 된다.
(그냥 join만 쓰면 inner join이라는 의미. left로 구분될 때만 left를 쓰자)
아래는 오늘 제출한 정답 쿼리.
SELECT e1.name AS "Employee"
FROM employee e1
JOIN employee e2
ON e1.managerid = e2.id
AND e1.salary > e2.salary;오늘부터는 예전에 풀었던 Leetcode 문제를 복기하면서
스프레드시트에도 데이터베이스를 정리하는 시간을 가질 예정이다.
SQL 공부는 이렇게만 진행하고
판다스/파이썬/통계학/머신러닝에도 계속 시간을 투입해야지.
