게임 평점 예측하기 1 : solvesql

평론가 평점, 사용자 평점이 null 값일 때, 레코드의 결측치를 같은 장르 게임의 평론가 평점의 평균, 사용자 평점의 평균으로 채우는 것이 문제의 요구사항이다.

장르 id로 그룹화를 한다고 했을 때, 그 게임의 id와 같은 값을 어떻게 표현해야 할까?

1. 한 장르 별로 평론가 및 사용자 평점의 평균을 구한 다음에 결측치에다가 값을 넣는다.

select  genre_id, 
        ROUND(AVG(critic_score), 3) as cs, 
        ROUND(AVG(critic_count)) as cc,
        ROUND(avg(user_score), 3) as us,
        ROUND(avg(user_count)) as uc
from    games
GROUP by
        genre_id

CTE로 쓰고

-- 같은 장르를 가진 전체 게임 평균 평점, 평균 평론가/사용자 수
with cte as (select  genre_id, 
        ROUND(AVG(critic_score), 3) as cs, 
        ROUND(AVG(critic_count)) as cc,
        ROUND(avg(user_score), 3) as us,
        ROUND(avg(user_count)) as uc
from    games
GROUP by
        genre_id)
-- 둘 중 하나만 누락인 게임이 있나?
SELECT  g.game_id, g.name,
        CASE WHEN g.critic_score IS NULL THEN c.cs ELSE g.critic_score END as critic_score,
        CASE WHEN g.critic_score IS NULL THEN c.cc ELSE g.critic_count END as critic_count,
        CASE WHEN g.user_score IS NULL THEN c.us ELSE g.user_score END as user_score,
        CASE WHEN g.user_score IS NULL THEN c.uc ELSE g.user_count END as user_count
FROM    games g INNER JOIN cte c
        ON g.genre_id = c.genre_id
WHERE   (g.critic_score is null OR g.user_score is NULL) AND
        year >= 2015

count는 자연수로 올림을 해줘야 하므로 ROUND 함수가 아니라 CEIL 올림 함수를 사용해줘야 한다.

answer

with cte as (select  genre_id, 
        ROUND(AVG(critic_score), 3) as cs, 
        CEIL(AVG(critic_count)) as cc,
        ROUND(avg(user_score), 3) as us,
        CEIL(avg(user_count)) as uc
from    games
GROUP by
        genre_id)

SELECT  g.game_id, g.name,
        CASE WHEN g.critic_score IS NULL THEN c.cs ELSE g.critic_score END as critic_score,
        CASE WHEN g.critic_score IS NULL THEN c.cc ELSE g.critic_count END as critic_count,
        CASE WHEN g.user_score IS NULL THEN c.us ELSE g.user_score END as user_score,
        CASE WHEN g.user_score IS NULL THEN c.uc ELSE g.user_count END as user_count
FROM    games g INNER JOIN cte c
        ON g.genre_id = c.genre_id
WHERE   (g.critic_score is null OR g.user_score is NULL) AND
        year >= 2015