如何计算车辆的高峰数量和高峰时间

I have a table containing records relating to public transportation. I need to know what time of day the peak vehicles were out and how many vehicles. The date range would be a fiscal year, from 7/1/yyyy to 6/30/yyyy. My table is called fixedrouterecords and here is a sample of the relevant columns.

我有一张表格，上面有关于公共交通的记录。我需要知道高峰时段的车停了，有多少辆车停了。日期范围为财政年度，从7/1/yyyy到6/30/yyy。我的表叫做fixedrouterecords，这里是相关列的一个样本。

I have tried using some of the examples for peak users, but could not get there.

我尝试过使用一些峰值用户的例子，但是没有成功。

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

of course there are thousands of lines for a fiscal year

当然，在一个财政年度里有成千上万条线。

6 个解决方案

#1

This will generate Peak Times by service day

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

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

This shows PEAK Usage by Service Day

这显示了服务日的峰值使用率

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

#2

select      service_date
           ,vehicles_out
           ,ts              as from_time
           ,next_ts         as to_time

from       (select      t.*
                       ,rank () over (partition by service_date order by vehicles_out desc) as rnk

            from       (select      service_date
                                   ,ts
                                   ,sum  (op) over (partition by service_date order by ts,op,bus)   as vehicles_out
                                   ,lead (ts) over (partition by service_date order by ts,op,bus)   as next_ts

                        from        (           select service_date ,bus ,leave_yard     as ts , 1 as op from fixedrouterecords 
                                    union all   select service_date ,bus ,return_to_yard       ,-1       from fixedrouterecords 
                                    ) t
                        ) t
            ) t

where       rnk = 1
;

#3

EDIT - Removed the need for the UDF and added an ad-hoc tally

编辑—删除了对UDF的需要，并添加了一个临时计数

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')

;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             -- << Replace with Your Table Name
         Join   cteT B
         on     T between leave_yard and return_to_yard
         Where  Year(service_date)=2016  -- << Or any other filter you like  
      )
Select MinOut 
      ,MaxOut
      ,Busses = count(Distinct bus)
 From (
        Select Top 1 
               Hits
              ,MinOut=min(OutTime)
              ,MaxOut=max(OutTime)
         From  (Select OutTime,Hits=count(*) from cteBase group by OutTime) A
         Group By Hits
         Order By Hits Desc
       ) A
 Join cteBase B on (OutTime between MinOut and MaxOut)
 Group By MinOut,MaxOut

Returns

MinOut      MaxOut      Busses
05:15:00    06:30:00    2

#4

This may not be the best way but I think it should work. Assuming you have a 24 hour window for this (based on limited info) I would use a view or a CTE and create a column for duration and for each hour of the day (ie 01,02,03 etc)

这可能不是最好的方法，但我认为它应该有效。假设您有一个24小时的窗口(基于有限的信息)，我将使用一个视图或CTE，为持续时间和一天中的每个小时创建一个列(即01、02、03等等)

Then I would use the leave_yard + Duration to determine for what hours in the day the bus was out (ie the formula to populate the value for 01 is)

然后，我将使用leave_yard +持续时间来确定公共汽车在一天内的时间(即计算01的值的公式)

 Case WHEN leave_yard > 01:00 AND leave_yard < (leave_yard + duration) THEN '1' ELSE '0' END AS '01'

So you set each hour column for that day as 1 or 0 then sum the hours so you get the number of buses out in each hour each day. Then compare the hours either with another expression and roll up with aggregates etc

所以你把每小时的时间设为1或0，然后加上小时，这样你就可以每天在每个小时内得到公共汽车的数量。然后将小时数与另一个表达式进行比较，然后用聚合语句卷起来

#5

SELECT phour AS peak_hour,
       bus_cnt AS peak_count
  FROM
     (
       SELECT *,
              ROW_NUMBER() OVER ( ORDER BY bus_cnt DESC ) AS rnk       
         FROM 
            (
               SELECT cast( DATEPART( hour, leave_yard ) as varchar )+ ':00:00' AS phour,
                      COUNT( bus ) AS bus_cnt 
                 FROM fixedrouterecords
                GROUP BY phour
             ) A
    ) B

WHERE rnk = 1 ;

其中rnk = 1;

#6

SELECT phour AS peak_hour, bus_cnt AS peak_count FROM ( SELECT *, ROW_NUMBER() OVER ( ORDER BY bus_cnt DESC ,partition by bus_cnt ) AS rnk FROM ( SELECT cast( DATEPART( hour, leave_yard ) as varchar )+ ':00:00' AS phour, COUNT( bus ) AS bus_cnt FROM fixedrouterecords GROUP BY cast( DATEPART( hour, leave_yard ) )y )z WHERE z.rnk = 1;

选择phour作为peak_hour bus_cnt peak_count从(SELECT *,ROW_NUMBER()(由bus_cnt bus_cnt DESC秩序,分区)的rnk(选择演员(DATEPART(小时,leave_yard)作为varchar)+“:00:00”phour,计数(总线)从fixedrouterecords bus_cnt GROUP BY铸造(DATEPART(小时,leave_yard))y)z z。rnk = 1;

#1