[Day 9] solvesql | Advent of SQL 2024 | 게임 평점 예측하기 1💡

게임 평점 예측하기 1

 

문제 조건

1. 2015년 이후에 발매한 게임 중 누락된 평점 데이터를 대체

2. 같은 장르를 가진 전체 게임의 평균 평점과 평점을 남긴 평균 평론가/사용자 수를 사용해 누락된 정보를 대체할 것

3. 평균 평점은 반올림하여 셋째 자리까지, 평론가/사용자 수는 올림 하여 자연수로 표현

 

Solution 

 

✅ MySQL

/*
 1) 2015년 이후 발매한 게임 >> 출시 연도 >= 2015
 2) 누락된 평점 정보가 있는 게임만을 출력 
 3) 평점 평균은 셋째 자리까지 표현, 사용자 수는 올림하여 자연수 표현
*/

WITH sub AS ( -- 대체값은 전체 게임 대상
  SELECT genre_id
       , ROUND(AVG(critic_score), 3) AS critic_score
       , CEIL(AVG(critic_count)) AS critic_count
       , ROUND(AVG(user_score), 3) AS user_score
       , CEIL(AVG(user_count)) AS user_count
  FROM games
  GROUP BY genre_id
)
-- 누락된 평점 정보만 대체할 것, 누락되지 않은 값은 그대로 출력
SELECT g.game_id
     , g.name
     , IFNULL(g.critic_score, s.critic_score) AS critic_score
     , IFNULL(g.critic_count, s.critic_count) AS critic_count
     , IFNULL(g.user_score, s.user_score) AS user_score
     , IFNULL(g.user_count, s.user_count) AS user_count
FROM games g
  JOIN sub s ON g.genre_id = s.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)

 

CEIL() : 값보다 큰 정수 중 가장 작은 정수 반환, 즉 올림을 의미함
IFNULL(A, B) : 컬럼 A가 null인 경우 값 B로 대체

 

 

✅ SQLite

WITH avg_ratings_by_genre AS (
  SELECT 
      genre_id,
      ROUND(AVG(critic_score), 3) AS avg_critic_score,
      CAST(AVG(critic_count) + 0.99999 AS INTEGER) AS avg_critic_count,
      ROUND(AVG(user_score), 3) AS avg_user_score,
      CAST(AVG(user_count) + 0.99999 AS INTEGER) AS avg_user_count
  FROM games
  GROUP BY genre_id
), 
missing_ratings_games AS (
  SELECT 
      game_id,
      name,
      genre_id,
      critic_score,
      critic_count,
      user_score,
      user_count
  FROM games
  WHERE year >= 2015 
    AND (critic_score IS NULL 
         OR critic_count IS NULL 
         OR user_score IS NULL 
         OR user_count IS NULL)
)
SELECT 
    g.game_id,
    g.name,
    COALESCE(g.critic_score, r.avg_critic_score) AS critic_score,
    COALESCE(g.critic_count, r.avg_critic_count) AS critic_count,
    COALESCE(g.user_score, r.avg_user_score) AS user_score,
    COALESCE(g.user_count, r.avg_user_count) AS user_count
FROM missing_ratings_games g
JOIN avg_ratings_by_genre r ON g.genre_id = r.genre_id

 

▶ Step 1: 장르별 평균 평점 계산

먼저, genre_id별로 critic_score, critic_count, user_score, user_count의 평균값을 구한다. 이때 사용자/평론가 수는 CAST 함수를 사용해 자연수로 변환한다.

 

▶ Step 2: 누락된 평점 데이터를 가진 게임 필터링

critic_score, critic_count, user_score, user_count 중 하나라도 NULL 값을 가진 게임을 필터링한다. 단, 조건으로 2015년 이후 발매된 게임만 포함한다.

 

▶ Step 3: 누락된 데이터를 평균값으로 채우기

COALESCE 함수는 인수를 2개로 한 경우 IFNULL 함수와 동일한 역할을 수행한다.

✔️ 0209 0819 복습