There is a trick to simplify the thing and avoid using aggregates : I think it will give you your answer.
http://postgresql.1045698.n5.nabble.com/Count-of-records-in-a-row-td5775363i20.html Cheers, Rémi-C 2013/12/13 Misa Simic <misa.si...@gmail.com> > Hi All, > > I am not sure how to define with words what I want to accomplish (so can't > ask google the right question :) ) > > So will try to explain with sample data and expected result: > > Scenario 1) > > id thing_id category period_id 1 1 A 1 2 1 A 2 3 1 A 3 4 1 A 4 5 1 > A 5 6 1 A 6 7 1 A 7 8 1 A 8 9 1 A 9 10 2 A 1 11 2 A 2 12 2 A 3 13 > 2 A 4 > > > Expected result: > > thing_id category periods 1 A 1-9 2 A 1-4 > (Sounds easy, group by, thing_id, category use Min and Max for period id - > but further scenarios makes it a bit complicated...) > > Scenario 2) > > id thing_id category period_id 1 1 A 1 2 1 A 2 3 1 A 3 4 1 A 4 5 1 > B 5 6 1 B 6 7 1 B 7 8 1 A 8 9 1 A 9 10 2 A 1 11 2 A 2 12 2 A 3 13 > 2 A 4 > Expected result: > thing_id category periods 1 A 1-4, 8-9 1 B 5-7 2 A 1-4 > Scenario 3) > > id thing_id category period_id 1 1 A 1 2 1 A 2 3 1 A 3 4 1 A 7 5 1 A > 8 6 1 A 9 7 2 A 1 8 2 A 2 9 2 A 3 10 2 A 4 > Expected result: > > thing_id category periods 1 A 1-3, 7-9 2 A 1-4 > > > So goal is, to group by thing_id, category id - but if period_id is > interupted (not in incremented by 1) to have aggregated spans... > > To desired results we have came up using several CTE's (what makes a query > a bit big, and more "procedural way": make cte what calculated diff between > current and previous row, next cte uses previous one to define groupings, > next cte to make aggregates etc...) > > So I wonder - is there some kind of aggregate window function what does > desired results? > > > Many Thanks, > > Misa > >
