문제
제출
SELECT
strftime ('%Y', acquisition_date) "Acquisition year",
count(*) "New acquisitions this year (Flow)",
sum(count(*)) over (
ORDER BY
strftime ('%Y', acquisition_date)
) "Total collection size (Stock)"
FROM
artworks
WHERE
"Acquisition year" is not null
GROUP BY
1
ORDER BY
1 asc📌 새로 배운 내용
✅ SUM() (윈도우 함수)
누적합(누계) 을 계산하거나 특정 구간의 합계를 구할 때 쓰는 강력한 함수
일반적인 SUM() 집계 함수랑 달리, 윈도우 함수는 행 단위로 작동해서 각 행마다 결과를 보여줌
SUM(column_name) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ... AND ...)• PARTITION BY: 그룹화할 열을 지정 (예: 이름별, 부서별 등)
• ORDER BY: 누적할 순서를 정함
• ROWS BETWEEN: 어느 범위까지 합계를 낼지 세밀하게 지정 가능
🤔 시도한 내용
이전 행의 Flow를 이번행의 total에 더하면 된다고 생각해 이전 행의 데이터 불러오는 함수를 사용해보았다.
처음에는 flow + 이전 행의 flow 형태로 누적 값을 계산하려고 했다.
하지만 실제로 total(누적 값)을 계산하려면 total = total + 이전 행의 flow 처럼 이전까지의 total 값을 계속 유지하면서 더해가는 방식이 필요하다.
이 방식은 SQL에서 처리하기 다소 복잡하고 가독성도 떨어지기 때문에, 보다 간단하고 명확한 누적 합(Cumulative Sum) 방식으로 대체할 수 있는 방법을 찾았다.
LAG()
이전 행의 값을 가져오는 함수
LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)• column_name: 이전 값을 가져올 대상 컬럼
• offset: 몇 행 이전의 값을 가져올지 (기본값은 1)
• default_value: 이전 값이 없을 때 대신 사용할 값 (생략 가능)
• OVER: 윈도우 프레임 지정 (PARTITION BY + ORDER BY)
LEAD()
다음 행의 값을 가져오는 함수
LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)• column_name: 다음 값을 가져올 대상 컬럼
• offset: 몇 행 이후의 값을 가져올지 (기본값은 1)
• default_value: 이후 값이 없을 때 대신 사용할 값 (선택사항)
• OVER: 윈도우 프레임 정의 (PARTITION BY + ORDER BY)
또 다른 답
SELECT
* ,sum("New acquisitions this year (Flow)") over(ORDER BY "Acquisition year") "Total collection size (Stock)"
FROM
(
SELECT
strftime ('%Y', acquisition_date) "Acquisition year",
count(*) "New acquisitions this year (Flow)"
FROM
artworks
WHERE
"Acquisition year" is not null
GROUP BY
1
ORDER BY
1 asc
)서브쿼리를 이용해 풀었다.
