SQL查询返回24小时，每小时计数，即使没有值存在吗？ [英] SQL Query to return 24 hour, hourly count even when no values exist?

问题描述

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

  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）和CAST（@EndDate AS SMALLDATETIME）
和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？
 
 
 
谢谢。
 
 
 
 UPDATE 
 
 
 
使用下面的tvf对我有用一天，但是我不知道如何使它在一个日期范围内工作。 p> 
 
 
使用24小时的临时表：
   - 临时表存储一天的时间
 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 ），[dbo]。[TerminalTransactions]。[TransactionTime]，108）
 GROUP BY [@tmp_Hours]。[WhichHour]，CONVERT（VARCHAR（2） 108），COALESCE（[dbo]。[TerminalTransactions]。[TransactionAmount]，0）
  
结果：
  TDate其中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 
  
要正确分组？
 
 
 
另一个问题是，有些日子没有交易，这些日子也需要出现。
 
 
 
感谢。
解决方案
回到使用Remus的原始函数，在递归调用中并将结果存储在临时表中：
  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 
  
 
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
Which displays something like this:
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.

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

Thanks.

UPDATE

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

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)
Gives me a result of:
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.

Thanks.
解决方案
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


                        
这篇关于SQL查询返回24小时，每小时计数，即使没有值存在吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！