Geek Answers Handbook

Grouping lines based on the "difference" between the lines

I have a table that contains the start time (using the number in the example to keep it simple) and the duration of the events.

I would like to define the “blocks” and their beginning and end.
Whenever the difference between the end time (start time + duration) of the previous line (sorted by start time) and the start time of the current line is equal >=5, a new “block” must begin.

This is my test data, including an attempt at a graphical explanation in the comments:

WITH test_data AS (
  SELECT  0 s, 2 dur FROM dual UNION ALL   --# ■■
  SELECT  2  , 2     FROM dual UNION ALL   --#   ■■
  SELECT 10  , 1     FROM dual UNION ALL   --#           ■
  SELECT 13  , 4     FROM dual UNION ALL   --#              ■■■■
  SELECT 15  , 4     FROM dual             --#                ■■■■
)
--# Should return
--#   0 ..  4                              --# ■■■■
--#  10 .. 19                              --#           ■■■■■■■■■

The first block starts with 0and ends with 4. Since the difference in the next line is equal >=5, run another block in 10, which ends with 19.

, LAG, , .

PL/SQL-, .

?

,

+5
4

:

SQL> WITH test_data AS (
  2    SELECT  0 s, 2 dur FROM dual UNION ALL   --# ■■
  3    SELECT  2  , 2     FROM dual UNION ALL   --#   ■■
  4    SELECT 10  , 1     FROM dual UNION ALL   --#           ■
  5    SELECT 13  , 4     FROM dual UNION ALL   --#              ■■■■
  6    SELECT 15  , 4     FROM dual             --#                ■■■■
  7  )
  8  SELECT MIN(s) "begin", MAX(s + dur) "end"
  9    FROM (SELECT s, dur, SUM(gap) over(ORDER BY s) my_group
 10             FROM (SELECT s, dur,
 11                           CASE
 12                              WHEN lag(s + dur) over(ORDER BY s) >= s - 5 THEN
 13                               0
 14                              ELSE
 15                               1
 16                           END gap
 17                      FROM test_data
 18                     ORDER BY s))
 19   GROUP BY my_group;

     begin        end
---------- ----------
         0          4
        10         19
+3

.. , , , .

!

WITH test_data AS (
  SELECT  0 s, 2 dur FROM dual UNION ALL   --# ■■
  SELECT  2  , 2     FROM dual UNION ALL   --#   ■■
  SELECT 10  , 1     FROM dual UNION ALL   --#           ■
  SELECT 13  , 4     FROM dual UNION ALL   --#              ■■■■
  SELECT 15  , 4     FROM dual             --#                ■■■■
)
select 
-- Group on each block
  min(start_time) as s, 
  max(end_time) - min(start_time) as dur
from (
  select 
    start_time,
    duration, 
    end_time, 
-- number the blocks sequentially 
    sum(is_block_start) over (order by start_time) as block_num
  from (
    select 
      start_time, 
      duration, 
      end_time, 
-- Mark the start of each block
      case 
        when nvl2(prev_end_time, start_time - prev_end_time,5) >= 5 
        then 1 else 0 end as is_block_start
    from (
      select 
        s as start_time, 
        dur as duration, 
        s+dur as end_time,
        lag(s+dur) over (order by s) prev_end_time
      from test_data
    )
  )
)
group by block_num
+2

MS-SQL ROW_NUMBER() OVER(ORDER BY starttime) AS Rank .

, NULL ( ).

, , , , :

blockstarttime1 nextstarttime1 (=starttime2)
blockstarttime2 nextstarttime2 (=starttime3)
blockstarttime3 NULL

, WHERE starttime BETWEEN blockstarttime and nextstarttime .

Oracle...

+1
source

There is a fantastic book by Richard Snodgrass that can help: SQL Server (), .

See the Richards page for links to some corrections in the book and the associated zip-formatted CD-ROM.

+1
source

More articles:


All Articles