Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
667 views
in Technique[技术] by (71.8m points)

excel - SQL Group Data within the date difference of 90 seconds

I have sample data like

Element Time Stamp
A 21/12/2020 06:10:56
B 21/12/2020 06:05:27
B 21/12/2020 06:06:10
A 21/12/2020 06:11:27
A 21/12/2020 06:05:27
A 21/12/2020 06:06:20
A 21/12/2020 06:12:30

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

If I understand correctly, you want "islands" that are separated by 90 seconds. If so, you can use lag() and a cumulative sum with aggregation:

select element, min(timestamp), max(timestamp), count(*)
from (select t.*,
             sum(case when prev_timestamp > dateadd(second, -90, timestamp) then 0 else 1 end) over (partition by element order by timestamp) as grp
      from (select t.*,
                   lag(timestamp) over (partition by element order by timestamp) as prev_timestamp
            from t
           ) t
     ) t
group by grp, element;

Here is a db<>fiddle.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...