아무리 사소하더라도 배움이 없는 날은 없다.
SQL 문제풀이 복습
Leetcode
문제 링크
풀다 보니 자연스럽게 1트(5/11) 때의 방식으로 풀었다.
2트(6/22)를 할 때는 한창 cte를 만드는 방식을 연습하던 때라
서브쿼리로 처리한 부분을 cte로 만들었다는 것 빼고는
풀이법 자체에는 차이가 없음.
SELECT a.category,
IFNULL(cnt, 0) AS "accounts_count"
FROM (SELECT 'Low Salary' AS "category"
UNION
SELECT 'Average Salary'
UNION
SELECT 'High Salary') a
LEFT JOIN (SELECT CASE
WHEN income < 20000 THEN 'Low Salary'
WHEN income BETWEEN 20000 AND 50000 THEN
'Average Salary'
WHEN income > 50000 THEN 'High Salary'
end AS "category",
COUNT(*) AS "cnt"
FROM Accounts
GROUP BY 1) b
ON a.category = b.category;문제 링크
첫 번째로 시도한 쿼리.
쿼리 자체는 맞게 작성한 것 같고
테스트 결과도 통과였는데 정작 제출하니
Time Limit Exceeded 에러가 났다.
SELECT l1.user_id,
l2.user_id AS "recommended_id"
FROM Listens l1
JOIN Listens l2
ON l1.user_id <> l2.user_id
AND l1.song_id = l2.song_id
AND l1.day = l2.day
GROUP BY l1.user_id,
l2.user_id,
l1.day
HAVING COUNT(DISTINCT l1.song_id) >= 3
AND ( l1.user_id, l2.user_id ) NOT IN (SELECT user1_id,
user2_id
FROM Friendship)
AND ( l1.user_id, l2.user_id ) NOT IN (SELECT user2_id,
user1_id
FROM Friendship);로드가 크게 걸리는 것으로 추정되는 부분은 세 개인데,
- 셀프 조인
- Listen 테이블을 2번 조회하는 과정에서 시간 복잡도가 크게 높아짐 (user_id가 서로 다르려면 모든 경우의 수를 체크해 봐야 하므로)
- group by와 count
- 각각의 user 쌍에 대해 매일 3곡 이상 같은 노래를 들은 경우를 찾아야 하므로 쿼리 속도가 느려질 수 있음
- 서브쿼리 내 not in
- Friendship 테이블과 비교하는 부분에서 큰 오버헤드가 발생할 수 있음. 특히, not in 조건은 효율성이 낮아 대량 데이터에서는 더욱 느려지기 쉽다는 설명.
이 피드백을 반영해서 cte로 처리할 수 있는 부분은
cte로 떼어서 처리해 보기로 했다.
아래는 friendship 부분만 cte로 떼어서 정답 처리된 버전인데,
이것도 매번 제출할 때마다 시간초과가 될 때도 있고 그냥 통과될 때도 있다.
WITH friendship_all
AS (SELECT user1_id AS "a",
user2_id AS "b"
FROM Friendship
UNION ALL
SELECT user2_id,
user1_id
FROM Friendship)
SELECT user_id,
recommended_id
FROM (SELECT l1.user_id,
l2.user_id AS "recommended_id"
FROM Listens l1
JOIN Listens l2
ON l1.user_id <> l2.user_id
AND l1.song_id = l2.song_id
AND l1.day = l2.day
GROUP BY l1.user_id,
l2.user_id,
l1.day
HAVING Count(DISTINCT l1.song_id) >= 3) a
LEFT JOIN friendship_all fa
ON a.user_id = a
AND a.recommended_id = b
WHERE fa.a IS NULL
GROUP BY 1,
2;문제 링크
위의 문제와 거의 모든 풀이가 똑같은데,
이번에는 user1과 user2가 '이미 친구'여야 하므로
Friendship 테이블에 있는 user들과 inner join하면 된다.
제외 조건보다는 조금 더 간단하게 풀 수 있다.
시간초과도 안 걸렸고.
SELECT a.user1_id,
a.user2_id
FROM (SELECT l1.user_id AS "user1_id",
l2.user_id AS "user2_id"
FROM Listens l1
JOIN Listens l2
ON l1.user_id <> l2.user_id
AND l1.song_id = l2.song_id
AND l1.day = l2.day
GROUP BY l1.user_id,
l2.user_id,
l1.day
HAVING COUNT(DISTINCT l1.song_id) >= 3) a
JOIN Friendship f
ON a.user1_id = f.user1_id
AND a.user2_id = f.user2_id
GROUP BY 1,
2;프로그래머스
문제 링크
1트(4/3) 때는 이렇게 풀었는데도 정답이었다.
(오늘도 이걸 내 봤는데 여전히 정답 처리)
select product_id, product_name, product_cd, category, price
from food_product
order by price desc
limit 1사실 저 쿼리는 오답이 되어야 한다.
만약 가격이 가장 비싼 상품이 2개 이상이라면
저 쿼리로는 커버할 수 없으므로.
오늘 제출한 쿼리는 max price를 서브쿼리로 처리한 버전.
SELECT PRODUCT_ID,
PRODUCT_NAME,
PRODUCT_CD,
CATEGORY,
PRICE
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE)
FROM FOOD_PRODUCT);문제 링크
1트(4/3) 때는 ifnull을 case when으로 처리했구나
ifnull을 몰랐을 때였을 걸로 짐작..
SELECT WAREHOUSE_ID,
WAREHOUSE_NAME,
ADDRESS,
IFNULL(FREEZER_YN, 'N') AS "FREEZER"
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기%'
ORDER BY 1;문제 링크
기본적인 case when 문제.
SELECT ORDER_ID,
PRODUCT_ID,
DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS "OUT_DATE",
CASE
WHEN OUT_DATE IS NULL THEN '출고미정'
WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
end AS "출고여부"
FROM FOOD_ORDER
ORDER BY 1; SELECT FACTORY_ID,
FACTORY_NAME,
ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원%'
ORDER BY 1; 문제 링크
where절 내에 서브쿼리를 쓰는 간단한 문제.
SELECT ID,
NAME,
HOST_ID
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
FROM PLACES
GROUP BY 1
HAVING COUNT(*) >= 2)
ORDER BY 1;