sql - 查询以获取一天中每个小时的结果，甚至数据(如果不存在)

Question

我正在尝试获取表中每天每小时的用户数的查询。如果该小时的数据不存在，我想记录计数为零的小时。此外，用户仅应在第一次进入时才被计数。应忽略后续条目。

表:

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

期望的输出:

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

我的尝试:

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)

这仅给出每小时存在的记录。不是缺失时间的数据。我认为有一种方法可以通过使用交叉连接来获取 0 数据，即使是在缺失的时间里。

我遇到过这样的事情，但无法用它构建正确的查询。

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

再次强调，我不是 SQL 专家，但正在努力构建这个结果集。

再一次尝试:

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

Answer 1

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

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

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

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