[TIL] 윈도우 함수를 활용한 누적합 구하기 #SQL #SQLite #solvesql

solvesql - 유량(Flow)와 저량(Stock)
👉 문제 바로가기(solvesql)

-- 연도별로 새롭게 소장하게 된 작품의 수
-- 연도별 누적 소장 작품 수를 계산하여 조회

SELECT
  STRFTIME('%Y', acquisition_date) as 'Acquisition year',
  COUNT(artwork_id) as 'New acquisitions this year (Flow)',
  SUM(COUNT(artwork_id)) OVER (ORDER BY STRFTIME('%Y', acquisition_date)) as 'Total collection size (Stock)'
FROM
  artworks
WHERE
  STRFTIME('%Y', acquisition_date) is not null
GROUP BY
  STRFTIME('%Y', acquisition_date)

---

[ 논리 과정 ]

1. 소장일시 타임스탬프에서 소장년도 추출하여 그룹핑
2. 집계함수 COUNT로 매해 새롭게 소장한 작품 수 COUNT
3. 윈도우 함수 SUM() OVER() 를 활용하여 누적합 산출

[ 문제 상황 ]

• 오답 부분 코드

SELECT
  STRFTIME('%Y', acquisition_date) as 'Acquisition year',
  COUNT(artwork_id) as 'New acquisitions this year (Flow)',
  SUM(COUNT(artwork_id)) OVER (PARTITION BY STRFTIME('%Y', acquisition_date)) as 'Total collection size (Stock)'

• 윈도우 함수를 활용하여 소장년도 별 소장 작품 수의 누적합을 구해야 한다.
• 의도 : 소장년도로 그룹화하여 이전 소장년도의 소장 작품 수와 현재 행의 데이터를 합산하여 행별로 누적합 산출
• 실제 결과 : 집계함수로 COUNT한 결과와 동일하게 나옴(소장년도별 신규 소장 작품 수로 집계됨)

[ 해결 ]

• SUM(COUNT(artwork_id)) OVER (PARTITION BY STRFTIME('%Y', acquisition_date)) 코드에서 PARTITION BY를 작성하면 지정된 컬럼별로 그룹화하고 그 그룹 안에서 누적합을 산출하게 됨
• PARTITION BY를 삭제하고 ORDER BY를 넣어서 윈도우 함수의 실행 순서를 지정하고 해당 순서에 따라 순차적으로 실행시킨다.

[ 회고 ]

• 윈도우 함수에서 ORDER BY는 단순히 윈도우 함수를 실행시키는 순서의 기준을 지정하는 것이 아니라 순차적으로 실행시키는 기능이 있다.
• 본 쿼리에서 ORDER BY를 사용했다면 본 쿼리의 정렬 기준 컬럼과 동일해야 쿼리가 문제없이 실행된다.