Re: Grouping, Aggregate, Min, Max

Thanks Rémi-C,

Well, not sure is it a goal to avoid aggregates...

Bellow problem/solution even works (not sure) I guess would produce (if we
imagine instead of count it use min and max in a row, though this case a
bit complicated because of it should take real values from the source
table, because of simplicity it starts in examples from 1 - what is not
always the case...)

1A1-4
1B5-71A8-9

so next step, is to aggregate1-4,8-9 in one row

I think we have used similar approach, what with a few CTE's provides
desired result, just think would be simpler with 1 windowed aggregate
function.... i.e.

for:
c1, c2
A 1
A 2
A 3

SELECT DISTINCT c1, custom_agg_function(c2) OVER (PARTITION BY c1 ORDER BY
c2)

result
a, 1 -3

in case
c1, c2
A 1
A 2
A 3
A 5

result:

A , 1-3, 5-5

thanks,

Misa

2013/12/13 Rémi Cura <remi(dot)cura(at)gmail(dot)com>

> 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
>>
>>
>