DAY 7 / [solvesql] Advent of SQL 2024 2문제 (스테디셀러 작가 찾기, 멀티 플랫폼 게임 찾기)

스테디셀러 작가 찾기

-- 5년 이상 연속으로 베스트셀러 작품 목록에 이름을 올린 소설 작가와 연도 정보를 출력하는 쿼리

WITH cal AS (
  SELECT *
       , LAG(year, 4) OVER(PARTITION BY author ORDER BY year) AS prev4
  FROM (
        SELECT author
            , year
            , COUNT(*) AS cnt_books
        FROM books
        WHERE genre = 'Fiction'
        GROUP BY author, year
        ) AS tmp
)

SELECT author
     , MAX(year) AS "year"
     , MAX(year) - MIN(prev4) + 1 AS depth
FROM cal
WHERE year = prev4 + 4
GROUP BY author

 

작성 순서

1. [작가 X 연도]별 책 갯수 카운트 집계하는 테이블 (단, 소설만 포함)
2. 현재 행의 연도에서 4개 전에 위치한 행의 연도를 가져오는 컬럼을 생성 (윈도우함수 LAG 이용하여 "prev4" 생성)
3. 현재 행의 연도와 4개 전에 위치한 행의 연도의 차이가 4인 경우만 필터링한 후(차이가 딱 4일 때 5년 연속에 해당하고, 4보다 크다면 중간에 빈 연도가 있어서 5년 연속에는 해당하지 않는다. 4보다 작은 경우는 없다)
4. 연속 베스트셀러 기간과 최근 연도를 표시하는 컬럼 생성 (연속 베스트셀러 기간을 표시할 때는 +1을 해주어야 연속 연수로 표시됨)

 

 

 

멀티 플랫폼 게임 찾기

-- 2012년 이후 출시된 게임들 중
-- 둘 이상의 메이저 플랫폼 계열에 출시된 게임 이름을 출력하는 쿼리 (단, 중복된 게임은 1번만 출력)

SELECT game_name AS name
FROM (
      SELECT g.game_id AS game_id
          , g.name AS game_name
          , p.name AS platform_name
          , CASE
              WHEN p.name IN ('PS3', 'PS4', 'PSP', 'PSV') THEN 'Sony'
              WHEN p.name IN ('Wii', 'WiiU', 'DS', '3DS') THEN 'Nintendo'
              WHEN p.name IN ('X360', 'XONE') THEN 'Microsoft'
              END AS platform_c_name
      FROM games AS g
      INNER JOIN platforms AS p ON g.platform_id = p.platform_id
      WHERE g.year >= 2012
        AND p.name IN ('PS3', 'PS4', 'PSP', 'PSV', 'Wii', 'WiiU', 'DS', '3DS', 'X360', 'XONE')
      ) AS tmp
GROUP BY game_name
HAVING COUNT(DISTINCT platform_c_name) >= 2
ORDER BY game_name

 

작성 순서

1. games 테이블과 platforms 테이블 조인 (플랫폼 이름으로 메이저 게임 계열을 찾아야 하므로) 
2. 조인한 테이블에  조건 설정.
-- (1) 출시 연도가 2012년 이후만
-- (2) platforms 테이블의 게임 플랫폼 이름이 (PS3, PS4, ...) 인 플랫폼만
3. 플랫폼 이름 조건에 따라 플랫폼 제작사 이름으로 표시하는 컬럼 생성
4. 3번까지 만든 테이블을 서브쿼리로 FROM 에 넣고, 플랫폼 제작사가 2개 이상인 게임만 추출하기

 

 

 

📌 여기서 갑자기 궁금한 점.

CASE 구문으로 각 플랫폼 이름 조건에 따라 플랫폼 제작사 이름을 매핑하는 컬럼을 생성할 때, WHERE 절에 필요한 플랫폼 이름만 명시해서 필터링하는게 쿼리 성능측면에서 좋을까? EXPLAIN 명령어로 확인해보자.

 

 

항목	WHERE 절 없는 경우	WHERE 절 있는 경우
쿼리 시작 노드	GroupAggregate	GroupAggregate
총 예상 비용 (Total Cost)	~702.45	~532.81 ✅ 더 낮음
입력 행 수 (Sort 단계)	2884 rows	1068 rows ✅ 더 적음
CASE문 실행	CASE WHEN p.name IN (...) THEN ... → 모든 행 평가	CASE WHEN p.name IN (...) THEN ... → WHERE로 인해 애초에 불필요한 행이 없음 ✅

 

 

- WHERE 절로 필요한 플랫폼만 미리 필터링하면 읽는 행 수도 줄고 (1068 rows) 정렬·집계 대상도 줄어들어 실행 비용이 25% 이상 감소됨 => 실행 효율성 향상
- WHERE 절을 활용해 필요한 데이터만 미리 필터링하는 것이 성능상 매우 유리함 (조건에 해당하지 않는 데이터를 사전에 제한해야 불필요한 계산과 정렬을 피할 수 있음)