복붙노트

[SQL] SQL Server의 누적 합계를 계산

SQL

SQL Server의 누적 합계를 계산

다음 표 (TestTable라는)를 상상해 :

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

내가 좋아하는, 날짜 순서로 실행중인 총을 반환하는 쿼리를 싶습니다

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

I 2000 / 2,008분의 2,005 SQL 서버에서이 일을 여러 가지 방법이 있습니다 알고 있습니다.

나는 특히 집계 세트 문 트릭을 사용하는 방법의 종류에 관심이 :

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
   SELECT id, somedate, somevalue, null
   FROM TestTable
   ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

...이 매우 효율적이지만 반드시 보장은 UPDATE 문이 올바른 순서로 행을 처리 할 수없는 때문에이 문제를 해결 문제가 들어 있습니다했다. 어쩌면 우리는 그 문제에 대한 어떤 명확한 답을 얻을 수 있습니다.

하지만 어쩌면 사람들이 제안 할 수있는 다른 방법이 있습니까?

편집 : 이제 설치와 SqlFiddle하고 '업데이 트 트릭'위의 예와

해결법

  1. ==============================

    1.업데이트, 당신은 SQL Server를 실행하는 경우 2012 참조 : https://stackoverflow.com/a/10309947

    업데이트, 당신은 SQL Server를 실행하는 경우 2012 참조 : https://stackoverflow.com/a/10309947

    문제는 오버 조항의 SQL 서버 구현이 다소 제한된다는 점이다.

    오라클 (그리고 ANSI-SQL)는 등의 작업을 수행 할 수 있습니다 :

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

    SQL Server는이 문제에 대한 더 깨끗한 솔루션을 제공합니다. 나는 큰 결과에 대한 몇 가지 벤치마킹을해야 할 것하지만 내 직감이는 커서가 가장 빠른 그 드문 경우 중 하나라고 나에게 말하고있다.

    업데이트 트릭은 편리하지만 나는 그것의 매우 깨지기을 느낍니다. 당신이 전체 테이블을 업데이트하는 경우 다음이 기본 키의 순서로 진행됩니다 것으로 보인다. 기본 키의 오름차순로 날짜를 설정 그렇다면 당신은 아마 안전 할 것입니다. 하지만 당신은 문서화되지 않은 SQL 서버 구현 세부 사항에 의존하고 (또한 만약 내가 어떻게 볼 궁금이 발동에 의해 수행되는 최대 쿼리 종료 : 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
  2. ==============================

    2.SQL 서버 2012 년에는 OVER () 절과 함께 SUM ()를 사용할 수 있습니다.

    SQL 서버 2012 년에는 OVER () 절과 함께 SUM ()를 사용할 수 있습니다.

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

    SQL 바이올린

  3. ==============================

    3.샘 사프란가에 큰 일을했다 동안, 그는 여전히이 문제에 대한 재귀 공통 테이블 식 코드를 제공하지 않았다. 그리고 우리를 위해 SQL 서버 2008 R2가 아니라 나다 작업하는 사람들, 그것은 100,000 행에 대해 약 10 배 빠른 커서보다는 내 작품을 컴퓨터의 여전히 빠른 실행 총을 얻을 수있는 방법, 또한 인라인 쿼리입니다. 그래서, 여기 (나는 테이블에 ORD 열이 있다고 가정하여 해요 그리고 빨리이 번호에 고유 제한해야합니다도 처리, 간격없이 일련 번호입니다)입니다 :

    샘 사프란가에 큰 일을했다 동안, 그는 여전히이 문제에 대한 재귀 공통 테이블 식 코드를 제공하지 않았다. 그리고 우리를 위해 SQL 서버 2008 R2가 아니라 나다 작업하는 사람들, 그것은 100,000 행에 대해 약 10 배 빠른 커서보다는 내 작품을 컴퓨터의 여전히 빠른 실행 총을 얻을 수있는 방법, 또한 인라인 쿼리입니다. 그래서, 여기 (나는 테이블에 ORD 열이 있다고 가정하여 해요 그리고 빨리이 번호에 고유 제한해야합니다도 처리, 간격없이 일련 번호입니다)입니다 :

    ;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

    SQL 바이올린 데모

    최신 정보 또한 변수 또는 기발한 갱신이 업데이트에 대한 호기심이었다. 그래서 보통은 확인을 작동하지만, 우리가 어떻게 확인이 모든 시간을 작동 할 수 있는가? 물론, 여기에 약간의 트릭이 (- http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258 여기를 찾을 수 없음) -의 당신은 그냥 현재 및 이전 ORD을 확인하고 경우에 1/0 할당을 사용하는 사람들 당신이 기대하는 것과 다른 :

    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

    당신이 당신의 테이블에 적절한 클러스터 된 인덱스 / 기본 키가있는 경우 내가 본 바로는 업데이트 (0으로 결코 발생하지 분할) 선형 방식으로 모든 시간을 진행합니다 (우리의 경우는 ORD_ID에 의해 인덱스 될 것이다). 즉, 프로덕션 코드에서 사용하려는 경우 결정하는 당신까지했다 :)

    NVARCHAR 연결 / 인덱스 / NVARCHAR (최대) 설명 할 수없는 행동 -는 기발한 업데이트의 신뢰성에 대한 유용한 정보를 포함 원인 업데이트 2 나는이 대답을 연결하고있다.

  4. ==============================

    4.이에 대한 SQL 2005 이상 작품에 연산자를 적용 :

    이에 대한 SQL 2005 이상 작품에 연산자를 적용 :

    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
  5. ==============================

    5.

    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 문에 비교에 사용하는 임의의 열을 생성하기 위해 ROW_NUMBER () 함수와 임시 테이블을 사용할 수 있습니다.

  6. ==============================

    6.상관 하위 쿼리를 사용합니다. 매우 간단하고, 여기 있습니다 :

    상관 하위 쿼리를 사용합니다. 매우 간단하고, 여기 있습니다 :

    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

    코드는 정확히 맞지 않을 수 있습니다,하지만 난 생각이 있음을 확신합니다.

    그룹이이 날짜가 두 번 이상 나타나는 경우에, 당신은 결과 집합의 일단을보고 싶어한다.

    당신이 반복 날짜를보고 괜찮다, 또는 당신이 원래 값과 ID를보고 싶은 경우, 다음은 당신이 원하는 것입니다 :

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

    7.또한 비정규 수 있습니다 - 상점이 같은 테이블에서 합계 실행 :

    또한 비정규 수 있습니다 - 상점이 같은 테이블에서 합계 실행 :

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx

    선택은 다른 솔루션에 비해 훨씬 빠르게 작동하지만 수정이 느려질 수 있습니다

  8. ==============================

    8.SQL 서버 2008이 윈도 작품을 가정하면 (나는 시도한 것을) 다른 곳에서하는 것처럼,이 갈 줄 :

    SQL 서버 2008이 윈도 작품을 가정하면 (나는 시도한 것을) 다른 곳에서하는 것처럼,이 갈 줄 :

    select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;

    MSDN은 (? 어쩌면 2005도)는 SQL 서버 2008에서 사용할 수라고하지만 난 그것을 시도하는 손에 인스턴스가 없습니다.

    편집 : 음, 분명히 SQL 서버 "BY 파티션을"지정 (그룹으로 결과를 나누어 있지만 GROUP BY가하는 꽤 방식으로 집계되지 않음)없이 윈도우 사양 ( "OVER (...)") 허용하지 않습니다. Annoying--은 MSDN 구문 참조는 자사의 옵션 제안,하지만 난 단지 순간에 주위 SQLSERVER 2000 인스턴스를 가지고있다.

    쿼리 나는 오라클 10.2.0.3.0과 PostgreSQL 8.4 베타 버전 모두에서 일을했다. 그래서 잡기 위해 MS에게)

  9. ==============================

    9.당신은 SQL 서버 2008 R2 이상을 사용하는 경우. 그리고, 그것은 할 수있는 가장 짧은 방법이 될 것입니다;

    당신은 SQL 서버 2008 R2 이상을 사용하는 경우. 그리고, 그것은 할 수있는 가장 짧은 방법이 될 것입니다;

    Select id
    ,somedate
    ,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable

    LAG는 이전 행의 값을 얻기 위해 사용한다. 당신은 더 많은 정보를 원하시면 구글을 할 수 있습니다.

    [1]:

  10. ==============================

    10.나는 누계는 간단한 INNER 아래 작업을 가입하여 달성 할 수 있다고 생각합니다.

    나는 누계는 간단한 INNER 아래 작업을 가입하여 달성 할 수 있다고 생각합니다.

    SELECT
     ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID
    ,rt.*
