SQL Server 按日期和日期范围内的一天中的时间分组

Question

我什至不确定这是否可以/应该通过 SQL 来完成，但这里是。

我有一个像这样存储开始日期和结束日期的表

userPingId    createdAt                    lastUpdatedAt
1             2017-10-17 11:31:52.160      2017-10-18 14:31:52.160

我想返回一个结果集，该结果集按日期对结果进行分组，以及它们是否在两个日期之间的不同点之间处于事件状态。

不同点是

• 上午 - 中午 12 点之前
• 下午 - 中午 12 点到下午 5 点
• 晚上 - 下午 5 点之后

所以例如我会得到以下结果

sessionDate    morning    afternoon    evening
2017-10-17     1          1            1
2017-10-18     1          1            0

这是我到目前为止所拥有的，我相信它非常接近，但事实上我无法获得我需要的结果让我认为这在 SQL 中可能是不可能的(顺便说一句，我正在使用数字查找表在我在另一个教程中看到的查询中)

DECLARE @s DATE = '2017-01-01', @e DATE = '2018-01-01';
;WITH d(sessionDate) AS
(
  SELECT TOP (DATEDIFF(DAY, @s, @e) + 1) DATEADD(DAY, n-1, @s) 
  FROM dbo.Numbers ORDER BY n
)
SELECT 
d.sessionDate,
sum(case when 
(CONVERT(DATE, createdAt) = d.sessionDate AND datepart(hour, createdAt) < 12) 
OR (CONVERT(DATE, lastUpdatedAt) = d.sessionDate AND datepart(hour, lastUpdatedAt) < 12) 
then 1 else 0 end) as Morning,
sum(case when 
(datepart(hour, createdAt) >= 12 and datepart(hour, createdAt) < 17)
OR (datepart(hour, lastUpdatedAt) >= 12 and datepart(hour, lastUpdatedAt) < 17) 
OR (datepart(hour, createdAt) < 12 and datepart(hour, lastUpdatedAt) >= 17)
then 1 else 0 end) as Afternoon,
sum(case when datepart(hour, createdAt) >= 17 OR datepart(hour, lastUpdatedAt) >= 17 then 1 else 0 end) as Evening
FROM d
LEFT OUTER JOIN MYTABLE AS s
ON s.createdAt >= @s AND s.lastUpdatedAt <= @e
AND (CONVERT(DATE, s.createdAt) = d.sessionDate OR CONVERT(DATE, s.lastUpdatedAt) = d.sessionDate)
WHERE d.sessionDate >= @s AND d.sessionDate <= @e
AND userPingId = 49
GROUP BY d.sessionDate
ORDER BY d.sessionDate;

Answer 1

在您开始使用数字表的基础上，您可以使用另一个 common table expression 将时间范围添加到临时日历表中使用 cross apply() 和 table value constructor (values (...),(...)) .

从那里，您可以使用 inner join基于重叠的日期范围以及条件聚合来调整结果:

declare @s datetime = '2017-01-01', @e datetime = '2018-01-01';

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (  /* adhoc date/numbers table */
  select top (datediff(day, @s, @e)+1) 
      SessionDate=convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,@s))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by SessionDate
)
, h as ( /* add time ranges to date table */
  select 
      SessionDate
    , StartDateTime = dateadd(hour,v.s,SessionDate)
    , EndDateTime   = dateadd(hour,v.e,SessionDate)
    , v.point
  from d
    cross apply (values 
        (0,12,'morning')
       ,(12,17,'afternoon')
       ,(17,24,'evening')
      ) v (s,e,point)
)

select
    t.userPingId
  , h.SessionDate
  , morning = count(case when point = 'morning' then 1 end)
  , afternoon = count(case when point = 'afternoon' then 1 end)
  , evening = count(case when point = 'evening' then 1 end)
from t
  inner join h
    on t.lastupdatedat >= h.startdatetime
   and h.enddatetime   > t.createdat 
group by t.userPingId, h.SessionDate

rextester 演示:http://rextester.com/MVB77123

返回:

+------------+-------------+---------+-----------+---------+
| userPingId | SessionDate | morning | afternoon | evening |
+------------+-------------+---------+-----------+---------+
|          1 | 2017-10-17  |       1 |         1 |       1 |
|          1 | 2017-10-18  |       1 |         1 |       0 |
+------------+-------------+---------+-----------+---------+

---

或者，您可以使用 pivot()而不是最终的条件聚合 select :

select UserPingId, SessionDate, Morning, Afternoon, Evening
from (
  select
      t.userPingId
    , h.SessionDate
    , h.point
  from t
    inner join h
      on t.lastupdatedat >= h.startdatetime
     and h.enddatetime   > t.createdat 
  ) t
  pivot (count(point) for point in ([Morning], [Afternoon], [Evening])) p

rextester 演示:http://rextester.com/SKLRG63092