[sql] SQL Server에서 누계 계산

SQL Server 2012를 실행중인 경우 업데이트 : https://stackoverflow.com/a/10309947

문제는 Over 절의 SQL Server 구현이 다소 제한적이라는 것 입니다.

Oracle (및 ANSI-SQL)을 사용하면 다음과 같은 작업을 수행 할 수 있습니다.

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          AS RunningTotal
  FROM Table

SQL Server는이 문제에 대한 명확한 해결책을 제공하지 않습니다. 내 직감은 커서가 가장 빠른 드문 경우 중 하나라고 말하지만 큰 결과에 대해 벤치마킹해야합니다.

업데이트 트릭은 편리하지만 상당히 취약합니다. 전체 테이블을 업데이트하는 경우 기본 키 순서대로 진행되는 것 같습니다. 따라서 날짜를 기본 키 오름차순으로 설정하면 probably안전합니다. 그러나 문서화되지 않은 SQL Server 구현 세부 사항에 의존하고 있습니다 (또한 쿼리가 두 프로세스에 의해 수행되면 어떻게 될지 궁금합니다 .MAXDOP 참조).

전체 작업 샘플 :

drop table #t
create table #t ( ord int primary key, total int, running_total int)

insert #t(ord,total)  values (2,20)
-- notice the malicious re-ordering 
insert #t(ord,total) values (1,10)
insert #t(ord,total)  values (3,10)
insert #t(ord,total)  values (4,1)

declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total

select * from #t
order by ord

ord         total       running_total
----------- ----------- -------------
1           10          10
2           20          30
3           10          40
4           1           41

당신은 벤치 마크를 요청했습니다. 이것은 낮습니다.

이 작업을 수행하는 가장 빠른 SAFE 방법은 커서가되며 상호 결합의 하위 쿼리보다 상관성이 훨씬 빠릅니다.

가장 빠른 방법은 UPDATE 트릭입니다. 그것에 대한 나의 유일한 관심은 모든 상황에서 업데이트가 선형 방식으로 진행될 것이라는 확신이 없다는 것입니다. 명시 적으로 말하는 쿼리에는 아무것도 없습니다.

결론적으로, 생산 코드의 경우 커서로 이동합니다.

테스트 데이터 :

create table #t ( ord int primary key, total int, running_total int)

set nocount on
declare @i int
set @i = 0
begin tran
while @i < 10000
begin
   insert #t (ord, total) values (@i,  rand() * 100)
    set @i = @i +1
end
commit

시험 1 :

SELECT ord,total,
    (SELECT SUM(total)
        FROM #t b
        WHERE b.ord <= a.ord) AS b
FROM #t a

-- CPU 11731, Reads 154934, Duration 11135 

시험 2 :

SELECT a.ord, a.total, SUM(b.total) AS RunningTotal
FROM #t a CROSS JOIN #t b
WHERE (b.ord <= a.ord)
GROUP BY a.ord,a.total
ORDER BY a.ord

-- CPU 16053, Reads 154935, Duration 4647

시험 3 :

DECLARE @TotalTable table(ord int primary key, total int, running_total int)

DECLARE forward_cursor CURSOR FAST_FORWARD
FOR
SELECT ord, total
FROM #t
ORDER BY ord


OPEN forward_cursor

DECLARE @running_total int,
    @ord int,
    @total int
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @ord, @total
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @total
     INSERT @TotalTable VALUES(@ord, @total, @running_total)
     FETCH NEXT FROM forward_cursor INTO @ord, @total
END

CLOSE forward_cursor
DEALLOCATE forward_cursor

SELECT * FROM @TotalTable

-- CPU 359, Reads 30392, Duration 496

시험 4 :

declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total

select * from #t

-- CPU 0, Reads 58, Duration 139

답변

select id,
       somedate,
       somevalue,
       sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

;with
CTE_RunningTotal
as
(
    select T.ord, T.total, T.total as running_total
    from #t as T
    where T.ord = 0
    union all
    select T.ord, T.total, T.total + C.running_total as running_total
    from CTE_RunningTotal as C
        inner join #t as T on T.ord = C.ord + 1
)
select C.ord, C.total, C.running_total
from CTE_RunningTotal as C
option (maxrecursion 0)

-- CPU 140, Reads 110014, Duration 132

declare @total int, @ord int

select @total = 0, @ord = -1

update #t set
    @total = @total + total,
    @ord = case when ord <> @ord + 1 then 1/0 else ord end,
    ------------------------
    running_total = @total

select * from #t

-- CPU 0, Reads 58, Duration 139

select
    t.id ,
    t.somedate ,
    t.somevalue ,
    rt.runningTotal
from TestTable t
 cross apply (select sum(somevalue) as runningTotal
                from TestTable
                where somedate <= t.somedate
            ) as rt
order by t.somedate

SELECT TOP 25   amount,
    (SELECT SUM(amount)
    FROM time_detail b
    WHERE b.time_detail_id <= a.time_detail_id) AS Total FROM time_detail a

SELECT
somedate,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
GROUP BY somedate
ORDER BY somedate

SELECT
id,
somedate,
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate