时间戳

首页 > 解决方案 > 日期明智的每小时（24 小时）客户计数

sql - 日期明智的每小时（24 小时）客户计数

问题描述

我有一个数据集，其中客户 ID、客户加入时间和离开时间可用。我想按小时计算每个日期客户这里是示例数据集在此处输入图像描述

我的预期输出

在这里，我将添加我尝试过的代码片段，其中第一个创建了 24 小时跨度，然后尝试加入和聚合函数以获得预期结果并获得当前日期，但我需要任何日期，即动态

select logdate as date,timespan,count(customer_id)
(
SELECT userid,cast(joinTime as date) as logdate,customer_id
,starttime,endtime,timespan
FROM login_out_logs AS logTable 
left join 

(select '00:00:00 - 01:00:00' timespan,DATEadd(hh,0,cast(dateadd(dd,-1,getdate()))) starttime,dateadd(hh,1,cast(dateadd(dd,-1,getdate()))) endtime
union 
select '01:00:00 - 02:00:00', dateadd(hh,1,cast(dateadd(dd,-1,getdate()))),dateadd(hh,2,cast(dateadd(dd,-1,getdate())))
union 
select '02:00:00 - 03:00:00', dateadd(hh,2,cast(dateadd(dd,-1,getdate()))),dateadd(hh,3,cast(dateadd(dd,-1,getdate())))
union 
select '03:00:00 - 04:00:00', dateadd(hh,3,cast(dateadd(dd,-1,getdate()))),dateadd(hh,4,cast(dateadd(dd,-1,getdate())))
union 
select '04:00:00 - 05:00:00', dateadd(hh,4,cast(dateadd(dd,-1,getdate()))),dateadd(hh,5,cast(dateadd(dd,-1,getdate())))
union 
select '05:00:00 - 06:00:00',dateadd(hh,5,cast(dateadd(dd,-1,getdate()))),dateadd(hh,6,cast(dateadd(dd,-1,getdate())))
union 
select '06:00:00 - 07:00:00',dateadd(hh,6,cast(dateadd(dd,-1,getdate()))),dateadd(hh,7,cast(dateadd(dd,-1,getdate())))
union 
select '07:00:00 - 08:00:00',dateadd(hh,7,cast(dateadd(dd,-1,getdate()))),dateadd(hh,8,cast(dateadd(dd,-1,getdate())))
union 
select '08:00:00 - 09:00:00',dateadd(hh,8,cast(dateadd(dd,-1,getdate()))),dateadd(hh,9,cast(dateadd(dd,-1,getdate())))
union 
select '09:00:00 - 10:00:00',dateadd(hh,9,cast(dateadd(dd,-1,getdate()))),dateadd(hh,10,cast(dateadd(dd,-1,getdate())))
union 
select '10:00:00 - 11:00:00',dateadd(hh,10,cast(dateadd(dd,-1,getdate()))),dateadd(hh,11,cast(dateadd(dd,-1,getdate())))
union 
select '11:00:00 - 12:00:00',dateadd(hh,11,cast(dateadd(dd,-1,getdate()))),dateadd(hh,12,cast(dateadd(dd,-1,getdate())))
union 
select '12:00:00 - 13:00:00',dateadd(hh,12,cast(dateadd(dd,-1,getdate()))),dateadd(hh,13,cast(dateadd(dd,-1,getdate())))
union 
select '13:00:00 - 14:00:00',dateadd(hh,13,cast(dateadd(dd,-1,getdate()))),dateadd(hh,14,cast(dateadd(dd,-1,getdate())))
union 
select '14:00:00 - 15:00:00',dateadd(hh,14,cast(dateadd(dd,-1,getdate()))),dateadd(hh,15,cast(dateadd(dd,-1,getdate())))
union
select '15:00:00 - 16:00:00',dateadd(hh,15,cast(dateadd(dd,-1,getdate()))),dateadd(hh,16,cast(dateadd(dd,-1,getdate())))
union
select '16:00:00 - 17:00:00',dateadd(hh,16,cast(dateadd(dd,-1,getdate()))),dateadd(hh,17,cast(dateadd(dd,-1,getdate())))
union
select '17:00:00 - 18:00:00',dateadd(hh,17,cast(dateadd(dd,-1,getdate()))),dateadd(hh,18,cast(dateadd(dd,-1,getdate())))
union 
select '18:00:00 - 19:00:00',dateadd(hh,18,cast(dateadd(dd,-1,getdate()))),dateadd(hh,19,cast(dateadd(dd,-1,getdate())))
union
select '19:00:00 - 20:00:00',dateadd(hh,19,cast(dateadd(dd,-1,getdate()))),dateadd(hh,20,cast(dateadd(dd,-1,getdate())))
union
select '20:00:00 - 21:00:00',dateadd(hh,20,cast(dateadd(dd,-1,getdate()))),dateadd(hh,21,cast(dateadd(dd,-1,getdate())))
union
select '21:00:00 - 22:00:00',dateadd(hh,21,cast(dateadd(dd,-1,getdate()))),dateadd(hh,22,cast(dateadd(dd,-1,getdate())))
union
select '22:00:00 - 23:00:00',dateadd(hh,22,cast(dateadd(dd,-1,getdate()))),dateadd(hh,23,cast(dateadd(dd,-1,getdate())))
union
select '24:00:00 - 00:00:00',dateadd(hh,23,cast(dateadd(dd,-1,getdate()))),dateadd(hh,23,dateadd(mi,59,cast(dateadd(dd,-1,getdate())))))a
on starttime between jointime and leaveTime
or endtime between jointime and leaveTime
or jointime>=starttime and jointime<endtime

) as T
group by leaveTime,timespan