INTO
    #tmp
FROM
    (
        SELECT 45 AS ID, CAST('01-01-2009' AS DATETIME) AS SomeDate, 3 AS SomeValue
        UNION ALL
        SELECT 23, CAST('01-08-2009' AS DATETIME), 5
        UNION ALL
        SELECT 12, CAST('02-02-2009' AS DATETIME), 0
        UNION ALL
        SELECT 77, CAST('02-14-2009' AS DATETIME), 7
        UNION ALL
        SELECT 39, CAST('02-20-2009' AS DATETIME), 34
        UNION ALL
        SELECT 33, CAST('03-02-2009' AS DATETIME), 6
    ) rt

SELECT
     t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
    ,SUM(t2.SomeValue) AS RunningTotal
FROM
    #tmp t1
    JOIN #tmp t2
        ON t2.OrderID <= t1.OrderID
GROUP BY
     t1.OrderID
    ,t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
ORDER BY
    t1.OrderID

DROP TABLE #tmp
  11. ==============================

    11.다음은 필요한 결과를 얻을 수 있습니다.

    다음은 필요한 결과를 얻을 수 있습니다.

    SELECT a.SomeDate,
       a.SomeValue,
       SUM(b.SomeValue) AS RunningTotal
FROM TestTable a
CROSS JOIN TestTable b
WHERE (b.SomeDate <= a.SomeDate) 
GROUP BY a.SomeDate,a.SomeValue
ORDER BY a.SomeDate,a.SomeValue

    SomeDate에 클러스터 된 인덱스를 갖는 것은 크게 성능이 향상됩니다.

  12. ==============================

    12.가입 사용 사용이 가입에 또 다른 변화이다. 이제 쿼리는 다음과 같다 수 :

    가입 사용 사용이 가입에 또 다른 변화이다. 이제 쿼리는 다음과 같다 수 :

        SELECT a.id, a.value, SUM(b.Value)FROM   RunTotalTestData a,
    RunTotalTestData b
    WHERE b.id <= a.id
    GROUP BY a.id, a.value 
    ORDER BY a.id;

    더 당신은이 링크를 visite에 수에 대한 http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12

  13. ==============================

    13.가장 좋은 방법은 윈도우 함수를 사용하는 것입니다 끝내야이지만, 그것은 또한 간단한 상관 하위 쿼리를 사용하여 수행 할 수 있습니다.

    가장 좋은 방법은 윈도우 함수를 사용하는 것입니다 끝내야이지만, 그것은 또한 간단한 상관 하위 쿼리를 사용하여 수행 할 수 있습니다.

    Select id, someday, somevalue, (select sum(somevalue) 
                                from testtable as t2
                                where t2.id = t1.id
                                and t2.someday <= t1.someday) as runningtotal
