Re: Grouping, Aggregate, Min, Max

[Misa Simic <misa.simic@xxxxxxxxx>]

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

1

A

1-4

1

B

5-7

1

A

8-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.cura@xxxxxxxxx>

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.simic@xxxxxxxxx>

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