아무리 사소하더라도 배움이 없는 날은 없다.
예전에 풀었던 방법
세 번 연속되는 숫자를 구하기 위해
똑같은 테이블을 3번 cross join한 후 where 조건문으로 해결했다.
SELECT
DISTINCT logs1.num AS ConsecutiveNums
FROM
logs AS logs1
CROSS JOIN logs AS logs2
CROSS JOIN logs AS logs3
WHERE
logs1.id + 1 = logs2.id AND logs1.num = logs2.num
AND logs2.id + 1 = logs3.id AND logs2.num = logs3.num오늘 풀었던 방법
WITH result
AS (SELECT id,
num,
Lag(num, 1)
OVER(
ORDER BY id) AS second_num,
Lag(num, 2)
OVER(
ORDER BY id) AS third_num,
Lag(id, 1)
OVER(
ORDER BY id) AS second_id,
Lag(id, 2)
OVER(
ORDER BY id) AS third_id
FROM logs)
SELECT DISTINCT num AS ConsecutiveNums
FROM result
WHERE num = second_num
AND second_num = third_num
AND id = second_id + 1
AND second_id = third_id + 1 문제에서는 데이터 수가 얼마 없어서 크게 상관은 없었다지만,
데이터 수가 많은 상황에서 예전에 풀었던 방법처럼 cross join을 두 번 수행하면 결과가 엄청나게 커질 수 있고, 연산 비용이 늘어날 수 있는 문제가 있다. (cross join을 두 번 수행하면 시간복잡도는 O(n^3)이다)
따라서, 이번에는 CTE와 윈도우 함수(lag)을 써서 result 테이블을 미리 만들어 두고 필요한 데이터를 result에서 뽑아오는 방식을 택했다. (join을 수행하지 않고 하나의 테이블을 여러 번 스캔하므로, 시간복잡도는 O(n)이다)
어떤 수가 세 번 연속되려면 연속되는 동안 id가 1씩 증가해야 하고, 동시에 숫자 3개가 동일해야 한다. lag함수를 써서 num과 id를 각각 한 칸, 두 칸씩 밀어둔 다음 본 쿼리에서 where절을 써서 num = second_num = third_num 조건과 id = second_id+1 = third_id+2 의 조건을 걸어주었다. 오늘 풀었던 방법이 복잡도 면에서 더 나은 쿼리라는 점에서 CTE를 적절하게 활용한 예라고 할 수 있겠다.
예전에 풀었던 방법
두 결과 테이블을 따로 만들어서 union을 해 준 방식.
저 쿼리만 놓고 보면 정확히 뭘 구하려는 건지 알아보기 어렵기도 한 듯.
SELECT DISTINCT product_id,
10 AS price
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id
FROM products
WHERE change_date <= '2019-08-16')
UNION
SELECT product_id,
new_price AS price
FROM products
WHERE ( product_id, change_date ) IN (SELECT product_id,
Max(change_date) AS date
FROM products
WHERE change_date <= '2019-08-16'
GROUP BY product_id) 오늘 풀었던 방법
WITH before_0816
AS (SELECT product_id,
Max(change_date) AS date
FROM products
WHERE change_date <= '2019-08-16'
GROUP BY 1),
after_0816
AS (SELECT product_id,
Min(change_date) AS date
FROM products
WHERE product_id NOT IN (SELECT product_id
FROM products
WHERE change_date <= '2019-08-16')
AND change_date > '2019-08-16'
GROUP BY 1)
SELECT b.product_id,
p.new_price AS price
FROM before_0816 b
LEFT JOIN products p
ON b.product_id = p.product_id
AND b.date = p.change_date
UNION
SELECT a.product_id,
10 AS price
FROM after_0816 a
LEFT JOIN products p
ON a.product_id = p.product_id
AND a.date = p.change_date; 오늘 풀이에서는 CTE를 사용하여 8/16 이전과 이후의 결과를 분리하고,
본 쿼리에서는 두 개 테이블 각각에 join을 수행한 뒤 결과를 union해서 해결했다.
먼저 before_0816에서는 2019년 8월 16일 이전을 기준으로 product_id별 가장 큰 날짜를 구했다. 그 전에 몇 번 가격을 변경한 이력이 있더라도, 결국 8/16 기준으로 마지막 날짜의 가격이 기준가격이 될 것이기 때문에.
그리고 after_0816에서는 2019년 8월 16일 이후를 기준으로 product_id별 가장 작은 날짜를 구하되, 이미 before_0816에서 변경 이력이 찍힌 id는 제외해야 하므로 where절에서 해당 id를 제외하는 조건을 주었다.
마지막으로 본 쿼리에서는 before_0816과 after_0816에서 각각 id를 불러온 후, before_0816에서는 products 테이블과 id와 날짜를 기준으로 join하고 products 테이블의 가격을 불러왔고, after_0816에서는 join까지는 똑같이 수행했지만 가격을 10으로 고정했다. after_0816 테이블에서 등장한 product_id는 before_0816에 등장한 적이 없는 product_id이고, 그 말인즉 한 번도 가격 변경 이력이 없는 상품이기 때문에 문제에서 주어진 조건대로 가격을 10으로 고정한 것.
그리고 이 두 연산 결과를 join하면 정답이 된다.
시간복잡도는 예전과 오늘 풀이 모두 O(nlogn)으로 동일하지만, CTE를 활용한 오늘의 풀이 쪽이 조금 더 다양한 조건 하에서 최적화할 수 있는 여지가 많다.
오늘 풀었던 방법
WITH total_weight
AS
(
SELECT person_name,
turn,
sum(weight) over(ORDER BY turn) AS total_weight
FROM queue)
SELECT person_name
FROM total_weight
WHERE total_weight <= 1000
ORDER BY total_weight DESC
LIMIT 1sum over를 써서 누적 몸무게를 구한 결과를 CTE에 담고,
본 쿼리에서는 그 누적 몸무게가 1,000 이하인 사람들만 불러온 다음
total_weight 기준으로 내림차순 정렬한 후 1개의 결과만 불러왔다.
이 사람이 누적 몸무게가 1,000을 찍는 시점까지, 혹은 그 직전 무게가 되는 시점까지 탑승할 수 있는 마지막 사람이 된다.
The result table must contain all three categories.
If there are no accounts in a category, return 0.조건에 따라 Low, Average, High salary를 빈 테이블로 만들어 두고
해당하는 경우를 left join으로 처리해서 풀었던 문제.
SELECT a.category,
Ifnull(count, 0) AS accounts_count
FROM (SELECT 'Low Salary' AS category
UNION
SELECT 'Average Salary' AS category
UNION
SELECT 'High Salary' AS category) 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(account_id) AS count
FROM accounts
GROUP BY 1) b
ON a.category = b.category;오늘 풀었던 방법
WITH a
AS (SELECT 'Low Salary' AS category
UNION ALL
SELECT 'Average Salary' AS category
UNION ALL
SELECT 'High Salary' AS category),
cnt
AS (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 count
FROM accounts
GROUP BY category)
SELECT a.category,
COALESCE(cnt.count, 0) AS accounts_count
FROM a
LEFT JOIN cnt
ON a.category = cnt.category;예전에 풀었을 때 서브쿼리로 처리했던 부분(빈 껍데기의 low, average, high salary 테이블을 만들어둔 것)과, case when 구문에 따라 각각의 income을 구간별로 나눠준 것을 모두 CTE로 처리했다.
본 쿼리에서는 빈 껍데기(a 테이블)와 cnt 테이블을 join해서 cnt.count를 불러와 주되, 0이 나오는 경우도 있을 수 있으므로 cnt.count를 COALESCE 함수로 감쌌다.
어떤 방식으로 처리하든 결과가 달라지지는 않지만, (시간복잡도도 동일함)
오늘 풀었던 것처럼 CTE를 활용해서 푸는 방식이 쿼리의 가독성 측면에서는 조금 더 유리한 부분이 있다고 한다.
