게임 평점 예측하기1
▶ 문제 POINT : 누락 정보 평균 값으로 채우기
[문제]
평점 정보에 일부 누락이 있음을 알게된 당신은 같은 장르를 가진 게임들의 평균 평점과 평균 평론가/사용자 수를 활용해 누락된 값을 채우려고 합니다. 2015년 이후에 발매한 게임 중 누락된 평점 정보가 있는 게임에 대해서 같은 장르를 가진 전체 게임의 평균 평점과 평균 평론가/사용자 수를 사용해 누락된 정보를 채우는 쿼리를 작성해주세요. 쿼리 결과에는 누락된 평점 정보가 있는 게임만 포함되어야 하며, 아래 6개 컬럼이 있어야 합니다. 또한, 평론가와 사용자 평점 평균은 소수점 아래 넷째 자리에서 반올림 해 셋째 자리까지 출력되어야 하고, 사용자 수는 올림하여 자연수로 출력되어야 합니다.
[조건]
- 장르별 평균 지표(평균 평점/평균 평론가 수/평균 사용자 점수/평균 사용자 수) 구하고
- 특정 년도에 누락된 값 평균값으로 대체
- 평균 지표들 출력 양식 맞추기(반올림/ 올림)
▶ 문제 해결 & 피드백 POINT : 평점 정보 컬럼 4개 중 하나라도 누락된 경우(null) 평균값으로 대체
[풀이 과정1]
1. 서브쿼리로 장르별 평균 값 구하기 : `group by` genre_id & 평균 값 집계
2. 아우터 쿼리
- 서브쿼리 이너 조인 후 where 절에서 평점 정보가 null 일때만 가져오기
- 2015년 이상 일때만 필터링
- select 절 if 조건으로 null 이면 평균 값, 아니면 기존값으로 조건별 값 대체
- 평균 평점 출력 양식 맞추기 (반올림 : `round` 올림 : `ceil`)
with avgg AS( # 장르별 평균 값
SELECT genre_id
,avg(critic_score) as critic_score_avg
,avg(critic_count) as critic_count_avg
,avg(user_score) as user_score_avg
,avg(user_count) as user_count_avg
FROM games
group by genre_id
)
SELECT g.game_id
,g.name
,ROUND(IF(g.critic_score IS NULL,a.critic_score_avg,g.critic_score),3) AS critic_score
,CEIL(IF(g.critic_count IS NULL,a.critic_count_avg,g.critic_count)) AS critic_count
,ROUND(IF(g.user_score IS NULL,a.user_score_avg,g.user_score),3) AS user_score
,CEIL(IF(g.user_count IS NULL,a.user_count_avg,g.user_count)) AS user_count
FROM games g
INNER JOIN avgg a ON g.genre_id=a.genre_id
WHERE g.year>=2015
AND (g.critic_score is NULL OR g.critic_count IS NULL OR g.user_score IS NULL OR g.user_count IS NULL)
[풀이 과정2]
1. 아우터쿼리 `COALESCE` 함수 사용한 풀이
- 서브쿼리 이너 조인 후 where 절에서 평점 정보가 null 일때만 가져오기
- 2015년 이상 일때만 필터링
- select 절 COALESCE 함수 사용하여 NULL 값인 경우 평균값으로 대체 : `COALESCE(g.critic_score,a.critic_score_avg)`
with avgg AS(
SELECT genre_id
,avg(critic_score) as critic_score_avg
,avg(critic_count) as critic_count_avg
,avg(user_score) as user_score_avg
,avg(user_count) as user_count_avg
FROM games
group by genre_id
)
SELECT g.game_id
,g.name
,ROUND(COALESCE(g.critic_score,a.critic_score_avg),3) AS critic_score
,CEIL(COALESCE(g.critic_count,a.critic_count_avg)) AS critic_count
,ROUND(COALESCE(g.user_score,a.user_score_avg),3) AS user_score
,CEIL(COALESCE(g.user_count,a.user_count_avg)) AS user_count
FROM games g
INNER JOIN avgg a ON g.genre_id=a.genre_id AND (g.critic_score is NULL OR g.critic_count IS NULL OR g.user_score IS NULL OR g.user_count IS NULL)
WHERE g.year>=2015
▶ REMIND
- `Coalesce` 함수
- null 값 대체 할 때 사용하는 함수
----- 파라미터로 값을 받을 때
coalesce(null,null,3) #3출력
----- 로직: 첫번째 값 null ->두번째 값 null-> 세번째 값 3 출력
----- 파라미터로 컬럼을 받을 때
coalesce(컬럼1,컬럼2,컬럼3)
----- 로직: 컬럼1 null 이면 컬럼2 값으로 대체/ 컬럼2도 null 이면 컬럼3 값으로 대체