您是否知道为过去 12 小时中的每一小时生成一条记录的简单方法？

对于 SQL Server 2005+，您可以使用循环和递归 CTE 非常轻松地生成这 12 条记录。以下是递归 CTE 的示例：

DECLARE @Date DATETIME
SELECT @Date = '20120308 11:00:00'

;WITH Dates AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour], 
      DATEADD(HOUR,-1,@Date) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])), 
      DATEADD(HOUR,-1,[Date]), Num+1
    FROM Dates
    WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates

然后，您只需将其与您的事件表一起加入即可。

Tally 表可以用于这样的事情。他们可以非常有效。创建下面的计数表。我为您的示例创建了只有 24 行的计数表，但是您可以使用任意数量的行来创建它以适应其他目的。

SELECT TOP 24 
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

我假设您的表名为 dbo.tblEvents，请运行以下查询。我相信这就是您正在寻找的：

SELECT t.n, count(e.EventTime)
FROM dbo.Tally t
LEFT JOIN dbo.tblEvent e  on t.n = datepart(hh, e.EventTime)
GROUP BY t.n
ORDER BY t.n

我相信归功于以下链接，我相信这是我第一次遇到这个问题的地方：

http://www.sqlservercentral.com/articles/T-SQL/62867/

http://www.sqlservercentral.com/articles/T-SQL/74118/

首先，我很抱歉自从我上次发表评论以来我的回复延迟。

该主题出现在评论中，即使用递归 CTE（从这里开始的 rCTE）运行速度足够快，因为行数很少。虽然看起来可能是这样，但事实并非如此。

构建计数表和计数功能

在开始测试之前，我们需要构建一个具有适当聚集索引和 Itzik Ben-Gan 风格的 Tally 函数的物理 Tally Table。我们还将在 TempDB 中完成所有这些操作，以免意外丢失任何人的好东西。

这是构建 Tally Table 的代码和我当前的 Itzik 精彩代码的生产版本。

--===== Do this in a nice, safe place that everyone has
    USE tempdb
;
--===== Create/Recreate a Physical Tally Table
     IF OBJECT_ID('dbo.Tally','U') IS NOT NULL
        DROP TABLE dbo.Tally
;
     -- Note that the ISNULL makes a NOT NULL column
 SELECT TOP 1000001
        N = ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0)
   INTO dbo.Tally
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (N)
;
--===== Create/Recreate a Tally Function
     IF OBJECT_ID('dbo.fnTally','IF') IS NOT NULL
        DROP FUNCTION dbo.fnTally
;
GO
 CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.

 As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

 Usage:
--===== Syntax example (Returns BIGINT)
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;

 Notes:
 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URLs for how it works and introduction for how it replaces certain loops. 
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
    that many values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending 
    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 22 Apr 2015 - Jeff Moden
        - Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1)                                  --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)      --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c)            --10E12 or 1 Trillion rows                 
            SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
             UNION ALL 
            SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
GO

顺便说一句……请注意，它构建了一百万零一行的 Tally 表，并在大约一秒左右的时间内向其中添加了一个聚集索引。用 rCTE 试试，看看需要多长时间！;-)

建立一些测试数据

我们还需要一些测试数据。是的，我同意我们将要测试的所有功能，包括 rCTE，仅在 12 行中运行在一毫秒或更短的时间内，但这是很多人陷入的陷阱。稍后我们将更多地讨论这个陷阱，但现在让我们模拟调用每个函数 40,000 次，这是关于我的商店中的某些函数在一天 8 小时内被调用的次数。试想一下，在大型在线零售业务中可能会调用多少次此类函数。

所以，这里是用随机日期构建 40,000 行的代码，每行都有一个行号，仅用于跟踪目的。我没有花时间把时间弄成整个小时，因为在这里没关系。

--===== Do this in a nice, safe place that everyone has
    USE tempdb
;
--===== Create/Recreate a Test Date table
     IF OBJECT_ID('dbo.TestDate','U') IS NOT NULL
        DROP TABLE dbo.TestDate
;
DECLARE  @StartDate DATETIME
        ,@EndDate   DATETIME
        ,@Rows      INT
;
 SELECT  @StartDate = '2010' --Inclusive
        ,@EndDate   = '2020' --Exclusive
        ,@Rows      = 40000  --Enough to simulate an 8 hour day where I work
;
 SELECT  RowNum       = IDENTITY(INT,1,1)
        ,SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,@StartDate,@EndDate)+@StartDate
   INTO dbo.TestDate
   FROM dbo.fnTally(1,@Rows)
;

构建一些功能来做 12 行小时的事情

接下来，我将 rCTE 代码转换为一个函数并创建了 3 个其他函数。它们都被创建为高性能 iTVF（内联表值函数）。您总是可以分辨出来，因为 iTVF 没有像标量或 mTVF（多语句表值函数）那样的 BEGIN。

