Grouping Data Based on a Header Record
Assuming the ordering of your input data is such that the beginning of the batch (Column1=1) always has the least RowNumber within that batch, you can do something like this to assign pseudo batch numbers using the largest RowNumber among the preceding header records:

select t.*, 
    max(case column1 when 1 then RowNumber end) 
    over(order by rownumber range between unbounded preceding and current row) as batchid
from tbl t  


(fiddle)

From there you can use aggregate or window functions to calculate aggregates if you wrap the above in a subselect and use the pseudo batch numbers to partition data or group data: 

select 
max(case column1 when 1 then column3 end) as batchid,
sum(case column1 when 2 then some_numeric_column end) as sum_children,
max(case column1 when 8 then column3 end) as footer_value
from (
  select t.*, 
    max(case column1 when 1 then rownumber end) 
    over(order by rownumber range between unbounded preceding and current row) as batchnum
from tbl
) t1
group by batchnum