SQL Server Çalışan bir Toplam hesaplamak
Aşağıdaki tablo (TestTable
) denilen bir düşünün:
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
Tarih içinde çalışan toplam sipariş gibi döndüren bir sorgu istiyorum:
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
SQL Server various ways of doing this olduğunu biliyorum 2000 / 2005 / 2008.
Toplama-set-beyan, hile kullandığı yöntem: bu tür özellikle ilgi duyuyorum
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
... bu çok etkili olur ama mutlaka UPDATE
deyimi doğru sırayla satırları işleyecektir garanti edemezsin çünkü sorunları var duydum. Belki bu konuda bazı cevaplar bulabiliriz.
Ama belki de insanlara tavsiye etmenin başka yolları da var mı?
edit: Şimdi Kur SqlFiddle ve 'hile' örnek yukarıda . güncelleme ile
CEVAP
Güncellemeeğer SQL Server 2012'yi çalıştırıyorsanız,: http://stackoverflow.com/a/10309947
Sorun Üzerinde yan tümcesi SQL Server uygulaması somewhat limited.
Oracle (ANSI-SQL) gibi şeyleri yapmak için izin:
SELECT somedate, somevalue,
SUM(somevalue) OVER(ORDER BY somedate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Table
SQL Server, bu soruna kesin bir çözüm verir. İçimden bir ses, Bu bir imleç büyük sonuçlar üzerinde bazı kıyaslama yapmak zorunda kalacak olsa da en hızlı olduğu o nadir durumlardan biri olduğunu söylüyor.
Update hile kullanışlı ama oldukça kırılgan hissediyorum. Eğer tam bir tablo güncellemeden sonra birincil anahtarın sırayla devam edecek gibi görünüyor. Eğer birincil anahtar probably
güvenli olacak artan eşin olarak ayarlayın. Ama belgesiz SQL Server bir uygulama ayrıntıları güvenerek de eğer sorgu iki procs tarafından gerçekleştirilen biterse ne olacak merak ediyorum, bakın: MAXDOP):
Tam çalışan bir örnek:
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
Bu işin iç yüzünü bir kıyaslama istedin.
Bu büyüklükte bir sipariş daha hızlı korelasyon daha İmleci olurdu yapmanın en GÜVENLİ yolu alt sorgu çapraz birleşim.
Mutlak en hızlı şekilde GÜNCELLEMESİ hiledir. Tek endişem her koşulda güncelleme doğrusal bir şekilde devam edeceğinden eminim. Açıkça öyle diyor sorguda bir şey yok.
Sonuç olarak, üretim kodu için imleç ile giderdim.
Test verileri:
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
Sorgu SQL Server üzerinde çalışan list...
SQL Server Çalışan büyük komut dosyala...
SQL SERVER iki tarih arasındaki toplam...
PHP Dateİnterval toplam saniye hesapla...
Nasıl ms sql server 2005'te açık/aktif...