Aggregation of values by condition in PG SQL

The database stores a table of the following form:

Is it possible to calculate the average by grouping the values according to the segment length condition?

That is. if the length of the segment is 30. the regrouped table is as follows:

Now I am not considering situations where the value of a segment may overlap with values within cells.

In fact, the task of grouping is to find a way to "collapse" the values.

>Solution :

You can group by how many whole segments fit under the "End" value using the floor() function. It would work the same with ceil() or nothing, if your segment length is an integer because dividing by an int will result in truncating everything after decimal point:

numeric_type / numeric_type → numeric_type
Division (for integral types, division truncates the result towards zero)

Demo at db<>fiddle:

select "Parent name"
      ,avg(value)
      ,format( '(%s)/%s'
              ,string_agg(value::text,'+')
              ,count(*)) as calculation
from your_table
group by "Parent name", floor("End"/30)::int
order by "Parent name", max("End");