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