按小时计数行并包括零 [英] count rows by hour and include zeroes

问题描述

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

I am trying to count (by hour) the number of entries into a database. I have successfully written a query that counts by hour, but it omits hours with zero entries. I need the results to include the zeroes. I have looked around the internet, and have found a lot of suggestions. I have created a View that has one column with datetime entries by minute. I've tried joining the main table to this view and I get the same results as without the join. Still no zeroes. Wondering how to get this query to return zeroes. I am using MS SQL 2008 R2. any suggestions?

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是什么意思.

Update: I changed the hoursalot view to be by the hour I'm sorry but I'm not sure what you mean by full view SQL.

出于法律原因，我无法从archivedItems表中放入任何海峡信息，但RecordCreated列是海峡时间戳记，即'2013-04-05 14:09:59.167'

I can't put any strait info from the archivedItems table for legal reasons but the RecordCreated column is a strait timestamp ie '2013-04-05 14:09:59.167'

推荐答案

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

Try putting the vw_hoursalot as the leftmost table in the condition - this will mean that all rows from the view will be returned, whether a corresponding record in ArchivedHours is found or not.

我再次编辑-这次我花时间模拟一些虚拟数据，我意识到我的代码和你的代码中的一个问题是 ArchivedItems.RecordCreated 列在WHERE条件下用作过滤器.因此，将仅返回具有现有 RecordCreated 值的记录.

I've edited again - this time I took the time to mock up some dummy data, and I realized that a problem in the code, both mine and yours, is that the ArchivedItems.RecordCreated column is being used as a filter in the WHERE condition. Therefore, only records with an existing RecordCreated value will be returned.

我已将该条件移至JOIN，然后针对我模拟的一些非常基本的数据运行以下查询.假设您的 vw_HoursALot 视图返回的是二十四个整数，分别为0-23，这将为您提供所需的数据.

I've moved that condition to the JOIN, and run the query below against some very basic data I mocked up. Assuming that your vw_HoursALot view is returning twenty-four integers numbered 0-23, this ought to get you the data you are looking for.

请注意:我假设 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)

这篇关于按小时计数行并包括零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！