실행 총계 / 실행 잔액 계산
자리가 있습니다.
create table Transactions(Tid int,amt int)
5개 행 포함:
insert into Transactions values(1, 100)
insert into Transactions values(2, -50)
insert into Transactions values(3, 100)
insert into Transactions values(4, -100)
insert into Transactions values(5, 200)
원하는 출력:
TID amt balance
--- ----- -------
1 100 100
2 -50 50
3 100 150
4 -100 50
5 200 250
기본적으로 첫 번째 레코드의 잔액은 다음과 같습니다.amt + 잔액 + 잔액 + 잔액입니다.amt저는 최적의 접근법을 찾고 있습니다.함수나 상관된 하위 쿼리를 사용하는 것에 대해 생각할 수는 있지만 정확히 어떻게 해야 하는지는 모르겠습니다.
SQL Server 2012 이상을 사용하지 않는 사용자의 경우 CLR 이외의 지원 및 보장 방법으로는 커서가 가장 효율적입니다.약간 더 빠르지만 향후 작동이 보장되지 않는 "빠른 업데이트"와 같은 다른 접근 방식도 있습니다. 물론 테이블이 커질수록 쌍곡선 성능 프로파일을 사용하는 세트 기반 접근 방식과 직접 #tempdb I/O를 요구하거나 결과적으로 거의 동일한 영향을 미치는 반복적인 CTE 방법도 있습니다.
내부 조인 - 다음 작업을 수행하지 않습니다.
느린 세트 기반 접근 방식은 다음과 같습니다.
SELECT t1.TID, t1.amt, RunningTotal = SUM(t2.amt)
FROM dbo.Transactions AS t1
INNER JOIN dbo.Transactions AS t2
ON t1.TID >= t2.TID
GROUP BY t1.TID, t1.amt
ORDER BY t1.TID;
이게 느린 이유는?테이블이 커질수록 각 증분 행은 테이블의 n-1 행을 읽어야 합니다.이는 기하급수적이며 실패, 시간 초과 또는 분노한 사용자에 한정됩니다.
상관된 하위 쿼리 - 다음 작업도 수행하지 않습니다.
하위 질의 형태는 유사하게 고통스러운 이유로 인해 유사하게 고통스럽습니다.
SELECT TID, amt, RunningTotal = amt + COALESCE(
(
SELECT SUM(amt)
FROM dbo.Transactions AS i
WHERE i.TID < o.TID), 0
)
FROM dbo.Transactions AS o
ORDER BY TID;
최신 업데이트 - 사용자의 책임 하에 업데이트합니다.
"quirky update" 방법이 위의 방법보다 더 효율적이지만, 동작이 문서화되지 않고, 순서에 대한 보장이 없으며, 동작이 현재는 작동하지만 미래에는 중단될 수 있습니다.저는 이것이 인기 있는 방법이고 효율적이기 때문에 이것을 포함하고 있지만, 그것이 제가 그것을 지지한다는 것을 의미하지는 않습니다.제가 이 질문을 중복으로 닫는 대신에 답변까지 한 주된 이유는 다른 질문이 수락된 답변으로 특이한 업데이트를 가지고 있기 때문입니다.
DECLARE @t TABLE
(
TID INT PRIMARY KEY,
amt INT,
RunningTotal INT
);
DECLARE @RunningTotal INT = 0;
INSERT @t(TID, amt, RunningTotal)
SELECT TID, amt, RunningTotal = 0
FROM dbo.Transactions
ORDER BY TID;
UPDATE @t
SET @RunningTotal = RunningTotal = @RunningTotal + amt
FROM @t;
SELECT TID, amt, RunningTotal
FROM @t
ORDER BY TID;
재귀 CTE
이 첫 번째는 TID에 의존하여 연속적이고 빈틈이 없습니다.
;WITH x AS
(
SELECT TID, amt, RunningTotal = amt
FROM dbo.Transactions
WHERE TID = 1
UNION ALL
SELECT y.TID, y.amt, x.RunningTotal + y.amt
FROM x
INNER JOIN dbo.Transactions AS y
ON y.TID = x.TID + 1
)
SELECT TID, amt, RunningTotal
FROM x
ORDER BY TID
OPTION (MAXRECURSION 10000);
이것에 의존할 수 없다면, 이 변형을 사용할 수 있습니다. 이 변형은 단순히 다음을 사용하여 연속적인 시퀀스를 만듭니다.ROW_NUMBER():
;WITH y AS
(
SELECT TID, amt, rn = ROW_NUMBER() OVER (ORDER BY TID)
FROM dbo.Transactions
), x AS
(
SELECT TID, rn, amt, rt = amt
FROM y
WHERE rn = 1
UNION ALL
SELECT y.TID, y.rn, y.amt, x.rt + y.amt
FROM x INNER JOIN y
ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
FROM x
ORDER BY x.rn
OPTION (MAXRECURSION 10000);
데이터 크기(예: 모르는 열)에 따라 관련 열을 #temp 테이블에만 먼저 채우고 기본 테이블 대신 해당 열에 대해 처리하면 전반적인 성능이 향상될 수 있습니다.
CREATE TABLE #x
(
rn INT PRIMARY KEY,
TID INT,
amt INT
);
INSERT INTO #x (rn, TID, amt)
SELECT ROW_NUMBER() OVER (ORDER BY TID),
TID, amt
FROM dbo.Transactions;
;WITH x AS
(
SELECT TID, rn, amt, rt = amt
FROM #x
WHERE rn = 1
UNION ALL
SELECT y.TID, y.rn, y.amt, x.rt + y.amt
FROM x INNER JOIN #x AS y
ON y.rn = x.rn + 1
)
SELECT TID, amt, RunningTotal = rt
FROM x
ORDER BY TID
OPTION (MAXRECURSION 10000);
DROP TABLE #x;
첫 번째 CTE 방법만이 기발한 업데이트에 필적하는 성능을 제공하지만 데이터의 특성에 대해 큰 가정을 합니다(공백 없음).다른 두 가지 방법은 취소될 것이며 이러한 경우에는 커서를 사용할 수도 있습니다(CLR을 사용할 수 없고 SQL Server 2012 이상 버전이 아닌 경우).
커서
모든 사람들은 커서가 나쁘고, 어떤 대가를 치르더라도 피해야 한다고 말하지만, 이것은 실제로 다른 지원되는 대부분의 방법의 성능을 능가하며, 기발한 업데이트보다 안전합니다.커서 솔루션보다 선호하는 것은 2012 및 CLR 방법(아래)뿐입니다.
CREATE TABLE #x
(
TID INT PRIMARY KEY,
amt INT,
rt INT
);
INSERT #x(TID, amt)
SELECT TID, amt
FROM dbo.Transactions
ORDER BY TID;
DECLARE @rt INT, @tid INT, @amt INT;
SET @rt = 0;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT TID, amt FROM #x ORDER BY TID;
OPEN c;
FETCH c INTO @tid, @amt;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rt = @rt + @amt;
UPDATE #x SET rt = @rt WHERE TID = @tid;
FETCH c INTO @tid, @amt;
END
CLOSE c; DEALLOCATE c;
SELECT TID, amt, RunningTotal = rt
FROM #x
ORDER BY TID;
DROP TABLE #x;
SQL Server 2012 이상
SQL Server 2012에 도입된 새로운 창 기능을 통해 이 작업을 훨씬 쉽게 수행할 수 있습니다(위의 모든 방법보다 성능이 우수함).
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;
큰 데이터 세트에서는 RANGE가 온 디스크 스풀(및 기본값은 RANGE)을 사용하기 때문에 위의 두 가지 옵션 중 하나보다 성능이 훨씬 우수합니다.그러나 동작과 결과가 다를 수 있으므로 이 차이를 기준으로 두 결과를 결정하기 전에 두 결과가 모두 올바른 결과를 반환하는지 확인해야 합니다.
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID)
FROM dbo.Transactions
ORDER BY TID;
SELECT TID, amt,
RunningTotal = SUM(amt) OVER (ORDER BY TID RANGE UNBOUNDED PRECEDING)
FROM dbo.Transactions
ORDER BY TID;
CLR
완전성을 위해 Pavel Pawlowski의 CLR 방법에 대한 링크를 제공합니다. CLR 방법은 SQL Server 2012 이전 버전에서 훨씬 선호되는 방법입니다(분명히 2000년은 아님).
http://www.pawlowski.cz/2010/09/sql-server-and-fastest-running-totals-using-clr/
결론
하는 경우 수 . - SQL Server 2012를 하십시오. 새로운 기능을 사용하십시오.SUM() OVER()하다, 건설하다,하다, 건설하다, 건설하다, 건설하다, 건설하다, 건설하다, 건설하다 등ROWS대 대RANGE이전 버전의 경우 스키마, 데이터에 대한 대체 접근 방식의 성능을 비교하고 성능과 관련되지 않은 요소를 고려하여 어떤 접근 방식이 적합한지 결정하려고 합니다.CLR 접근 방식일 수도 있습니다.다음은 선호하는 순서대로 제가 추천하는 사항입니다.
SUM() OVER() ... ROWS2012년 혹은 그 이상이면- CLR 방법(가능한 경우)
- 가능한 경우 첫 번째 재귀 CTE 방법
- 커서
- 다른 재귀적 CTE 방법
- 특이한 업데이트
- 조인 및/또는 상관된 하위 쿼리
이러한 방법의 성능 비교에 대한 자세한 내용은 http://dba.stackexchange.com 에서 다음 질문을 참조하십시오.
https://dba.stackexchange.com/questions/19507/running-total-with-count
또한 이러한 비교에 대한 자세한 내용을 여기에 블로그에 올렸습니다.
http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
그룹화/파티션 실행 총계에 대해서도 다음 게시물을 참조하십시오.
http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals
2012 버전을 사용하는 경우 다음과 같은 솔루션이 있습니다.
select *, sum(amt) over (order by Tid) as running_total from Transactions
이전 버전의 경우
select *,(select sum(amt) from Transactions where Tid<=t.Tid) as running_total from Transactions as t
SQL Server 2008+에서
SELECT T1.* ,
T2.RunningSum
FROM dbo.Transactions As T1
CROSS APPLY ( SELECT SUM(amt) AS RunningSum
FROM dbo.Transactions AS CAT1
WHERE ( CAT1.TId <= T1.TId )
) AS T2
SQL Server 2012 이상에서
SELECT * ,
SUM(T1.amt) OVER ( ORDER BY T1.TId
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotal
FROM dbo.Transactions AS t1
우리는 2008 R2에 있고 저는 변수와 온도표를 사용합니다.이를 통해 사례 문을 사용하여 각 행을 계산할 때 사용자 지정 작업을 수행할 수 있습니다(예: 특정 트랜잭션이 다르게 작동하거나 특정 트랜잭션 유형에 대한 총계만 원할 수 있음).
DECLARE @RunningBalance int = 0
SELECT Tid, Amt, 0 AS RunningBalance
INTO #TxnTable
FROM Transactions
ORDER BY Tid
UPDATE #TxnTable
SET @RunningBalance = RunningBalance = @RunningBalance + Amt
SELECT * FROM #TxnTable
DROP TABLE #TxnTable
230만 행의 트랜잭션 테이블과 3,300개 이상의 트랜잭션이 있는 항목이 있습니다. 이러한 유형의 쿼리를 실행하는 데는 시간이 전혀 걸리지 않습니다.
select v.ID
,CONVERT(VARCHAR(10), v.EntryDate, 103) + ' ' + convert(VARCHAR(8), v.EntryDate, 14)
as EntryDate
,case
when v.CreditAmount<0
then
ISNULL(v.CreditAmount,0)
else
0
End as credit
,case
when v.CreditAmount>0
then
v.CreditAmount
else
0
End as debit
,Balance = SUM(v.CreditAmount) OVER (ORDER BY v.ID ROWS UNBOUNDED PRECEDING)
from VendorCredit v
order by v.EntryDate desc
2012년과 함께SUM그리고.OVER이제 중첩할 수 있는 함수sum그리고.counts.
SELECT date, sum(count(DISTINCT unique_id)) OVER (ORDER BY date) AS total_per_date
FROM dbo.table
GROUP BY date
언급URL : https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance
'programing' 카테고리의 다른 글
| Oracle - 필드에 소문자가 있는 위치를 선택합니다. (0) | 2023.06.27 |
|---|---|
| 반환된 열의 이름을 Pandas Aggregate 함수에 지정하시겠습니까? (0) | 2023.06.27 |
| 명시적으로 파일을 닫는 것이 중요합니까? (0) | 2023.06.27 |
| MS SQL Server에서 예약된 단어/키워드인 테이블 이름 만들기 (0) | 2023.06.27 |
| 파이썬 문자열에서 하위 문자열이 처음 나타나는 것을 어떻게 찾을 수 있습니까? (0) | 2023.06.27 |