Вычислите рабочее общее количество в SQL Server
Ниже код, который работает для меня:
mFirestore.collection("AllUsers").document(str).get().addOnSuccessListener(OnSuccessListener<DocumentSnapshot> {documentSnapshot->
var id: String = ""
var gamingname:String=""
if(documentSnapshot.exists()){
id= documentSnapshot.getString("gaminguserid")!!
gamingname=documentSnapshot.getString("gaminname")!!
}else
{
Toast.makeText(this@HostActivityScreen,"Document does not exist",
Toast.LENGTH_LONG).show()
}
}).addOnFailureListener(OnFailureListener { e->
val error=e.message
Toast.makeText(this@HostActivityScreen,"Error:"+error, Toast.LENGTH_LONG).show()
})6 ответов
Update, if you are running SQL Server 2012 see: https://stackoverflow.com/a/10309947
The problem is that the SQL Server implementation of the Over clause is somewhat limited.
Oracle (and ANSI-SQL) allow you to do things like:
SELECT somedate, somevalue,
SUM(somevalue) OVER(ORDER BY somedate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Table
SQL Server gives you no clean solution to this problem. My gut is telling me that this is one of those rare cases where a cursor is the fastest, though I will have to do some benchmarking on big results.
The update trick is handy but I feel its fairly fragile. It seems that if you are updating a full table then it will proceed in the order of the primary key. So if you set your date as a primary key ascending you will probably be safe. But you are relying on an undocumented SQL Server implementation detail (also if the query ends up being performed by two procs I wonder what will happen, see: MAXDOP):
Full working sample:
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
You asked for a benchmark this is the lowdown.
The fastest SAFE way of doing this would be the Cursor, it is an order of magnitude faster than the correlated sub-query of cross-join.
The absolute fastest way is the UPDATE trick. My only concern with it is that I am not certain that under all circumstances the update will proceed in a linear way. There is nothing in the query that explicitly says so.
Bottom line, for production code I would go with the cursor.
Test data:
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
The following will produce the required results.
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
Having a clustered index on SomeDate will greatly improve the performance.
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
You can also use the ROW_NUMBER() function and a temp table to create an arbitrary column to use in the comparison on the inner SELECT statement.
Assuming that windowing works on SQL Server 2008 like it does elsewhere (that I've tried), give this a go:
select testtable.*, sum(somevalue) over(order by somedate)
from testtable
order by somedate;
MSDN says it's available in SQL Server 2008 (and maybe 2005 as well?) but I don't have an instance to hand to try it.
EDIT: well, apparently SQL Server doesn't allow a window specification ("OVER(...)") without specifying "PARTITION BY" (dividing the result up into groups but not aggregating in quite the way GROUP BY does). Annoying-- the MSDN syntax reference suggests that its optional, but I only have SqlServer 2000 instances around at the moment.
The query I gave works in both Oracle 10.2.0.3.0 and PostgreSQL 8.4-beta. So tell MS to catch up ;)
Оператор APPLY в 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
Вы также можете денормализовать - сохранить текущие итоги в той же таблице:
Выбирает работу намного быстрее, чем любые другие решения, но модификации могут выполняться медленнее