Re: Grouping, Aggregate, Min, Max

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(dot)simic(at)gmail(dot)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
>
>