SQL
카타
문제 110. 1164. Product Price at a Given Date : where, union, distint 활용
LeetCode - The World's Leading Online Programming Learning Platform
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
leetcode.com
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
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
leetcode.com
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절의 특성도 쓰자면,
- 쿼리 내 중복되는 부분 줄임
- 중간 결과를 생성해 쿼리 작성의 효율을 높임
- 위의 이유들로 가독성을 높임
파이썬
1. 개인과제
## 데이터 입력
log_date = (너무 길어서 생략)
push_count = (너무 길어서 생략)
df = pd.DataFrame({'log_date':log_date, 'push_count':push_count})
display(df.head())
#Question 1: 날짜 전처리
## 날짜를 변환
format = "%y-%m-%d"
df.log_date = pd.to_datetime(df['log_date'], format = format
## 변환된 날짜 칼럼으로부터 요일dayofweek을 숫자로 추출
df = df.assign(day_of_week = df['log_date'].dt.weekday)
#Question 2: Groupby로 통계량 집계
## 요일별 푸시 횟수의 평균과 중간값 추출
push_count_by_dow = df.groupby('day_of_week')['push_count'].agg({'mean', 'median'}) ##
push_count_by_dow = push_count_by_dow.sort_index()
display(push_count_by_dow)
#Question 3: Bar chart 시각화
## 요일별 평균을 bar chart로 시각화
push_count_by_dow["mean"].plot.bar(x = range(0, 7, 1), y = 'mean') ##
plt.show()
[assign 매서드]
- 기존의 DataFrame 안의 칼럼을 활용해서 새로운 칼럼을 생성
- 본 문제의 경우, 기존의 log_date 칼럼의 날짜를 활용해서 요일을 숫자로 추출한 뒤(dt.weekday), 이를 새 칼럼으로 생성함.
log_date = (너무 길어서 생략)
push_count = (너무 길어서 생략)
df = pd.DataFrame({'log_date':log_date, 'push_count':push_count}) #1 : assign 사용 전
df = df.assign(day_of_week = df['log_date'].dt.weekday) #2 : assign 사용 후
[groupby 및 agg매서드]
*agg() 매서드 :
- 다중집계작업(multiple aggregation operation)을 간결하게 나타낼 수 있게 함
- DataFrame에서 여러 집계 매서드(sum(), mean(), max())를 한 번에 사용할 수 있음
- groupby 매서드와 어떻게 조합하느냐에 따라 결과가 사뭇 달라질 수 있음
agg 매서드 자체는 이렇게 정의할 수 있는데 여기서 정리하고자 하는 것은 3번. 즉, groupby와 같이 쓸 땐 구체적으로 어떻게 코드를 작성하느냐에 따라 결과가 사뭇 달라진다는 점이다.
#1번 방식
push_count_by_dow = df.groupby('day_of_week')['push_count'].agg({'mean', 'median'})
push_count_by_dow = push_count_by_dow.sort_index()
display(push_count_by_dow)
#2번 방식
push_count_by_dow = df.groupby('day_of_week').agg({'push_count' : ['mean', 'median']})
push_count_by_dow = push_count_by_dow.sort_index()
display(push_count_by_dow)1번 방식의 경우 아래 이미지에서 push_count부분이 없이 출력되는데, 그 결과 그대로 info() 매서드를 사용하면 칼럼명도 mean, median 두 개가 있다고 표시된다. 하지만 두 번째 경우는 그 결과값이 아래처럼 출력이 되는데, 처음에 그대로 다음 작업을 하려다가 에러가 계속 뜨길래 info()로 확인을 해봤더니 각 칼럼명이 'push_count, mean', 'push_count, median'으로 표시가 됐다.
앞으로 공부하고 작업하면서 한번씩 조심할 필요가 있을듯!
