SQL 查询返回 24 小时，即使不存在值也每小时计数?

2021-12-30 00:00:00 group-by count tsql sql-server

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

I've written a query that groups the number of rows per hour, based on a given date range.

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

给出交易次数和一天中的给定时间.

Giving the number of transactions and the given hour of the day.

如何显示一天中的所有小时 - 从 0 到 23，并为那些没有值的显示 0?

How can I display all hours of the day - from 0 to 23, and show 0 for those which have no values?

谢谢.

使用下面的 tvf 对我有用一天，但是我不确定如何让它在某个日期范围内工作.

Using the tvf below works for me for one day, however I'm not sure how to make it work for a date range.

使用 24 小时的临时表:

Using the temp table of 24 hours:

 -- 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

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

So how can I get this to group properly?

另一个问题是，有几天不会有交易，而这几天也需要出现.

The other issue is that for some days there will be no transactions, and these days also need to appear.

谢谢.

推荐答案

所以回到使用 Remus 的原始函数，我在递归调用中重新使用它并将结果存储在临时表中:

So going back to using Remus' original function, I've re-used it in a recursive call and storing the results in a temp table:

DECLARE @count INT
DECLARE @NumDays INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CurrentDay DATE

    DECLARE @tmp_Transactions TABLE 
    (
        StartHour DATETIME,
        TotalHourlyTransactions INT
    )   

SET @StartDate = '2000/02/10'
SET @EndDate = '2010/02/13'
SET @count = 0
SET @NumDays = DateDiff(Day, @StartDate, @EndDate)
WHILE @count < @NumDays 
    BEGIN
        SET @CurrentDay = DateAdd(Day, @count, @StartDate)
        INSERT INTO @tmp_Transactions (StartHour, TotalHourlyTransactions)
            SELECT  h.StartHour ,
                    t.TotalHourlyTransactions
            FROM    tvfGetDay24Hours(@CurrentDay) AS h
                    OUTER APPLY ( SELECT    COUNT(TransactionID) AS TotalHourlyTransactions
                                  FROM      [dbo].[TerminalTransactions]
                                  WHERE     TransactionTime BETWEEN h.StartHour AND h.EndHour
                                            AND TerminalId = 4
                                ) AS t
            ORDER BY h.StartHour
        SET @count = @Count + 1
    END 

SELECT *
FROM @tmp_Transactions

相关文章