Calculer un total en cours D'exécution dans SQL Server

Dans SQL Server 2012, vous pouvez utiliser SUM() avec SUR() clause.

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

SQL Violon

Alors que Sam Saffron a fait un excellent travail, il n'a toujours pas fourni recursive common table expression code 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'exécuter total, 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, le voici (je suppose qu'il y a une colonne ord dans la table et c'est un numéro séquentiel sans lacunes, pour un traitement rapide là aussi devrait être une contrainte unique sur ce nombre):

;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 violon démo

Mettre à jour J'étais aussi curieux de connaître cette mise à jour avec la variable ou mise à jour excentrique. Donc, habituellement, cela fonctionne bien, mais comment pouvons-nous être sûrs que cela fonctionne à chaque fois? Eh bien, voici un petit truc (trouvé ici - http://www.sqlservercentral.com/Forums/Topic802558-203-21.aspx#bm981258) - vous vérifiez simplement ord actuel et précédent et utilisez 1/0 affectation au cas où ils sont différents de ce que vous attendez:

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 index/clé primaire en cluster approprié sur votre table (dans notre cas, il serait index par ord_id) la mise à jour se déroulera de manière linéaire tout le temps (jamais rencontré diviser par zéro). Cela dit, c'est à vous de décider si vous voulez l'utiliser dans le code de production:)

L'opérateur APPLY dans SQL 2005 et supérieur fonctionne pour ceci:

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

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

Utilise une sous-requête corrélée. Très simple, voilà:

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

Le code pourrait ne pas être exactement correct, mais je suis sûr que l'idée est.

Le GROUP BY est dans le cas où une date apparaît plus d'une fois, vous ne voudriez la voir qu'une seule fois dans le jeu de résultats.

Si cela ne vous dérange pas de voir des dates répétées, ou si vous voulez voir la valeur et l'id d'origine, alors ce qui suit est ce que vous voulez:

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

Vous pouvez également dénormaliser-stocker les totaux en cours d'exécution dans la même table:

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

Les sélections fonctionnent beaucoup plus rapidement que toutes les autres solutions, mais les modifications peuvent être plus lentes

En supposant que le fenêtrage fonctionne sur SQL Server 2008 comme il le fait ailleurs (que j'ai essayé), essayez ceci:

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

MSDN dit qu'il est disponible dans SQL Server 2008 (et peut-être 2005 aussi?) mais je n'ai pas d'instance à portée de main pour l'essayer.

EDIT: Eh bien, apparemment SQL Server n'autorise pas une spécification de fenêtre ("OVER (...) ") sans spécifier "PARTITION BY" (divisant le résultat en groupes mais ne pas agréger de la même manière que GROUP BY). Ennuyeux -- le La référence de syntaxe MSDN suggère que c'est facultatif, mais je n'ai que des instances SqlServer 2000 pour le moment.

La requête que j'ai donnée fonctionne à la fois dans Oracle 10.2.0.3.0 et PostgreSQL 8.4-beta. Alors dites à MS de rattraper son retard;)

Ce qui suit produira les résultats requis.

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

Avoir un index cluster sur SomeDate améliorera grandement les performances.

Si vous utilisez Sql server 2008 R2 ci-dessus. Ensuite, ce serait le moyen le plus court de faire;

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

GAL est utilisé pour obtenir la ligne précédente valeur. Vous pouvez faire google pour plus d'informations.

[1]:

Je crois qu'un total en cours d'exécution peut être atteint en utilisant l'opération de jointure interne simple ci-dessous.

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

Utilisation de jointure Une autre variante consiste à utiliser la rejoindre. Maintenant, la requête pourrait ressembler à:

    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;

Pour plus vous pouvez visiter ce lien http://askme.indianyouth.info/details/calculating-simple-running-totals-in-sql-server-12

Bien que le meilleur moyen de le faire soit d'utiliser une fonction de fenêtre, il peut également être fait en utilisant une simple sous-requête corrélée .

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;

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