sql - 我如何找到高峰车辆和高峰时间

我有一张桌子，上面有公共交通工具的记录。我需要知道高峰时段什么时候出车以及多少辆车。
日期范围将是一个会计年度，从7/1 / yyy到6/30 / yyyy。
我的表称为fixedrouterecords，这是相关列的示例。

我曾尝试为高峰用户使用一些示例，但未能达到目标。

service_date    bus leave_yard  return_to_yard
 2016-10-24     104  05:15:00    06:30:00
 2016-10-24     204  04:10:00    06:30:00

当然一个会计年度有数千行

最佳答案

这将在服务日产生高峰时间

Declare @YourTable table (service_date date,bus int,leave_yard Time,return_to_yard time)
Insert Into @YourTable values
('2016-10-24',104,'05:15:00','06:30:00'),
('2016-10-24',204,'04:10:00','06:30:00'),
('2016-10-25',997,'05:15:00','06:30:00'),
('2016-10-25',998,'04:10:00','06:30:00'),
('2016-10-25',999,'05:30:00','06:00:00'),
('2016-10-26',999,'06:30:00','07:15:00')

;with cte0(N) as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
    , cteT(T) as (Select Top 1440 cast(DateAdd(MI,Row_Number() over (Order By (Select NULL))-1,'1900-01-01') as time) From cte0 N1, cte0 N2, cte0 N3, cte0 N4)
    , cteBase as (
         Select service_date
               ,bus
               ,OutTime = T
         From   @YourTable A
         Join   cteT B
         on     T between leave_yard and return_to_yard
         Where  Year(service_date)=2016  -- or any other filter you like
      )
Select A.Service_Date
      ,A.MinOut
      ,A.MaxOut
      ,Busses = count(Distinct B.Bus)
 From (
        Select Service_Date
              ,MinOut = Min(A.OutTime)
              ,MaxOut = Max(A.OutTime)
         From (
                Select *,Rnk=Rank() over (Partition By Service_Date Order by Hits Desc)
                 From (Select Service_Date,OutTime,Hits=count(*) From cteBase Group by Service_Date,OutTime ) A
               ) A
         Where Rnk=1
         Group By Service_Date
      ) A
 Join cteBase B on A.Service_Date=B.Service_Date and B.OutTime between A.MinOut and A.MaxOut
 Group By A.Service_Date,A.MinOut,A.MaxOut

这显示了服务日的PEAK使用情况

Service_Date    MinOut   MaxOut    Busses
2016-10-24     05:15:00  06:30:00  2
2016-10-25     05:30:00  06:00:00  3
2016-10-26     06:30:00  07:15:00  1

关于sql - 我如何找到高峰车辆和高峰时间，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40267754/