[solvesql - day 23] lv4 유량(Flow)와 저량(Stock) ★

👉 문제 바로가기 : solvesql - Advent of SQL 2024 DAY 23
저작권 문제로 링크만 첨부한다.

✔️문제 풀이에 필요한 점

window 함수와 group by의 처리순서를 알고 사용해야한다.
window 함수의 기본기를 닦을 수 있는 좋은 문제

✍️답안

SELECT
  strftime('%Y', acquisition_date) "Acquisition year"
  , count(*) "New acquisitions this year (Flow)" -- step 2
  , sum(count(*)) OVER(ORDER BY strftime('%Y', acquisition_date)) "Total collection size (Stock)" -- step 3
FROM
  artworks
WHERE
  acquisition_date IS NOT NULL -- step 1
GROUP BY
  strftime('%Y', acquisition_date) -- step 2
;

답안 설명

1. WHERE절 - 소장일자 정보가 없는 경우를 제거한다.
2. GROUP BY절 - 소장년도별 합계를 구한다.
3. SUM() OVER() - 소장년도별 누적합을 구한다.

function() over()의 형식을 갖는 윈도우 함수의 가장 큰 특징은
'출력 결과 수에 영향을 주지 않는다' 이다.

같은 집계함수를 써도 GROUP BY절의 경우 결과행이 줄어들지만, 윈도우 함수는 그렇지 않다.

window함수는 sql의 마지막 절차에 최종적으로, 독립적으로 동작한다고 생각하면 편하다.

답안 도출과정을 좀 더 풀어보자면 아래와 같다.

#1 - null 제거

SELECT
  acquisition_date
FROM
  artworks
WHERE
  acquisition_date IS NOT NULL 
;

#2 - 년도별 합계 계산

SELECT
  strftime('%Y', acquisition_date) "연도"
  , count(*) "연도별 합계" 
FROM
  artworks
WHERE
  acquisition_date IS NOT NULL
GROUP BY
  strftime('%Y', acquisition_date) -- "연도"
;

#3 - 년도별 누적합 계산

SELECT
  strftime('%Y', acquisition_date) "연도"
  , count(*) "연도별 합계" 
  , sum(count(*)) OVER(ORDER BY strftime('%Y', acquisition_date)) "연도별 누적합"
FROM
  artworks
WHERE
  acquisition_date IS NOT NULL 
GROUP BY
  strftime('%Y', acquisition_date)
;

• sum() over(order by col) 구문을 사용하면 col 순서대로 더한다.