CODEWITHSUNDEEP
sqlsqlitegroup-byaggregate
Peter Alstorp
Peter Alstorp

Reputation: 11

SQL: aggregate table data in subgroups

fellow SQLers.

I have a simple yet complex problem that I need to solve with SQL (SQLite) for my FME-script to run efficiantly. I have data I want to aggregate but in subgroups - taking z-level into account. It's a geological profile where I want to simplify the incoming data before I create my profile.

Example data

x,y,z_from,z_to,value
1,2,1,2,A
1,2,2,3,A
1,2,3,4,B
1,2,4,5,A
1,2,5,6,A
1,2,6,7,A
1,2,7,8,C

With SQL I want to aggregate on value but only rows that are adjacent (sort of a dissolve but on non-spatial data). And I want to calculate (also in sql) the correct z_from- and z_to-values. So the result from the example data above should be:

x,y,z_from,z_to,value
1,2,1,3,A 
1,2,3,4,B
1,2,4,7,A
1,2,7,8,C

Any help on this will be remembered for all eternity.

Peter

Upvotes: 1

Views: 207

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

It looks like you want all rows where the the "next" value is different, as defined by z_to. Unfortunately, SQLite doesn't support lead(), but you can do:

select e.*
from (select e.*,
             (select e2.value
              from example e2
              where e2.z_to > e.z_to
              order by e2.z_to
              limit 1
             ) as next_value
      from example e
     ) e
where next_value is null or next_value <> value;

Upvotes: 1

Related Questions