sum(count (distinct (customers)))
如果我只运行计数,结果将是: >
Day | CountCustomers
----------------------
5/1 | 1
5/2 | 0
5/3 | 5
我需要输出的总和为:方法,但永远不要计数。现在我不知道该怎么做了。
#1 楼
您可以比较以下几种方法。首先让我们建立一个包含一些虚拟数据的表。我用一堆来自sys.all_columns的随机数据填充它。好吧,这是随机的-我确保日期是连续的(这实际上仅对答案之一很重要)。CREATE TABLE dbo.Hits(Day SMALLDATETIME, CustomerID INT);
CREATE CLUSTERED INDEX x ON dbo.Hits([Day]);
INSERT dbo.Hits SELECT TOP (5000) DATEADD(DAY, r, '20120501'),
COALESCE(ASCII(SUBSTRING(name, s, 1)), 86)
FROM (SELECT name, r = ROW_NUMBER() OVER (ORDER BY name)/10,
s = CONVERT(INT, RIGHT(CONVERT(VARCHAR(20), [object_id]), 1))
FROM sys.all_columns) AS x;
SELECT
Earliest_Day = MIN([Day]),
Latest_Day = MAX([Day]),
Unique_Days = DATEDIFF(DAY, MIN([Day]), MAX([Day])) + 1,
Total_Rows = COUNT(*)
FROM dbo.Hits;
结果:数据看起来像这样(5000行)-但是根据版本和内部版本,您的系统上看起来会略有不同:#< >
Earliest_Day Latest_Day Unique_Days Total_Days
------------------- ------------------- ----------- ----------
2012-05-01 00:00:00 2013-09-13 00:00:00 501 5000
所以我要比较的方法是:
“自连接”-基于集合的纯粹方法
“带日期的递归CTE” -这依赖于连续的日期(没有间隔)
“具有row_number的递归CTE”-与上述类似,但速度较慢,依赖于ROW_NUMBER
“具有#temp表的递归CTE”-根据建议从Mikael的答案中被盗
“古怪的更新”虽然不受支持并且没有希望的已定义行为,但似乎非常流行。 />自我加入
人们会这样当他们警告您不要游标时,您应该这样做,因为“基于集合的总是更快”。在最近的一些实验中,我发现游标超出了此解决方案。最多10000个递归级别,并且您知道自己感兴趣的范围的开始日期(设置锚点)。当然,您可以使用子查询来动态设置锚点,但是我想让事情保持简单。同样,这支持10000的最大递归级别,但是您无需分配锚点。
Day CustomerID
------------------- ---
2012-05-01 00:00:00 95
2012-05-01 00:00:00 97
2012-05-01 00:00:00 97
2012-05-01 00:00:00 117
2012-05-01 00:00:00 100
...
2012-05-02 00:00:00 110
2012-05-02 00:00:00 110
2012-05-02 00:00:00 95
...
古怪的更新
我再次出于完整性考虑仅将其包括在内。我个人不会依赖此解决方案,因为正如我在另一个答案中提到的那样,这种方法根本无法保证会起作用,并且在将来的SQL Server版本中可能会完全中断。 (我正在尽最大努力迫使SQL Server遵循我想要的顺序,并为索引选择提供了提示。)
这里的光标!光标是邪恶的!您应该不惜一切代价避免使用光标!”不,那不是我在说话,这只是我经常听到的东西。与流行的看法相反,在某些情况下游标是合适的。
Day c rt
------------------- -- --
2012-05-01 00:00:00 6 6
2012-05-02 00:00:00 5 11
2012-05-03 00:00:00 4 15
2012-05-04 00:00:00 7 22
2012-05-05 00:00:00 6 28
...
SQL Server 2012使我们能够轻松地计算运行总额,而无需花费自我加入的指数成本(SUM是一次计算),CTE的复杂性(包括要求连续的行以获得更好的CTE),不受支持的快速更新和禁止光标。只是要警惕使用
RANGE
和ROWS
之间的区别,或者根本不指定-仅ROWS
避免使用磁盘假脱机,否则会严重影响性能。;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
)
SELECT g.[Day], g.c, rt = SUM(g2.c)
FROM g INNER JOIN g AS g2
ON g.[Day] >= g2.[Day]
GROUP BY g.[Day], g.c
ORDER BY g.[Day];
性能比较
我采用了每种方法,并使用以下方法将其包装成一个批次: br />
;WITH g AS
(
SELECT [Day], c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], c, rt = c
FROM g
WHERE [Day] = '20120501'
UNION ALL
SELECT g.[Day], g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.[Day] = DATEADD(DAY, 1, x.[Day])
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);
我又没有DBCC命令又做了一次:最后,我将源表中的行数乘以10(将top更改为50000并添加另一个表作为交叉联接)。结果是,一次没有DBCC命令的迭代(仅出于时间考虑):
;WITH g AS
(
SELECT [Day], rn = ROW_NUMBER() OVER (ORDER BY DAY),
c = COUNT(DISTINCT CustomerID)
FROM dbo.Hits
GROUP BY [Day]
), x AS
(
SELECT [Day], rn, c, rt = c
FROM g
WHERE rn = 1
UNION ALL
SELECT g.[Day], g.rn, g.c, x.rt + g.c
FROM x INNER JOIN g
ON g.rn = x.rn + 1
)
SELECT [Day], c, rt
FROM x
ORDER BY [Day]
OPTION (MAXRECURSION 10000);
我仅测量了持续时间-我将其作为练习让读者比较这些方法在其数据上,比较其他可能重要的指标(或可能随其架构/数据而变化)。在从此答案中得出任何结论之前,您将需要根据数据和架构对其进行测试...随着行数的增加,这些结果几乎肯定会发生变化。
demo
我添加了一个sqlfiddle。结果:
结论
在我的测试中,选择将是:SQL Server 2012方法(如果我有SQL Server 2012)。
如果SQL Server 2012不可用,并且我的日期是连续的,则我将使用带日期的递归cte方法。
如果1.和2.都不适用,则将使用self-即使性能已接近,也可以加入古怪的更新,只是因为行为已得到记录并得到保证。我不太担心将来的兼容性,因为希望如果怪异的更新不成功,它将在我将所有代码都转换为1后出现。:-)模式和数据。由于这是人为设计的,行数相对较少的测试,因此也很可能是放屁的事。我已经用不同的模式和行计数进行了其他测试,并且性能启发式方法也大不相同...这就是为什么我问了那么多跟进原始问题的原因。 />我在此处发布了有关此内容的更多信息:
运行总计的最佳方法–已针对SQL Server 2012更新
#2 楼
显然,这是最佳解决方案DECLARE @dailyCustomers TABLE (day smalldatetime, CountCustomers int, RunningTotal int)
DECLARE @RunningTotal int
SET @RunningTotal = 0
INSERT INTO @dailyCustomers
SELECT day, CountCustomers, null
FROM Sales
ORDER BY day
UPDATE @dailyCustomers
SET @RunningTotal = RunningTotal = @RunningTotal + CountCustomers
FROM @dailyCustomers
SELECT * FROM @dailyCustomers
评论
有没有实现临时表的想法(我的proc已经根据需要强制通过多个临时表的值,因此我试图找到一种避免使用另一个临时表的方法)?如果没有,我将使用此方法。我认为它将起作用
–user1465095
2012年6月19日下午0:47
也可以使用自联接或嵌套子查询来完成,但是这些选项的执行效果不尽如人意。另外,无论如何,使用这些具有假脱机或工作表的替代方法,您仍然可能会遇到tempdb。
– M_M
2012年6月19日下午0:52
请注意,不能保证此“古怪的更新”方法有效-不支持此语法,并且其行为未定义,并且它可能会在将来的版本,修补程序或Service Pack中中断。因此,虽然可以接受,但是它比某些受支持的替代品要快,但会带来潜在的未来兼容性成本。
–亚伦·伯特兰(Aaron Bertrand)
2012年6月19日,0:54
Jeff Moden在某处写了很多关于此方法的警告。例如,您应该在一天中具有聚集索引。
–马丁·史密斯
2012年6月19日下午6:37
@MartinSmith这是sqlservercentral.com上的一篇非常大的文章(转到“作者”页面并在快速更新中找到他的文章)。
–Fabricio Araujo
2012年6月19日在20:20
#3 楼
这是另一种方式,成本高昂,但版本独立。它不使用临时表或变量。select T.dday, T.CustomersByDay +
(select count(A.customer) from NewCustomersByDate A
where A.dday < T.dday) as TotalCustomerTillNow
from (select dday, count(customer) as CustomersByDay
from NewCustomersByDate group by dday) T
评论
那不好,那很慢。即使您只有100行,它也会在表之间进行5,050次的乒乓读取。 200行,是20,100次。仅使用1,000行,读取sqlblog.com/blogs/adam_machanic/archive/2006/07/12/…的指数就跃升至500,500。
–迈克尔·布恩(Michael Buen)
2012年6月19日下午4:34
发布此消息后,我看到了指向您博客的链接,现在我知道这是一个非常糟糕的主意,谢谢!
– Jcis
2012年6月19日下午5:01
评论
请使用哪个版本的SQL Server?您能否共享数据的范围-我们是在谈论1000行,一百万,十亿吗?真的只是这两栏,还是您为我们简化了架构?最后,Day是关键,值是连续的吗?我做了一个关于运行总计的全面博客(Quirky更新vs混合递归CTE vs游标):ienablemuch.com/2012/05/…我没有包括使用基于纯集合的方法的运行总计,性能没什么好需要:sqlblog.com/blogs/adam_machanic/archive/2006/07/12/…