Calculate frequencies over deciles

user_16188953 0
Viewed 121

Let's say I have SQL Server tables detailing marathon races, all of them alike. Each row is a participant and one of the columns is the finish time, [dbo].[marathon_X].[finish]. Let's assume [finish] is counted in minutes (int) for simplicity.

I'm stuck and could use help figuring out a query that divides the finish times for a certain race into deciles and counts the number of participants finishing within each decile. This so I can get an idea of the frequency distribution over different time segments (which, in this case, I expect to be something other than even, or normal for that matter).

So for example if the winner in a certain race finishes after 130 min and the last participant after 520 minutes, then in that particular race each finish time decile would be (MAX(finish) - MIN(finish))/10 = 39 min wide. And then 1st decile would be finish times within 130-168 min, 2nd would be 169-207 min, etc. I then need a count of the number of runners in each decile, the range of which will vary from race to race.

Probably stupidly simple but I haven't figured out how to NTILE this (or equivalent).

quantile frequency tsql sql-server
1 Answers

Not sure if you were looking to aggregate the final results, but that would be a small matter in the final select.

The cte will create the desired ranges. Then it becomes a small matter of joining the two sets of data.

Example

Declare @YourTable table (Participant int, FinishTime int)
Insert Into @YourTable values
 (1,130)  -- Top 
,(2,510)  -- Last
,(3,150)  -- Random ...
,(4,255)
,(5,460)
,(6,325)

;with cte as (
      Select Decile
            ,R1  = (MinV) + ( RngV * (Decile-1)) + (Decile-1)
            ,R2  = (MinV) + ( RngV * (Decile-1)) + (Decile-1) + RngV
       From  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) A(Decile)
       Cross Join ( Select MinV = min(FinishTime)
                          ,MaxV = max(FinishTime)
                          ,RngV = (max(FinishTime)-min(FinishTime))/10  
                     From  @YourTable ) B
)
Select A.*
      ,B.Decile
 From  @YourTable A
 Join  cte B on FinishTime between R1 and R2
 Order By FinishTime

Results

Participant FinishTime  Decile
1           130         1
3           150         1
4           255         4
6           325         6
5           460         9
2           510         10
user_15700000
Related