[solvesql] 5년 연속 베스트셀러 작가 찾기

* 문제 해결 Point: 연속 5년 vs 띄엄띄엄 5년을 어찌 구분할 것인가.

다음 표에서 작가 kim은 연속 5년 베스트셀러 작가이지만, 

lee는 5개년에 베스트셀러 등극이 된 작가이지만 '연속' 5년은 아닌..

이 둘을 어찌 구분할까.

author	year
kim	2011
kim	2012
kim	2013
kim	2014
kim	2015
lee	2011
lee	2012
lee	2013
lee	2015
lee	2016

 

 

방법1. lag() + 누적합 사용

lag()를 사용해 "연속된 year면 0, 그렇지 않으면 1"을 "break_flag"로 표시. 

즉, 연속되지 않은 해는 1로 표시된다.

 

그 후 sum(break_flag) over() 누적합을 사용해, 연속인 해에는 같은 그룹 id를 부여한다 (grp_id)

WITH t AS (
  SELECT
    author,
    year,
    CASE
      WHEN year = LAG(year) OVER (PARTITION BY author ORDER BY year) + 1 --💥
      THEN 0 ELSE 1
    END AS break_flag
  FROM (
    SELECT DISTINCT author, year FROM books
  ) a
),
grp AS (
  SELECT *,
         SUM(break_flag) OVER (PARTITION BY author ORDER BY year) AS grp_id --💥
  FROM t
)
SELECT
  author,
  MAX(year) AS year,
  COUNT(*) AS depth
FROM grp
GROUP BY author, grp_id
HAVING COUNT(*) >= 5;

 

break_flag와 grp_id는 다음과 같이 나온다.
연속되지 않은 해마다 1이라는 break_flag가 부여되기 때문에, 누적합(sum over)를 사용하게 되면

연속되지 않은 해마다 grp_id에 +1이 되어 다른 group으로 id가 매겨지는 것.

이로써 grp_id로 group by를 했을 때 count(*)>=5면 5년 이상 연속으로 베스트셀러에 등극한 작가를 filtering하는 것이 되겠다.

author	year	break_flag	grp_id
kim	2011	0	0
kim	2012	0	0
kim	2013	0	0
kim	2014	0	0
kim	2015	0	0
kim	2017	1	1
kim	2020	1	2
lee	2011	0	0
lee	2012	0	0
lee	2013	0	0
lee	2015	1	1
lee	2016	0	1

 

 

방법2. row_number() 사용

WITH uniq AS (
    SELECT DISTINCT author, year
    FROM books
),
grp AS (
    SELECT
        author,
        year,
        year - ROW_NUMBER() OVER (PARTITION BY author ORDER BY year) AS grp_id
    FROM uniq
),
cnt AS (
    SELECT
        author,
        MAX(year) AS year,
        COUNT(*) AS depth
    FROM grp
    GROUP BY author, grp_id
    HAVING COUNT(*) >= 5
)
SELECT *
FROM cnt
ORDER BY author, year;

 

row_number를 사용하게 되면, 

연속된 year끼리는 year-rn의 값이 같게 나오고, 

year가 연속되지 않았는데 rn은 순차적으로 매겨지니, 연속되지 않은 year-rn은 다른 값이 나온다,

year	rn	year-rn
2012	1	2011
2013	2	2011
2014	3	2011
2016	4	2012
2017	5	2012