Date             Hour   customer_count
  2018-01-01      8-9    1       
  2018-01-01      9-10   1
  2018-01-01      10-11  1 
  2018-01-01      11-12  1
  2018-01-01      12-13  1
  2018-01-01      13-14  1
  2018-01-01      14-15  1
  2018-01-01      15-16  1
  2018-01-01      16-17  1
  2018-01-01      17-18  1
  2018-01-01      18-19  1
  2018-01-01      19-20  1
  2018-01-01      20-21  2
  2018-01-01      21-22  3
  2018-01-01      22-23  2
  2018-01-01      23-00  1

标签： sqlsql-server

解决方案

这是一种方法-也许这已经解决了您的问题。我设计它是为了处理加入和离开之间的任何日差。但是，我无法说明更大数据集的性能，因为我仅使用您的示例进行了测试，如果涉及到更大的数据集，对所有相关时间的评估可能需要更长的时间。无论如何，我在这里使用了递归 cte 来评估加入和离开之间的所有时间，稍后我按日期和时间分组：

DECLARE @Cust TABLE(
  customer_id INT,
  joinTime DATETIME,
  leaveTime DATETIME
)

INSERT INTO @Cust VALUES
  (536, '2018-01-01 08:05:00', '2018-01-01 18:31:00'),
  (344, '2018-01-01 19:37:00', '2018-01-01 20:16:00'),
  (344, '2018-01-01 19:49:00', '2018-01-01 20:00:00'),
  (899, '2018-01-01 20:49:00', '2018-01-01 21:14:00'),
  (2336, '2018-01-01 21:02:00', '2018-01-01 21:03:00'),
  (335, '2018-01-01 21:03:00', '2018-01-01 23:43:00'),
  (2336, '2018-01-01 21:03:00', '2018-01-02 00:06:00'),
  (899, '2018-01-01 21:18:00', '2018-01-01 22:24:00'),
  (345, '2018-01-01 21:21:00', '2018-01-01 21:39:00'),
  (345, '2018-01-01 21:53:00', '2018-01-02 00:13:00');

;WITH cte AS(
SELECT  c.customer_id,
        c.joinTime,
        c.leaveTime,
        c.joinTime x
  FROM @Cust c
UNION ALL
SELECT  c.customer_id,
        c.joinTime,
        c.leaveTime,
        DATEADD(HOUR, 1, x) x
  FROM cte c
  WHERE DATEADD(HOUR, 1, x) <= CASE WHEN DATEPART(MINUTE, x) < DATEPART(MINUTE, c.leaveTime) THEN c.leaveTime ELSE DATEADD(HOUR, 1, c.leaveTime) END
)
SELECT CONVERT(DATE, x) AS cDate, DATEPART(HOUR, x) AS cHour, COUNT(*) AS cCount
  FROM cte
  GROUP BY CONVERT(DATE, x), DATEPART(HOUR, x)
  ORDER BY 1,2
  OPTION (MAXRECURSION 0)

推荐阅读