- 7회차 QCC
문제 1번 # 좋아요를 받지 않은 페이지
1. 추출
select distinct p.page_id
from pages p
left join page_likes pl
on p.page_id = pl.page_id
where pl.page_id(혹은 pl.liked_date) is null
order by p.page_id
# 여기선 없지만 like가 여러번 눌릴 수도 있으니 pl.page_id를 사용하면 좋음.
# 안전을 위해 distinct를 사용(여기선 문제가 없었지만... 여러 번 나올 수 있으니...)
문제 2번
# 사용할 컬럼을 잘못 선정한 문제...(최고가랑 시가는 다르다! 시가==open 최고가==high)
# 증권회사에 들어가면 자주 만나게 될 문제
# 혹은 "블룸버그 터미널"에선 최신 정보를 많이 가져오며 이와 유사한 분석을 자주 시도함.
1. 사용할 컬럼 준비
select date_format(date, '%Y%m') mth, ticker, min(open)
from stock_prices
where open is not null
2. with 함수 준비
with sp as (
select date_format(date, '%Y%m') mth, ticker, open
from stock_prices
where open is not null
)
select ticker, min(open), max(opne)
from sp
group by 1
# 여기서 바로 가면 min이랑 max 값을 찾아서 그것만 그대로 사용하면 됨.
# 가장 효율적인 방법을 시작
3. 효율적인 방법
with sp as (
select date_format(date, '%Y%m') mth, ticker, open
from stock_prices
where open is not null
)
select mth, ticker, open,
rank() over(partition by ticker order by open) low_to_high_rnk,
rank() over(partition by ticker order by open desc) high_to_low_rnk
from sp
# 이제 요기서 추출(필요한 것만 가져오면 된다.)
# 방법은 조인하거나 case when을 쓰는 2가지 방법들이 있음(더 있을수도 있음)
4. 추출!(최종)
방법 1
with sp as (
select date_format(date, '%Y%m') mth, ticker, open
from stock_prices
where open is not null
), rn as (select mth, ticker, open,
rank() over(partition by ticker order by open) low_to_high_rnk,
rank() over(partition by ticker order by open desc) high_to_low_rnk
from sp)
select ticker,
max(case when high_to_low_rnk =1 then mth end) as highest_mth,
max(case when high_to_low_rnk =1 then mth end) as highest_open,
min(case when low_to_high_rnk =1 then mth end) as lowest_mth,
min(case when low_to_high_rnk =1 then mth end) as lowest_open
from rn
group by 1
# highest_mth는 1인것만 값이찍힘 나머지는 Null
# 그렇기에 max혹은 min으로 묶어주면 1개만 찍힌다!
방법 2 with로 rn 만든 것 뒤에 (최고 월, 최고 금액), (최저 월, 최저 금액)을 만들면 된다.
방법 3. sp까지만 구한 뒤 min, max open 날짜, 금액을 따로 구한 뒤 일치하는 값을 가져오는 방법이 있음.(윈도우 펑션 안 쓰는 방법)
# 하필... high로 써서... open 컬럼을 쓰면 금방 해결되는 거였는데...
문제 3번
# 윈도우 펑션 문제
# round가 아닌 floor를 통해 버림하는 문제!
# 파이썬에서도 floor ceil 하는 방식이 있음...
# 리드 함수를 활용!
1. 세션 상태에 따른 시간대 확인
select server_id, status_time as start_status_time,
lead(status_time) over(partition by server_id order by status_time) as end_status_time,
session_status as start_status,
lead(session_status) over(partition by server_id order by status_time) as end status
from server_utilization
2. 필요한 것만 가져오기
(오직 start만 있거나 stop만 있거나를 제거! + stop다음 start인 시간은 제거!)
select *
from(
select server_id, status_time as start_status_time,
lead(status_time) over(partition by server_id order by status_time) as end_status_time,
session_status as start_status,
lead(session_status) over(partition by server_id order by status_time) as end status
from server_utilization) a
where start_status='strat' and end_status='stop'
3. 이제 추출(필요한 시간차이 찾기
select floor(sum(timestampdiff(secomd, start_status_time, end_status_time)) / 86400)
from(
select server_id, status_time as start_status_time,
lead(status_time) over(partition by server_id order by status_time) as end_status_time,
session_status as start_status,
lead(session_status) over(partition by server_id order by status_time) as end status
from server_utilization) a
where start_status='start' and end_status='stop'