查询以获取每天每小时的结果，即使数据不存在

I am trying to get the query for getting the count of users every hour of day in the table. If the data for that hour is not present, I want to record the hour with count of zero. Also users should be counted only for their first entry. Subsequent entries should be ignored.

我正在尝试获取查询，以获取表中每小时的用户计数。如果那个小时的数据不存在，我想用计数为零的方式记录这个小时。同样，用户应该只对他们的第一个条目进行计数。后面的条目应该被忽略。

Table:

表:

userId      creationDate
1           2014-10-08 14:33:20.763
2           2014-10-09 04:24:14.283
3           2014-10-10 18:34:26.260

Desired output:

期望的输出:

Date                      UserCount
2014-10-08 00:00:00.000      1
2014-10-08 01:00:00.000      1
2014-10-08 02:00:00.000      1
2014-10-08 03:00:00.000      0
2014-10-08 04:00:00.000      1
....
.....
2014-10-10 23:00:00.000      1
2014-10-10 00:00:00.000      0

My attempt:

我的尝试:

SELECT 
    CAST(creationDate as date) AS ForDate, 
    DATEPART(hour, date) AS OnHour, 
    COUNT(distinct userId) AS Totals
FROM 
    Table
WHERE
    primaryKey = 123
GROUP BY 
    CAST(creationDate as date), DATEPART(hour, createDate)

This only gives me per hour for the record that is present. Not the data for the missing hours. I think there is a way by using a cross join to get 0 data even for the missing hours.

这只给了我每小时的记录。而不是失踪时间的数据。我认为有一种方法可以使用交叉连接来获取0数据，即使是丢失的时间。

Something like this, I came across, but not able to construct a proper query with it.

类似这样的事情，我遇到了，但却无法用它构造一个合适的查询。

cross join (select 
                ROW_NUMBER() over (order by (select NULL)) as seqnum
            from 
                INFORMATION_SCHEMA.COLUMNS) hours 
where hours.seqnum >= 24 

Once again, I am not a SQL expert, but trying hard to construct this result set.

再说一遍，我不是SQL专家，但我正在努力构建这个结果集。

One more attempt :

一个尝试:

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour 
     from (select distinct cast(cast(createDate as DATE) as datetime) as thedatehour
           from Table a 
          ) a 
          cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours.seqnum (less than)= 24
    )
select dh.DateHour, COUNT(distinct c.userId) 
from dh cross join Table c
--on dh.DateHour = c.createDate
group by dh.DateHour
order by 1

4 个解决方案

#1

You need to build up a table of possible hours, and then join this to your actual records.

您需要构建一个可能的小时表，然后将其加入到实际记录中。

The best way to build up a table of possible hours is to use a recursive common table expression. Here's how:

构建可能的小时表的最佳方法是使用递归公共表表达式。方法如下:

-- Example data
DECLARE @users TABLE(UserID INT, creationDate DATETIME)
INSERT @users
        ( UserID, creationDate )
VALUES  ( 1, '2014-10-08 14:33:20.763'),
        (  2, '2014-10-09 04:24:14.283'),
         ( 3, '2014-10-10 18:34:26.260')


;WITH u1st AS (  -- determine the FIRST time the user appears
    SELECT UserID, MIN(creationDate) AS creationDate
    FROM @users
    GROUP BY UserID
),  hrs AS ( -- recursive CTE of start hours
    SELECT DISTINCT CAST(CAST(creationDate AS DATE) AS DATETIME) AS [StartHour] 
    FROM @users AS u
    UNION ALL
    SELECT DATEADD(HOUR, 1, [StartHour]) AS [StartHour] FROM hrs 
    WHERE DATEPART(HOUR,[StartHour]) < 23
), uGrp AS ( -- your data grouped by start hour
    SELECT -- note that DATETIMEFROMPARTS is only in SQL Server 2012 and later
        DATETIMEFROMPARTS(YEAR(CreationDate),MONTH(CreationDate), 
                     DAY(creationDate),DATEPART(HOUR, creationDate),0,0,0) 
                     AS StartHour, 
        COUNT(1) AS UserCount  FROM u1st AS u
    GROUP BY YEAR(creationDate), MONTH(creationDate), DAY(creationDate), 
             DATEPART(HOUR, creationDate)
)
SELECT hrs.StartHour, ISNULL(uGrp.UserCount, 0) AS UserCount 
FROM hrs LEFT JOIN uGrp ON hrs.StartHour = uGrp.StartHour
ORDER BY hrs.StartHour

NB - DATETIMEFROMPARTS is only in SQL SERVER 2012 and greater. If you are using an earlier version of SQL SERVER you could have

NB - DATETIMEFROMPARTS仅在SQL SERVER 2012和更大。如果您使用的是较早版本的SQL SERVER，您可以使用

