[SOLVESQL] 스테디 셀러 작가 찾기 - DISTINCT와 윈도우함수

https://solvesql.com/problems/find-steadyseller-writers/

https://solvesql.com/problems/find-steadyseller-writers/

 

 

마침 최근 치뤘던 한 코딩 테스트 문제랑 비슷해서 초반엔 쉽게 푸는..줄 알았던 그런 문제.

ROW_NUMBER를 기반으로 그룹 번호를 매겨주고서 이를 기반으로 그룹당 카운트가 5가 넘어가는 것만 출력하면 되는데...

 

[풀이] : 주요 포인트들

1. DISTINCT를 활용하여 작가별 연도 중복 제거(초반에 놓친 포인트!!!!) + 서브쿼리 활용: 문제를 처음 풀 때 놓쳤던 내용이자 이 포스트에서 주로 다루고자 하는 내용
2. ROW_NUMBER를 활용해서 작가의 연도별로 열번호 부여
3. 부여한 열번호를 기반으로 그룹화 : YEAR - ROW_NUM => 연속된 연도들의 최초 연도로 그루핑 가능해짐
4. 그룹 기반으로 최근 연도와 그룹별 연도 수 구하기

WITH TMP AS (
SELECT       author
            ,year
            ,ROW_NUMBER() OVER (PARTITION BY AUTHOR ORDER BY year) AS RN
FROM         books
WHERE        genre = 'Fiction'
ORDER BY     1, 2
)

SELECT       DISTINCT author
            ,year
            ,cnt AS depth
FROM        (
              SELECT     author
                        ,MAX(year) OVER (PARTITION BY AUTHOR, GRP) AS year
                        ,COUNT(*) OVER (PARTITION BY AUTHOR, GRP) AS cnt
              FROM      (
                          SELECT     author
                                    ,year
                                    ,year - RN AS GRP
                                    ,RN
                          FROM       TMP
                ) A
) B
WHERE        cnt >= 5

 

위의 쿼리문 대로 출력을 해보니 사람 수 다섯 명. 이대로 제출을 하니 6명이어야 하는데 한 명이 빠졌다고 했고, 다른 풀이들을 찾아보니 Rick이라는 사람이 빠졌다.

 

그래서 Rick을 따로 조회해보니 아래와 같이 나왔다. distinct를 초반에 먹여줘도 그대로 문제였다. 

 

어찌하면 좋으리오....

 

그래서 추가로 서칭을 해보니 아래의 사실을 알게 되었다.

이거 몰라서 삼사십분은 헤맨듯;;;;

[DISTINCT 특징]

• SELECT 절의 전체 행 리스트에 대한 중복행 제거
• 단, 윈도우 함수(ROW_NUMBER, RANK 등)의 경우 행마다 고유한 값을 만들기 때문에 DISTINCT가 무의미해짐
• 대안: 서브쿼리를 활용하는 것 외에는 답 없음

SELECT       DISTINCT author
            ,year
             --  ,ROW_NUMBER() OVER (PARTITION BY author ORDER BY year) AS RN
FROM         books
WHERE        author = 'Rick Riordan'
ORDER BY     1, 2

 

DISTINCT로 이렇게 중복이 없어지지만

 

SELECT       DISTINCT author
            ,year
            ,ROW_NUMBER() OVER (PARTITION BY author ORDER BY year) AS RN
FROM         books
WHERE        author = 'Rick Riordan'
ORDER BY     1, 2

ROW_NUMBER 등과 함께 쓰면 무용해진다.

 

---

그렇게 저렇게 해서 아래가 최종 답.

WITH TMP AS (
SELECT       author
            ,year
            ,ROW_NUMBER() OVER (PARTITION BY AUTHOR ORDER BY year) AS RN
FROM         (
                SELECT       DISTINCT author
                            ,year
                FROM         books
                WHERE        genre = 'Fiction'
  ) A
ORDER BY     1, 2
)

SELECT       DISTINCT author
            ,year
            ,cnt AS depth
FROM        (
              SELECT     author
                        ,MAX(year) OVER (PARTITION BY AUTHOR, GRP) AS year
                        ,COUNT(*) OVER (PARTITION BY AUTHOR, GRP) AS cnt
              FROM      (
                          SELECT     author
                                    ,year
                                    ,year - RN AS GRP
                                    ,RN
                          FROM       TMP
                ) A
) B
WHERE        cnt >= 5

 

 

---

추가. 하루 뒤 정리한 답.

왜 위처럼 복잡하게 적었던 걸까.

 

WITH TMP AS (
SELECT     author
          ,year
          ,ROW_NUMBER() OVER (PARTITION BY AUTHOR ORDER BY YEAR) AS RN
FROM       (
                SELECT     DISTINCT author
                          ,year
                FROM       books
                WHERE      GENRE = 'Fiction'
            ) A
)


SELECT     author
          ,MAX(YEAR) AS year
          ,COUNT(year) as depth
FROM       TMP
GROUP BY   author, year - rn
HAVING     COUNT(year) >= 5