如标题所示,我需要一些帮助来使T-SQL的运行状况更全面。问题是我需要做的总和是一个计数的总和:

sum(count (distinct (customers))) 


如果我只运行计数,结果将是: >
Day | CountCustomers
----------------------
5/1  |      1
5/2  |      0
5/3  |      5


我需要输出的总和为:方法,但永远不要计数。现在我不知道该怎么做了。

评论

请使用哪个版本的SQL Server?您能否共享数据的范围-我们是在谈论1000行,一百万,十亿吗?真的只是这两栏,还是您为我们简化了架构?最后,Day是关键,值是连续的吗?

我做了一个关于运行总计的全面博客(Quirky更新vs混合递归CTE vs游标):ienablemuch.com/2012/05/…我没有包括使用基于纯集合的方法的运行总计,性能没什么好需要:sqlblog.com/blogs/adam_machanic/archive/2006/07/12/…

#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),不受支持的快速更新和禁止光标。只是要警惕使用RANGEROWS之间的区别,或者根本不指定-仅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