SQL Server 查询 - 两个日期之间每小时记录出现次数总和的子查询结构

Question

很难用标题描述，但这就是我要做的。

我有一个名为"访问"的表，其中包含人们访问某个地方的记录：

| Id | EntryTime | Duration | Contact_Id (Ignore this)

Duration是小时数的双倍类型。

Entrytime是一个日期时间字段。

从EntryTime和Duration我们可以推断出人存在的窗口。

因此，我想制作的是一个表格，它平均了在某个日期期间每个小时间隔在场的总人数。仅从此访问表。

因此，为了更清楚地考虑以下 9 次访问的数据集(昨天 4 次，今天 5 次(：

| Id |      EntryTime      | Duration
  1    2016-05-09 09:00:00      5.5
  2    2016-05-09 10:00:00      5.5
  3    2016-05-09 11:00:00      5.5
  4    2016-05-09 12:00:00      5.5
  5    2016-05-10 09:00:00      5.5
  6    2016-05-10 10:00:00      5.5
  7    2016-05-10 11:00:00      5.5
  8    2016-05-10 11:00:00      5.5
  9    2016-05-10 12:00:00      5.5

然后我想产生一个看起来像这样的结果：

| Hour_of_Day | Total_People
       0             0
       1             0
       2             0
       3             0
       4             0
       5             0
       6             0
       7             0
       8             0
       9             1      //because on both days one person was present at 9am.
      10             2      //because on both days two people were present at 10am.
      11             3.5    //because yesterday 1 extra came at 11, and today 2 people did.
                              This averages out at 1.5.
      12             4.5    //one more entered at 12 on both days.
      13             4.5    
      14             4.5
      15             3.5    //On both days one person left at 2:30pm.
      16             2.5    //And at 3:30pm.
      17             1      //1.5 people on average left at 4:30pm.
      18             0      //Everyone had left by 5:30pm so we have no people present 6pm (18:00) onwards.
      19             0
      20             0
      21             0
      22             0
      23             0

我认为这是一件非常困难的事情，而且我非常缺乏SQL经验。

我的流程是：

• 创建编号规则 0-23 作为HourOfDay。

• 简单的 where 语句，用于获取日期范围内的Visits。

• 类似datename(hh, EntryTime) as EntryHour进入的东西。

• datename(hh, EntryTime) + Duration as LeavingTime离开时间。

• 某种条件案例语句，如果HourOfDay > EntryHour and HourOfDay <= LeavingTime then 1 else 0等作为No_Of_People并计算这些事件。

我认为这个流程有效，但我正在努力将其拼凑在一起。

让我知道我是否解释得很好，以及我想要的结果是否现实。如果没有，也许我应该找到解决这个问题的另一种方法。

Answer 1

WITH VisitorsPerDayHour AS (
    SELECT CONVERT(date, EntryTime) AS [Day]
          ,[Hour]
          ,SUM(CASE WHEN [Hour] - DATEPART(hh, EntryTime) BETWEEN 0 AND Duration THEN 1.0 ELSE 0.0 END), 0) AS Visitors
    FROM Visitors
         CROSS APPLY (
              VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
                    ,(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)
                    ,(20),(21),(22),(23)
         ) AS CA1([Hour])
    GROUP BY CONVERT(date, EntryTime)
            ,[Hour]
)
SELECT [Hour] AS Hour_of_Day
      ,AVG(Visitors) AS Total_People
FROM VisitorsPerDayHour
GROUP BY [Hour]
ORDER BY [Hour]

请注意，这仅在至少一个访问者的天数内平均。

Answer 2

首先创建一个中间表来使用递归 with 语句存储小时数

with HoursOfDay as(
    SELECT 0 AS hour
    UNION ALL
    SELECT hour+1 from HoursOfDay WHERE hour < 24
),

然后我们可以全部准备好进行所有计算，但分两步完成会更容易。我们现在连接两个表并找出访问何时开始以及每小时何时结束，因为 MIN 和 MAX 在这里没有按照我们想要的方式工作，我们使用 CASE WHEN 语句：

Visits as(
   SELECT hour, 
        CASE WHEN peopleVisit.EntryTime < hour THEN hour 
            else peopleVisit.EntryTime end as StartTime,
        CASE WHEN peopleVisit.EntryTime + peopleVisit.Duration >hour +1 THEN hour 
            else peopleVisit.EntryTime + peopleVisit.Duration end as EndTime
   FROM peopleVisit INNER JOIN HoursOfDay WHERE peopleVisit.EntryTime < hour+1 and peopleVisit.EntryTime + peopleVisit.Duration >hour 
)

现在我们能够得到想要的结果

SELECT hour, SUM(EndTime-StartTime) from Visits group by hour

我没有测试它，因为我在这里无法访问任何 SQL 数据库，但这应该可以工作。