1. WHERE, UNION, DISTINCT, 2. WITH, 윈도우함수

카타

문제 110. 1164. Product Price at a Given Date  :   where, union, distint 활용

LeetCode - The World's Leading Online Programming Learning Platform

Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

Return the result table in any order.

The result format is in the following example.

 

Input
Products =
| product_id | new_price | change_date |
| ---------- | --------- | ----------- |
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |

 

 

 

오답 1

select product_id,
       new_price,
       change_date
from products
where change_date in (select max(change_date)
                      from products
                      where change_date <= '2019-08-16'
                      group by product_id)

 

 

Output
| product_id | new_price | change_date |
| ---------- | --------- | ----------- |
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 35        | 2019-08-16  |
Expected
| product_id | price |
| ---------- | ----- |
| 1          | 35    |
| 2          | 50    |
| 3          | 10    |

 

 

오답 2

select product_id,
       new_price as price
from products
where (product_id, change_date) in 
                     (select product_id, max(change_date)
                      from products
                      where change_date <= '2019-08-16'
                      group by product_id)
union all
select product_id,
       10 as pric
from products
where (product_id, change_date) not in 
                     (select product_id, change_date
                      from products
                      where change_date <= '2019-08-16'
                      group by product_id)

 

Output
| product_id | price |
| ---------- | ----- |
| 2          | 50    |
| 1          | 35    |
| 2          | 10    |
| 3          | 10    |

 

union의 윗 부분은 어쩌어찌 잘 나왔는데, 아랫부분에서 'where ~ not in (서브쿼리)' 부분에서 2가 포함될 수 있다는 것을 간과해서 실패. 

 

 

최종 정답

select product_id,
       new_price as price
from products
where (product_id, change_date) in 
                     (select product_id, max(change_date)
                      from products
                      where change_date <= '2019-08-16'
                      group by product_id)
union
select product_id,
       10 as price
from products
where product_id not in (select distinct product_id
                        from Products
                        where change_date <='2019-08-16')

 

 

 

여기서 포인트는 distinct 함수

select distinct product_id
from Products
where change_date <='2019-08-16'

| product_id |
| ---------- |
| 1          |
| 2          |

group by로 필터링할 생각만 했기 때문에 이 부분이 제일 어려웠고 헤맸던 부분인데, distinct로 간단하게 해결 할 수 있는 문제였다. 즉, 각 product_id가 한 번이라도 8/16 전에 언급되기만 하면 위 쿼리로 그 결과값을 출력해낼 수 있으므로, 

 

포인트

• 중복되는 유닛 단위로 필터링 해야한다고 해서 그룹화가 무조건 답은 아님
• 그룹화를 통한 필터링이 막힐 때에는 distinct를 사용할 것

 

 

문제111. 1204. Last Person to Fit in the Bus : with, 윈도우 함수(sum)

LeetCode - The World's Leading Online Programming Learning Platform

 

There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.

Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.

The result format is in the following example.

 

정답

with cte as (
select person_name,
       sum(weight) over (order by turn) as total_weight
from queue
order by turn )

select person_name
from cte
where total_weight <=1000
order by total_weight desc
limit 1;

문제 자체는 이전 문제들에 비해 무척 쉬웠지만(문제 예시에서 대놓고 윈도우 함수를 쓰라고 알려줄 정도ㅋㅋ)

with를 직접 사용한게 기억하기로 거의 처음이라서 일부러 따로 기록. 

기록한 김에 with절의 특성도 쓰자면,

• 쿼리 내 중복되는 부분 줄임
• 중간 결과를 생성해 쿼리 작성의 효율을 높임
• 위의 이유들로 가독성을 높임