Grouping when using analytic functions

Question

Let's suppose we have a table that looks like this:

Level|Depth|Descrip|  
  0  |  0  | Base    |  
  1  |  50 | Level_1 |  
  2  |  53 | Level_2 |  
  3  |  60 | Level_3 |  
  8  |  80 | Level_8 |  
  10 |  81 | Level_10|    
  15 | 101 | Level_15|  
  16 | 102 | Level_16|  
  17 | 102 | Level_16_bis|  
  18 | 103 | Level_17|

I need, in first place, to get the rows that represent significative(more than 15 mts) depth jump respecting the previous ones. I get those rows doing something like this:

Select level,depth, descrip from(
    Select level
    , depth
    ,lag(depth) over (order by level asc) as prev_depth
    , descrip
    from ground_levels
    )
Where abs(depth-prev_depth) > 15 and depth > 0

Which give me a table like this:

Level|Depth|Descrip|  
  1  | 50  | Level_1|  
  8  | 80  | Level_8|  
 15  | 101 | Level_15|

Now, I need to collect the levels that falls in between the jumps. So, I need something like this:

Level|Depth| Descrip | Equivalent_levels |  
  1  | 50  | Level_1 |     2,3           |  
  8  | 80  | Level_8 |     10            |  
 15  | 101 | Level_15|     16,17,18      |

I have being doing some searching about use "listagg", rank() and other analytic functions but I'm stuck with the script :(

In addition, it would be great if I can start a grouping when this condition is meet: abs(depth-prev_depth) > 15, so I can get something like that:

Level|Depth|Descrip  | Group_ID   
  1  |  50 | Level_1 |  1  |  
  2  |  53 | Level_2 |  1  |  
  3  |  60 | Level_3 |  1  |  
  8  |  80 | Level_8 |  2  |  
  10 |  81 | Level_10|  2  |  
  15 | 101 | Level_15|  3  |  
  16 | 102 | Level_16|  3  |  
  17 | 102 | Level_16_bis| 3  |   
  18 | 103 | Level_17|   3   |

Any ideas ??

P.S: Sorry my bad english...

user_1144035 · Answer

You can use a cumulative sum to define the groups. And then aggregation:

Select min(level) as level,
       min(depth) keep (dense_rank first order by level) as depth,
       min(descrip) keep (dense_rank first order by level) as descrip,
       list_agg(level, ',') within group (order by level) as levels
from (select gl.*,
             sum(case when abs(prev_depth - depth) > 15 and depth > 0 then 1 else 0 end) over (order by level) as grp
      from (select gl.*, lag(depth) over (order by level asc) as prev_depth
            from ground_levels
           ) gl
     ) gl
group grp;

This actually keeps the starting level in the list. It can be removed, but that requires a bit more work.

Grouping when using analytic functions

1 Answers