Group on time interval

Analytics

Group on time interval

The ask is to group the items within a specific time interval say 1 min.

Below are the items with associated time. We start with the minimum time and try to group all items within 1 minute of the minimum time.

Expectation is

So M1 till M6 are under one group as they all fall under 1 min.

Next min time is for M7 and if we add a minute to it there no other item. So M7 remains as is and so is M8.

Then the next minimum time is for M9 and if we add a minute to it M9 till M13 are under one group. 

M121:01:10
M221:01:20
M321:01:30
M421:01:40
M521:01:50
M621:02:10
M721:02:11
M821:03:11
M921:05:25
M1021:05:35
M1121:05:55
M1221:06:10
M1321:06:25

 

How can we have a recursive grouping logic to get the result as mentioned?

  • Group
  • Interval
1 REPLY
Junior Contributor

Re: Group on time interval

Your logic is not correct, is it less than or up to 60 seconds?

 

So M1 till M6 are under one group as they all fall under 1 min.

21:01:10 -> 21:02:10 = 60 seconds 

 

Next min time is for M7 and if we add a minute to it there no other item. So M7 remains as is and so is M8.

M7 21:02:11 -> M8 21:03:11 = 60 seconds

 


How many rows do you need to process?

Are there many gaps over 60 seconds between rows?

What's the average & maximm number of rows per group?

Do you need to assign a number to each group or just the same time?

Do you want to run this as a single Select or can you use Volatile Tables?