代码之家 › 专栏 › 技术社区 › Zaynul Abadin Tuhin

每小时(24小时)计算日期

sql-server sql

-3

Zaynul Abadin Tuhin · 技术社区 · 6 年前

我有一个数据集,其中客户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

2 回复 | 直到 6 年前

Tyron78 6 年前

这里有一个方法-也许这已经解决了你的问题。我设计它是为了解决加入和离开之间的任何一天差异。但是,我不能告诉任何关于更大数据集的性能,因为我只使用您的示例进行了测试,如果涉及到更大的数据集,对所有相关时间的评估可能需要更长的时间。无论如何,我在这里使用递归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)

George Menoutis 6 年前

试试这个:

;WITH hourlist(starthour) AS (
  SELECT 0     -- Seed Row
  UNION ALL
  SELECT starthour + 1 -- Recursion
  FROM hourlist
  where starthour+1<=23
)
SELECT 
    day
    ,convert(nvarchar,starthour)+'-'+convert(nvarchar,case when starthour+1=24 then 0 else starthour+1 end) hourtitle
    ,count(distinct customer_id) 'customer count'
FROM 
    hourlist h -- list of all hourse
    cross join 
    (
        select distinct dateadd(day,datediff(day,0, joinTime),0) from #login_out_logs 
        union
        select distinct dateadd(day,datediff(day,0,leaveTime),0) from #login_out_logs
    )q10(day)  -- list of all days of jointime and leavetime
    inner join #login_out_logs l on -- log considered for specific day/hour if starts before hourend and ends before hourstart
        l.joinTime <dateadd(hour,starthour+1,q10.day)
        and
        l.leaveTime>=dateadd(hour,starthour  ,q10.day)
group by day,starthour
order by day,starthour

注意:这只适用于0或1天不同的加入时间和离开时间,而不是2天或更多。