Calculer un total courant dans SQL Server

Question

Imaginez le tableau suivant (appelé 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

Je voudrais une requête qui renvoie un total courant dans l'ordre des dates, par exemple :

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

Je sais qu'il y a différentes manières de procéder dans SQL Server 2000 / 2005 / 2008.

Je m'intéresse particulièrement à ce genre de méthode qui utilise l'astuce de l'agrégation des ensembles :

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

... c'est très efficace mais j'ai entendu dire qu'il y avait des problèmes autour de cela parce que vous ne pouvez pas nécessairement garantir que la UPDATE traitera les lignes dans l'ordre correct. Nous pourrons peut-être obtenir des réponses définitives à ce sujet.

Mais peut-être y a-t-il d'autres moyens que les gens peuvent suggérer ?

edit : Maintenant avec un SqlFiddle avec la configuration et l'exemple de "mise à jour" ci-dessus

Answer 1

Mise à jour Si vous utilisez SQL Server 2012, voyez : https://stackoverflow.com/a/10309947

Le problème est que l'implémentation de la clause Over dans le serveur SQL est assez limité .

Oracle (et ANSI-SQL) vous permet de faire des choses comme :

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

SQL Server ne vous donne aucune solution propre à ce problème. Mon instinct me dit qu'il s'agit d'un de ces rares cas où un curseur est le plus rapide, bien que je doive faire quelques analyses comparatives sur des résultats importants.

L'astuce de mise à jour est pratique mais je pense qu'elle est assez fragile. Il semble que si vous mettez à jour une table complète, la mise à jour se fera dans l'ordre de la clé primaire. Ainsi, si vous définissez votre date comme une clé primaire ascendante, vous obtiendrez les résultats suivants probably être en sécurité. Mais vous vous fiez à un détail d'implémentation non documenté de SQL Server (de plus, si la requête finit par être exécutée par deux procs, je me demande ce qui se passera, voir : MAXDOP) :

Échantillon de travail complet :

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

Vous avez demandé une analyse comparative, voici les résultats.

La méthode SAFE la plus rapide serait le curseur, qui est un ordre de grandeur plus rapide que la sous-requête corrélée de la jonction croisée.

Le moyen le plus rapide est l'astuce UPDATE. Mon seul souci est que je ne suis pas certain que, dans toutes les circonstances, la mise à jour se déroule de manière linéaire. Il n'y a rien dans la requête qui le dit explicitement.

En résumé, pour un code de production, je choisirais le curseur.

Données d'essai :

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

Test 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 

Test 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

Test 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

Test 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

Answer 2

Dans SQL Server 2012, vous pouvez utiliser SOMME() avec le OVER() clause.

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

Fiddle SQL

Answer 3

Bien que Sam Saffron ait fait un excellent travail dessus, il n'a toujours pas fourni expression récursive du tableau commun pour ce problème. Et pour nous qui travaillons avec SQL Server 2008 R2 et non Denali, c'est toujours le moyen le plus rapide d'obtenir le total courant, c'est environ 10 fois plus rapide que le curseur sur mon ordinateur de travail pour 100000 lignes, et c'est aussi une requête en ligne.
Donc, voilà (je suppose qu'il y a une ord dans la table et il s'agit d'un numéro séquentiel sans discontinuité, pour un traitement rapide, il devrait également y avoir une contrainte unique sur ce numéro) :

;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

<a href="http://sqlfiddle.com/#!3/d41d8/6967" rel="nofollow noreferrer">sql fiddle demo</a>

mise à jour J'étais également curieux de savoir ce qui suit mise à jour avec variable o mise à jour excentrique . En général, cela fonctionne bien, mais comment être sûr que cela fonctionne à chaque fois ? http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258 ) - il suffit de vérifier l'actuel et le précédent ord et utiliser 1/0 au cas où elles seraient différentes de ce que vous attendiez :

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

D'après ce que j'ai vu, si vous avez un bon index clusterisé/clé primaire sur votre table (dans notre cas, il s'agirait de l'index by ord_id ) la mise à jour se fera toujours de manière linéaire (jamais de division par zéro). Cela dit, c'est à vous de décider si vous voulez l'utiliser dans du code de production :)

actualisation 2 Je mets un lien vers cette réponse, car elle contient des informations utiles sur le manque de fiabilité de cette mise à jour étrange. concaténation nvarchar / index / nvarchar(max) comportement inexplicable .

Answer 4

L'opérateur APPLY dans SQL 2005 et plus fonctionne pour cela :

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

Answer 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

Vous pouvez également utiliser la fonction ROW_NUMBER() et une table temporaire pour créer une colonne arbitraire à utiliser dans la comparaison de l'instruction SELECT interne.