WITH u1st AS ( -- determine the FIRST time the user appears
    SELECT UserID, MIN(creationDate) AS creationDate
    FROM @users
    GROUP BY UserID
),  hrs AS ( -- recursive CTE of start hours
    SELECT DISTINCT CAST(CAST(creationDate AS DATE) AS DATETIME) AS [StartHour] 
    FROM @users AS u
    UNION ALL
    SELECT DATEADD(HOUR, 1, [StartHour]) AS [StartHour] FROM hrs 
    WHERE DATEPART(HOUR,[StartHour]) < 23
), uGrp AS ( -- your data grouped by start hour
    SELECT -- note that DATETIMEFROMPARTS is only in SQL Server 2012 and later
        CAST(CAST(YEAR(creationDate) AS CHAR(4)) + '-'
             + RIGHT('0' + CAST(MONTH(creationDate) AS CHAR(2)), 2) + '-'
             + RIGHT('0' + CAST(DAY(creationDate) AS CHAR(2)), 2) + ' '
             + RIGHT('0' + CAST(DATEPART(HOUR, creationDate) AS CHAR(2)), 2) 
             + ':00:00.000'
             AS DATETIME) AS StartHour,
        COUNT(1) AS UserCount  FROM u1st AS u
    GROUP BY YEAR(creationDate), MONTH(creationDate), DAY(creationDate), 
             DATEPART(HOUR,creationDate)
)
SELECT hrs.StartHour, ISNULL(uGrp.UserCount, 0) AS UserCount 
FROM hrs LEFT JOIN uGrp ON hrs.StartHour = uGrp.StartHour
ORDER BY hrs.StartHour

#2

I asked a similar question on dba just this morning...https://dba.stackexchange.com/questions/86435/filling-in-date-holes-in-grouped-by-date-sql-data. You can used my GetSequence function, or create a Numbers table. I haven't done my own testing yet to validate what was suggested in my scenario.

就在今天早上，我对dba提出了一个类似的问题:https://dba.stackexchange.com/questions/86435/filling-in-date- holes-- in-date- by-date-sql-data。您可以使用我的GetSequence函数，或者创建一个数字表。我还没有进行自己的测试，以验证我的方案中建议的内容。

#3

Try this:

试试这个:

BUILD SAMPLE DATA

构建样本数据

CREATE TABLE yourTable(
    userId          INT,
    creationDate    DATETIME
)
INSERT INTO yourTable VALUES (1, '2014-10-08 14:33:20.763'), (2, '2014-10-09 04:24:14.283'),(3, '2014-10-10 18:34:26.260');

SOLUTION

WITH tally(N) AS(
    SELECT TOP(23) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns
)
,hourly(creationDate) AS(
    SELECT DATEADD(HOUR, t.N, d.creationDate)
    FROM tally t
    CROSS JOIN(
        SELECT DISTINCT DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0) AS creationDate FROM yourTable
    ) d
)
SELECT
    h.creationDate,
    userCount = ISNULL(t.userCount, 0)
FROM hourly h
LEFT JOIN(
    SELECT
        creationDate = DATEADD(HOUR, DATEPART(HOUR, creationDate) ,DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0)),
        userCount = COUNT(*)
    FROM yourTable
    GROUP BY DATEADD(DD, DATEDIFF(DD, 0, creationDate), 0), DATEPART(HOUR, creationDate)
)t
    ON t.creationDate = h.creationDate

CLEANUP

清理

DROP TABLE yourTable

#4

1. Create a temporary table (let's say #CreationDateHours) containing create date and hours from 0 to 23.

   创建一个临时表(比如#CreationDateHours)，其中包含从0到23的创建日期和小时。

   Declare @date as date
   SELECT MAX(CAST(creationDate as date)) AS ForDate, 0 as OnHour into #CreationDateHours
       FROM Table
           WHERE
              primaryKey = 123
       Select @date=ForDate from #CreationDateHours 
       Declare @i int
       Set @i=1
       While @i<24
       begin
       insert into #CreationDateHours
       select @date as ForDate, @i as OnHour 
       set @i+=1
       end
   

2. Now, Run this query to get the desired results

   现在，运行此查询以获得所需的结果

   select t1.ForDate, t1.OnHour, isnull(t2.Totals,0) AS Totals
   from
       #CreationDateHours t1 left join (SELECT 
           CAST(creationDate as date) AS ForDate, 
           DATEPART(hour, date) AS OnHour, 
           COUNT(distinct userId) AS Totals
       FROM 
           Table
       WHERE
           primaryKey = 123
       GROUP BY 
           CAST(creationDate as date), DATEPART(hour, createDate)) as t2
   on t1.ForDate= t2.ForDate and t1.OnHour=t2.OnHour