这是构建这 4 个函数的代码......我以它们使用的方法命名它们，而不是它们所做的，只是为了更容易识别它们。

--=====  CREATE THE iTVFs
--===== Do this in a nice, safe place that everyone has
    USE tempdb
;
-----------------------------------------------------------------------------------------
     IF OBJECT_ID('dbo.OriginalrCTE','IF') IS NOT NULL
        DROP FUNCTION dbo.OriginalrCTE
;
GO
 CREATE FUNCTION dbo.OriginalrCTE
        (@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
WITH Dates AS
(
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,@Date)) [Hour], 
      DATEADD(HOUR,-1,@Date) [Date], 1 Num
    UNION ALL
    SELECT DATEPART(HOUR,DATEADD(HOUR,-1,[Date])), 
      DATEADD(HOUR,-1,[Date]), Num+1
    FROM Dates
    WHERE Num <= 11
)
SELECT [Hour], [Date]
FROM Dates
GO
-----------------------------------------------------------------------------------------
     IF OBJECT_ID('dbo.MicroTally','IF') IS NOT NULL
        DROP FUNCTION dbo.MicroTally
;
GO
 CREATE FUNCTION dbo.MicroTally
        (@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 SELECT  [Hour] = DATEPART(HOUR,DATEADD(HOUR,t.N,@Date))
        ,[DATE] = DATEADD(HOUR,t.N,@Date)
   FROM (VALUES (-1),(-2),(-3),(-4),(-5),(-6),(-7),(-8),(-9),(-10),(-11),(-12))t(N)
;
GO
-----------------------------------------------------------------------------------------
     IF OBJECT_ID('dbo.PhysicalTally','IF') IS NOT NULL
        DROP FUNCTION dbo.PhysicalTally
;
GO
 CREATE FUNCTION dbo.PhysicalTally
        (@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 SELECT  [Hour] = DATEPART(HOUR,DATEADD(HOUR,-t.N,@Date))
        ,[DATE] = DATEADD(HOUR,-t.N,@Date)
   FROM dbo.Tally t
  WHERE N BETWEEN 1 AND 12
;
GO
-----------------------------------------------------------------------------------------
     IF OBJECT_ID('dbo.TallyFunction','IF') IS NOT NULL
        DROP FUNCTION dbo.TallyFunction
;
GO
 CREATE FUNCTION dbo.TallyFunction
        (@Date DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 SELECT  [Hour] = DATEPART(HOUR,DATEADD(HOUR,-t.N,@Date))
        ,[DATE] = DATEADD(HOUR,-t.N,@Date)
   FROM dbo.fnTally(1,12) t
;
GO

构建测试工具来测试功能

最后但同样重要的是，我们需要一个测试工具。我进行基线检查，然后以相同的方式测试每个功能。

这是测试工具的代码...

PRINT '--========== Baseline Select =================================';
DECLARE @Hour INT, @Date DATETIME
;
    SET STATISTICS TIME,IO ON;
 SELECT  @Hour = RowNum
        ,@Date = SomeDateTime
   FROM dbo.TestDate
  CROSS APPLY dbo.fnTally(1,12);
    SET STATISTICS TIME,IO OFF;
GO
PRINT '--========== Orginal Recursive CTE ===========================';
DECLARE @Hour INT, @Date DATETIME
;

    SET STATISTICS TIME,IO ON;
 SELECT  @Hour = fn.[Hour]
        ,@Date = fn.[Date]
   FROM dbo.TestDate td
  CROSS APPLY dbo.OriginalrCTE(td.SomeDateTime) fn;
    SET STATISTICS TIME,IO OFF;
GO
PRINT '--========== Dedicated Micro-Tally Table =====================';
DECLARE @Hour INT, @Date DATETIME
;

    SET STATISTICS TIME,IO ON;
 SELECT  @Hour = fn.[Hour]
        ,@Date = fn.[Date]
   FROM dbo.TestDate td
  CROSS APPLY dbo.MicroTally(td.SomeDateTime) fn;
    SET STATISTICS TIME,IO OFF;
GO
PRINT'--========== Physical Tally Table =============================';
DECLARE @Hour INT, @Date DATETIME
;
    SET STATISTICS TIME,IO ON;
 SELECT  @Hour = fn.[Hour]
        ,@Date = fn.[Date]
   FROM dbo.TestDate td
  CROSS APPLY dbo.PhysicalTally(td.SomeDateTime) fn;
    SET STATISTICS TIME,IO OFF;
GO
PRINT'--========== Tally Function ===================================';
DECLARE @Hour INT, @Date DATETIME
;
    SET STATISTICS TIME,IO ON;
 SELECT  @Hour = fn.[Hour]
        ,@Date = fn.[Date]
   FROM dbo.TestDate td
  CROSS APPLY dbo.TallyFunction(td.SomeDateTime) fn;
    SET STATISTICS TIME,IO OFF;
GO

在上面的测试工具中要注意的一件事是，我将所有输出分流到“一次性”变量中。这是为了尽量保持性能测量的纯正，而不会输出到磁盘或屏幕倾斜结果。

关于集合统计的注意事项

另外，对于潜在的测试人员来说，请注意......在测试标量或 mTVF 函数时，您不得使用 SET STATISTICS。它只能安全​​地用于本测试中的 iTVF 功能。SET STATISTICS 已被证明可以使 SCALAR 函数的运行速度比没有它时实际运行速度慢数百倍。是的，我正在尝试倾斜另一个风车，但那将是一篇完整的文章长度的帖子，我没有时间。我在 SQLServerCentral.com 上有一篇文章谈到了这一切，但是在这里发布链接是没有意义的，因为有人会因为它而变得不正常。

测试结果

因此，这是我在我的 6GB RAM 的小型 i5 笔记本电脑上运行测试工具时的测试结果。

--========== Baseline Select =================================
Table 'Worktable'. Scan count 1, logical reads 82309, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 206 ms.
--========== Orginal Recursive CTE ===========================
Table 'Worktable'. Scan count 40001, logical reads 2960000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4258 ms,  elapsed time = 4415 ms.
--========== Dedicated Micro-Tally Table =====================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 235 ms.
--========== Physical Tally Table =============================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 250 ms,  elapsed time = 252 ms.
--========== Tally Function ===================================
Table 'Worktable'. Scan count 1, logical reads 81989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TestDate'. Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 250 ms,  elapsed time = 253 ms.

仅选择数据的“基线选择”（每行创建 12 次以模拟相同的返回量）大约在 1/5 秒内出现。其他一切都在大约四分之一秒内出现。好吧，除了该死的 rCTE 功能之外的一切。耗时 4 又 1/4 秒或 16 倍（慢 1,600%）。

看看逻辑读取（内存 IO）...... rCTE 消耗了高达 2,960,000 次（几乎 300 万次读取），而其他功能仅消耗了大约 82,100 次。这意味着 rCTE 消耗的内存 IO 是其他任何函数的 34.3 倍以上。

结束思想

让我们总结一下。与其他任何函数相比，执行此“小型”12 行操作的 rCTE 方法使用的 CPU（和持续时间）多 16 倍（1,600%），内存 IO 多 34.3 倍（3,430%）。

Heh... I know what you're thinking. "Big Deal! It's just one function."

Yeah, agreed, but how many other functions do you have? How many other places outside of functions do you have? And do you have any of those that work with more than just 12 rows each run? And, is there any chance that someone in a lurch for a method might copy that rCTE code for something much bigger?

Ok, time to be blunt. It makes absolutely no sense for people to justify performance challenged code just because of supposed limited row counts or usage. Except for when you purchase an MPP box for perhaps millions of dollars (not to mention the expense of rewriting code to get it to work on such a machine), you can't buy a machine that runs your code 16 times faster (SSD's won't do it either... all this stuff was in high speed memory when we tested it). Performance is in the code. Good performance is in good code.

Can you imagine if all of your code ran "just" 16 times faster?

Never justify bad or performance challenged code on low rowcounts or even low usage. If you do, you might have to borrow one of the windmills I was accused of tilting at to keep your CPUs and disks cool enough. ;-)

A WORD ON THE WORD "TALLY"

Yeah... I agree. Semantically speaking, the Tally Table contains numbers, not "tallies". In my original article on the subject (it wasn't the original article on the technique but it was my first on it), I called it "Tally" not because of what it contains, but because of what it does... it's used to "count" instead of looping and to "Tally" something is to "Count" something. ;-) Call it what you will... Numbers Table, Tally Table, Sequence Table, whatever. I don't care. For me, "Tally" is more meaning full and, being a good lazy DBA, contains only 5 letters (2 are identical) instead of 7 and it's easier to say for most folks. It's also "singular", which follows my naming convention for tables. ;-) It's also what the article that contained a page from a book from the 60's called it. I'll always refer to it as a "Tally Table" and you'll still know what I or someone else means. I also avoid Hungarian Notation like the plague but called the function "fnTally" so that I could say "Well, if you used the eff-en Tally Function I showed you, you wouldn't have a performance problem" without it actually being an HR violation. ;-)

What I'm more concerned about is people learning to use it properly instead of resorting to things like performance challenged rCTEs and other forms of Hidden RBAR.

您需要RIGHT JOIN使用查询返回您需要的每小时一条记录的数据。

有关获取行号的几种方法，请参见this ，然后您可以从当前时间减去几小时。

在 Oracle 中，对 dual 的分层查询将生成行：

SELECT to_char(sysdate-level/24,'HH24') FROM dual CONNECT BY Level <=24;