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