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?
