/*
-- Title : [SQL2017] ROWS 또는 RANGE를 활용(누적 합계 구하기)
-- Reference : docs.microsoft.com/ko-kr/sql/t-sql/...
-- Tag : 누적합계 rows unbounded preceding rows range central following
*/
■ 누적합계 구하기
/**************************************/
-- 샘플 데이터 생성
/**************************************/
drop table #ttt;
create table #ttt
( id int not null identity(1,1)
, a varchar(10) not null
, b int not null
);
insert into #ttt
select 'A', 12 union all
select 'B', 3 union all
select 'C', 10 union all
select 'D', 9 union all
select 'E', 8 union all
select 'F', 6 union all
select 'G', 11 union all
select 'H', 7;
select * from #ttt;
/**************************************/
-- 누적 합계 구하기
/**************************************/
select id
, a
, b
, sum(b) over(order by a rows unbounded preceding) as "csum"
from #ttt
order by id;
/* sum(b) over(partition by Col1, order by a rows unbounded preceding) as "csum" 구문도 됨*/
■ ROWS 또는 RANGE
select
object_id
, [preceding] = count(*) over(order by object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
, [central] = count(*) over(order by object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )
, [following] = count(*) over(order by object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
from sys.objects
order by object_id asc;
