아무리 사소하더라도 배움이 없는 날은 없다.
SQL 코드카타
문제 링크
어려운 문제였다;;
예전에 풀었던 코드카타들을 참고해서 1시간이 넘게 매달린 끝에 일단 풀긴 풀었다.
주어진 숫자들의 중앙값(median)을 찾아야 하는데,
문제는 이 숫자들이 그냥 주어진 게 아니라는 것.
예를 들어 Numbers 테이블이 저렇게 주어지면,
num * frequency만큼 숫자들을 출력해서 나열하고 그 중에서 중앙값을 찾아야 한다.
즉,
0,0,0,0,0,0,0,1,2,2,2,3 에서 중앙값을 찾아야 하므로
6번째인 0과 7번째인 0의 산술평균인 0이 중앙값이 된다.
우선 num과 frequency의 조합만큼 숫자들을 출력해야 하는데,
지난 번 코드카타에서 썼던 재귀적 CTE를 만들어보기로 했다.
WITH recursive arr_num
AS
(
SELECT num,
frequency-1 AS cnt
FROM numbers
UNION ALL
SELECT num,
cnt-1
FROM arr_num
WHERE cnt > 0)먼저 numbers로부터 num, frequency-1(cnt로 명명함)을 출력한다.
(0,6)
(1,0)
(2,2)
(3,0)
이 출력될 것이다.
이제 이 cnt가 0보다 큰 경우에 대해 num과 cnt-1을 출력하여 union한다.
(1,0)
(3,0)
은 cnt가 0보다 큰 경우에 해당하지 않으므로,
최초 한 번만 출력된 후 다시 출력되지 않을 것이고
(0,5)
(2,1)
이 출력되어 합쳐질 것이다.
(0,6)
(1,0)
(2,2)
(3,0)
(0,5)
(2,1) ← 이런 식으로.
이제 재귀적 CTE에 의해 위의 과정이 계속 반복되게 되고,
최종적으로 frequency의 숫자만큼
'num'과 '1씩 줄어드는 frequency'가 출력될 것이다.
이 재귀적 CTE의 최종 실행 결과는
(0,6)
(1,0)
(2,2)
(3,0)
(0,5)
(2,1)
(0,4)
(2,0)
(0,3)
(0,2)
(0,1)
(0,0) ← 이렇게 된다. 0이 7번, 1이 1번, 2가 3번, 3이 1번 출력되었다.
다음 단계로 num과 row_number 함수를 써서 1씩 증가하는 ranking을 만들어 주었고, 이 결과를 a라는 이름의 CTE로 저장했다.
WITH recursive arr_num
AS
(
SELECT num,
frequency-1 AS cnt
FROM numbers
UNION ALL
SELECT num,
cnt-1
FROM arr_num
WHERE cnt > 0),
a
AS
(
SELECT num,
row_number() over(ORDER BY num) AS ranking
FROM arr_num
ORDER BY 1)이 때 num이 오름차순으로 정렬되어야 하므로 order by 1을 추가했다.
그리고 다음은 num이 몇 개나 출력되는지 알아야 하므로(그래야 중앙값을 구할 수 있으니) num의 갯수를 세는 b라는 이름의 CTE도 만들어 주었다.
WITH recursive arr_num
AS
(
SELECT num,
frequency-1 AS cnt
FROM numbers
UNION ALL
SELECT num,
cnt-1
FROM arr_num
WHERE cnt > 0),
a
AS
(
SELECT num,
row_number() over(ORDER BY num) AS ranking
FROM arr_num
ORDER BY 1),
b
AS
(
SELECT count(*) AS cnt_total
FROM a)이제 a와 b를 조합하면 중앙값을 구할 수 있다.
SELECT avg(num) AS median
FROM a,
b
WHERE ranking IN (floor((cnt_total+1)/2),
ceil((cnt_total+1)/2));테이블 a와 b를 cross join하고, ranking이 where조건에 해당하는 경우를 찾아서 산술평균(avg)해 주면 된다.
그저께 코드카타에서는 where절 안에 case when 구문을 집어넣어서 짝수냐, 홀수냐에 따라 랭킹을 분기했는데, 그렇게 할 필요 없이 ranking이 cnt_total(이 문제에서는 12)에 1을 더하고 2로 나눈 값(6.5)의 floor(6)와 ceil(7)에 속한다고 명시해 주어도 된다. 이렇게 하면 cnt_total, 즉 나열된 숫자의 총 수가 홀수(예컨대 13)여도 14를 2로 나눈 값의 floor와 ceil은 7로 동일하기 때문. case when을 쓰지 않아도 깔끔하게 작성할 수 있다.
아래는 완성된 풀버전 정답 쿼리.
WITH recursive arr_num
AS
(
SELECT num,
frequency-1 AS cnt
FROM numbers
UNION ALL
SELECT num,
cnt-1
FROM arr_num
WHERE cnt > 0),
a
AS
(
SELECT num,
row_number() over(ORDER BY num) AS ranking
FROM arr_num
ORDER BY 1),
b
AS
(
SELECT count(*) AS cnt_total
FROM a)
SELECT avg(num) AS median
FROM a,
b
WHERE ranking IN (floor((cnt_total+1)/2),
ceil((cnt_total+1)/2));재귀적 CTE와 row_number 함수까지 섞어 써야 하는 문제였는데,
특히나 아직 익숙하지 않은 재귀적CTE를 만드는 데 시간이 오래 걸렸다.
숫자를 반복해서 출력하거나 일정한 규칙에 의해 늘려가거나 줄여가면서 출력할 일이 분명 있을테니 앞으로도 문제에서 보일 때마다 잘 정리해 두자.
WITH a
AS (SELECT seat_id,
free,
Row_number()
OVER(
ORDER BY seat_id) AS ranking
FROM cinema
WHERE free = '1')
SELECT seat_id
FROM a
WHERE seat_id - ranking IN (SELECT seat_id - ranking
FROM a
GROUP BY 1
HAVING Count(*) >= 2);이런 풀이도 가능하다.
SELECT seat_id
FROM cinema
WHERE free = 1
AND ( seat_id - 1 IN (SELECT seat_id
FROM cinema
WHERE free = 1)
OR seat_id + 1 IN (SELECT seat_id
FROM cinema
WHERE free = 1) ); SELECT product_id,
Sum(quantity) AS "total_quantity"
FROM sales
GROUP BY 1; WITH show_question
AS
(
SELECT question_id,
count(*) AS cnt_show
FROM surveylog
WHERE action = 'show'
GROUP BY 1),
answer_question
AS
(
SELECT question_id,
count(*) AS cnt_answer
FROM surveylog
WHERE action = 'answer'
GROUP BY 1)
SELECT s.question_id AS "survey_log"
FROM show_question s
LEFT JOIN answer_question a
ON s.question_id = a.question_id
ORDER BY ifnull(cnt_answer,0)/cnt_show DESC,
1
LIMIT 1;문제 링크
이게 왜 난이도 medium이지..?
SELECT d.dept_name,
Count(s.student_id) AS "student_number"
FROM department d
LEFT JOIN student s
ON d.dept_id = s.dept_id
GROUP BY 1
ORDER BY 2 DESC,
1; 