from testtable as t1
order by id,someday;
  14. ==============================

    14.

    BEGIN TRAN
CREATE TABLE #Table (_Id INT IDENTITY(1,1) ,id INT ,    somedate VARCHAR(100) , somevalue INT)


INSERT INTO #Table ( id  ,    somedate  , somevalue  )
SELECT 45 , '01/Jan/09', 3 UNION ALL
SELECT 23 , '08/Jan/09', 5 UNION ALL
SELECT 12 , '02/Feb/09', 0 UNION ALL
SELECT 77 , '14/Feb/09', 7 UNION ALL
SELECT 39 , '20/Feb/09', 34 UNION ALL
SELECT 33 , '02/Mar/09', 6 

;WITH CTE ( _Id, id  ,  _somedate  , _somevalue ,_totvalue ) AS
(

 SELECT _Id , id  ,    somedate  , somevalue ,somevalue
 FROM #Table WHERE _id = 1
 UNION ALL
 SELECT #Table._Id , #Table.id  , somedate  , somevalue , somevalue + _totvalue
 FROM #Table,CTE 
 WHERE #Table._id > 1 AND CTE._Id = ( #Table._id-1 )
)

SELECT * FROM CTE

ROLLBACK TRAN
  15. ==============================

    15.여기에 실행 합계를 계산하는 2 개 간단한의 방법이 있습니다 :

    여기에 실행 합계를 계산하는 2 개 간단한의 방법이 있습니다 :

    접근 1 : 당신의 DBMS가 분석 기능을 지원하는 경우는이 방법을 쓸 수있다

    SELECT     id
           ,somedate
           ,somevalue
           ,runningtotal = SUM(somevalue) OVER (ORDER BY somedate ASC)
FROM       TestTable

    이 접근 : 당신은 당신의 데이터베이스 버전 / 분석 기능을 지원하지 않습니다 자체 DBMS 경우 OUTER의 사용은 적용 할 수 있습니다

    SELECT     T.id
           ,T.somedate
           ,T.somevalue
           ,runningtotal = OA.runningtotal
FROM       TestTable T
           OUTER APPLY (
                           SELECT   runningtotal = SUM(TI.somevalue)
                           FROM     TestTable TI
                           WHERE    TI.somedate <= S.somedate
                       ) OA;

    참고 : - : 행에 걸쳐 합계를 실행하고 ID로 그룹화 계산 별도로 다른 파티션에 대한 누적 합계를 계산해야하는 경우 여기에 게시, 그것은 수행 할 수 있습니다

    16. from https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 내부와 업데이트 문은 오라클에 합류  (0) 2020.03.05
[SQL] T-SQL 분할 문자열  (0) 2020.03.05
[SQL] 어떻게 업데이트는 SQL Server에서 SELECT에서합니까?  (0) 2020.03.05
[SQL] SQL 주입을 방지하는 좋은 방법은 무엇입니까? [복제]  (0) 2020.03.05
[SQL] 어떻게 SQL의 다른 열을 기준으로 DISTINCT MAX (열 값)와 행을 선택할 수 있습니까?  (0) 2020.03.05
,

티스토리툴바