sql-server - SQL 查询返回 24 小时，每小时计数，即使不存在值？

Question

我编写了一个查询，它根据给定的日期范围对每小时的行数进行分组。

SELECT CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) as TDate, 
    COUNT(TransactionID) AS TotalHourlyTransactions
    FROM MyTransactions WITH (NOLOCK)
    WHERE TransactionTime BETWEEN CAST(@StartDate AS SMALLDATETIME) AND CAST(@EndDate AS SMALLDATETIME)
    AND TerminalId = @TerminalID
    GROUP BY CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108)
    ORDER BY TDate ASC

显示如下内容:

02/11/20 07 4
02/11/20 10 1
02/11/20 12 4
02/11/20 13 1
02/11/20 14 2
02/11/20 16 3

给出交易数量和一天中的给定时间。

如何显示一天中的所有时间 - 从 0 到 23，并为没有值的时间显示 0？

谢谢。

更新

使用下面的 tvf 对我有用一天，但我不确定如何使它适用于某个日期范围。

使用 24 小时临时表:

 -- temp table to store hours of the day    
 DECLARE @tmp_Hours TABLE ( WhichHour SMALLINT )

 DECLARE @counter SMALLINT
 SET @counter = -1
 WHILE @counter < 23 
    BEGIN
        SET @counter = @counter + 1
      --print 
        INSERT  INTO @tmp_Hours
                ( WhichHour )
        VALUES  ( @counter )
    END 

    SELECT MIN(CONVERT(VARCHAR(10),[dbo].[TerminalTransactions].[TransactionTime],101)) AS TDate, [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) AS TheHour,
        COUNT([dbo].[TerminalTransactions].[TransactionId]) AS TotalTransactions, 
        ISNULL(SUM([dbo].[TerminalTransactions].[TransactionAmount]), 0) AS TransactionSum
    FROM [dbo].[TerminalTransactions] RIGHT JOIN @tmp_Hours ON [@tmp_Hours].[WhichHour] = CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) 
    GROUP BY [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108),  COALESCE([dbo].[TerminalTransactions].[TransactionAmount], 0)

给我一​​个结果:

TDate      WhichHour TheHour TotalTransactions TransactionSum
---------- --------- ------- ----------------- ---------------------
02/16/2010 0         00      4                 40.00
NULL       1         NULL    0                 0.00
02/14/2010 2         02      1                 10.00
NULL       3         NULL    0                 0.00
02/14/2010 4         04      28                280.00
02/14/2010 5         05      11                110.00
NULL       6         NULL    0                 0.00
02/11/2010 7         07      4                 40.00
NULL       8         NULL    0                 0.00
02/24/2010 9         09      2                 20.00

那么我怎样才能让它正确分组呢？

另一个问题是，有几天没有交易，这几天也需要出现。

谢谢。

Answer 1

您首先构建 23 hours 表，对 transactions 表进行外部连接。出于同样的目的，我使用表值函数:

create function tvfGetDay24Hours(@date datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@date as float)) as datetime)) as StartHour
  , dateadd(hour, number+1, cast(floor(cast(@date as float)) as datetime)) as EndHour
from master.dbo.spt_values
where number < 24 and type = 'p');

然后我可以在需要获取“每小时”基础数据的查询中使用 TVF，即使数据中缺少间隔:

select h.StartHour, t.TotalHourlyTransactions
from tvfGetDay24Hours(@StartDate) as h
outer apply (
  SELECT 
    COUNT(TransactionID) AS TotalHourlyTransactions
    FROM MyTransactions 
    WHERE TransactionTime BETWEEN h.StartHour and h.EndHour
    AND TerminalId = @TerminalID) as t
order by h.StartHour

更新

在任意日期之间返回 24 小时的 TVF 示例:

create function tvfGetAnyDayHours(@dateFrom datetime, @dateTo datetime)
returns table
as return (
select dateadd(hour, number, cast(floor(cast(@dateFrom as float)) as datetime)) as StartHour
  , dateadd(hour, number+1, cast(floor(cast(@dateFrom as float)) as datetime)) as EndHour
from master.dbo.spt_values
where type = 'p'
and number < datediff(hour,@dateFrom, @dateTo) + 24);

请注意，由于 master.dbo.spt_values 仅包含 2048 个数字，因此该函数在相隔超过 2048 小时的日期之间不起作用。