按小时数行并包含零

Question

我试图(按小时)计算数据库中的条目数。我已经成功地编写了一个按小时计算的查询，但是它忽略了带有零条目的小时。我需要结果包括零。我环顾了一下互联网，发现了很多建议。我已经创建了一个视图，它有一个列，每分钟都有一个日期时间条目。我尝试将主表连接到这个视图中，并得到了与没有联接的结果相同的结果。还是没有零。想知道如何让这个查询返回零。我正在使用MS 2008 R2。有什么建议吗？

declare @limit datetime;
use InputArchive
set @limit = current_timestamp;
set @limit = DATEADD(hour, -72, @limit);
SELECT DATEADD(hour, datediff(hour, 0, ArchivedItems.RecordCreated), 0) as TimeHour, COUNT(ISNULL((ArchivedItems.RecordCreated),' ')) as NumPerHour
FROM ArchivedItems 
LEFT OUTER JOIN vw_hoursalot
ON vw_hoursalot.dtHr = ArchivedItems.RecordCreated
where InputTypeId = 5 or InputTypeId = 6 or InputTypeId = 8 and (ArchivedItems.RecordCreated >= @limit)
Group BY DATEADD(hour, Datediff(hour, 0, ArchivedItems.RecordCreated), 0)
order by DATEADD(hour, datediff(hour, 0, ArchivedItems.RecordCreated), 0) desc
option (MAXRECURSION 0)

更新:我将“小时”更改为“小时”，对不起，我不知道您所说的“全视图SQL”是什么意思。

出于法律原因，我无法从archivedItems表中提供任何海峡信息，但RecordCreated列是一个海峡时间戳，即“2013-04-05 14:09:59.167”

Answer 1

尝试将vw_hoursalot作为最左边的表放在条件中--这意味着视图中的所有行都将被返回，无论是否在ArchivedHours中找到了相应的记录。

我再次编辑了--这一次我花时间模拟了一些虚拟数据，我意识到代码中的一个问题，无论是我的还是您的，都是ArchivedItems.RecordCreated列被用作WHERE条件下的筛选器。因此，只返回具有现有RecordCreated值的记录。

我已经将该条件移到JOIN中，并对我模拟的一些非常基本的数据运行下面的查询。假设您的vw_HoursALot视图返回编号为0-23的24个整数，这将为您提供所要的数据。

，请注意，：我假设InputTypeID来自ArchivedItems。

WITH -- I used these two CTEs as my dummy data, based on the information in your post
    vw_HoursALot AS 
      (
      SELECT 1 dtHr UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
      SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION 
      SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION 
      SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION 
      SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 0 
      ),
    ArchivedItems AS 
      (
        SELECT GETDATE() AS RecordCreated, 5 AS InputTypeID
        UNION SELECT DATEADD(HOUR, -3, GETDATE()), 6 AS InputTypeID
      )


-- this part is actually doing the work
SELECT 
    vw_HoursALot.dtHr,
    COUNT(ArchivedItems.RecordCreated) AS NumPerHour 
FROM 
    vw_hoursalot
     LEFT OUTER JOIN 
    ArchivedItems ON 
        vw_hoursalot.dtHr = DATEPART(hour, ArchivedItems.RecordCreated) AND
        ArchivedItems.RecordCreated >= DATEADD(hour, -72, GETDATE()) AND
          (
            InputTypeId = 5 OR
            InputTypeId = 6 OR 
            InputTypeId = 8 
          ) 
GROUP BY vw_HoursALot.dtHr 
ORDER BY vw_HoursALot.dtHr DESC 
OPTION (MAXRECURSION 0)

Answer 2

以下是我想出的：

declare @limit datetime;
declare @BaseTime datetime
set @BaseTime = '20141020 15:00'; --must be an even hour
set @limit = DATEADD(hour, -72, @BaseTime);
print @Basetime
;WITH
  D1(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
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  Numbers AS (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS Number FROM D2),
  AllHours AS (SELECT DATEADD(hour,numbers.number,@limit) AS hr FROM Numbers),
  Raw_Data AS (
    SELECT DATEADD(hour,DATEDIFF(hour,@BaseTime,RecordCreated),@BaseTime) AS HourRecorded FROM
    ArchivedItems
    WHERE RecordCreated BETWEEN @limit AND @BaseTime
    AND InputTypeID IN (5,6,8)
    )
SELECT count(Raw_Data.HourRecorded),AllHours.hr
FROM AllHours left outer join Raw_Data on AllHours.hr = Raw_Data.HourRecorded
GROUP BY AllHours.hr
order by AllHours.hr

下面是我用来创建测试数据的内容

;WITH
  D1(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
           ),
  D2(N) AS (SELECT 1 FROM D1 a, D1 b),
  D4(N) AS (SELECT 1 FROM D2 a, D2 b),
  Numbers AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS Number FROM D4)
INSERT INTO ArchivedItems(InputTypeID, RecordCreated) 
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) % 10 AS INT)), DATEADD(MINUTE, ABS(CAST(CAST(NEWID() AS VARBINARY) % 10000 AS INT)),'20141017')
FROM Numbers
--Make sure there is a gap
DELETE FROM ArchivedItems WHERE RecordCreated BETWEEN '2014-10-20 06:00:00.000' AND '2014-10-20 08:00:00.000'