I have a table like this:
| total | alert | group_id | hlevel | full_path | parent_id | root_group_id |
|---|---|---|---|---|---|---|
| 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 1 | BizA\DivA | 5100FFFF-60B6-D5CD-BFBA-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | ||
| 10 | CellPhone | 5100FFFF-60B6-D5CD-C46A-A8A3E03F0000 | 2 | BizA\DivA\Region1 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 164 | FoodDrink | 5100FFFF-60B6-D5CD-C46A-A8A3E03F0000 | 2 | BizA\DivA\Region1 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 1 | Unbelted | 5100FFFF-60B6-D5CD-C46A-A8A3E03F0000 | 2 | BizA\DivA\Region1 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 7 | Smoking | 5100FFFF-60B6-D5CD-C46A-A8A3E03F0000 | 2 | BizA\DivA\Region1 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 3 | Motion | 5100FFFF-60B6-D5CD-C46A-A8A3E03F0000 | 2 | BizA\DivA\Region1 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 1 | Motion | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 39 | RollingStop | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 7 | CellPhone | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 3 | Smoking | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 347 | FoodDrink | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 12 | Obstruction | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
| 3 | Unbelted | 5100FFFF-60B6-D5CD-C46B-A8A3E03F0000 | 2 | BizA\DivA\Region2 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 | 5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000 |
I'm trying to show a recursive total sum for each group including what alerts it had plus the ones all of its descendants have (the full table is 10 levels deep).
root_group_id was obtained via recursion and it basically means each group its is own root_group_id and all of its children are related to the same root_group_id.
the final result would look like this:
| total | alert | hlevel | full_path |
|---|---|---|---|
| 4 | Motion | 1 | BizA\DivA |
| 39 | RollingStop | 1 | BizA\DivA |
| 17 | CellPhone | 1 | BizA\DivA |
| 10 | Smoking | 1 | BizA\DivA |
| 511 | FoodDrink | 1 | BizA\DivA |
| 12 | Obstruction | 1 | BizA\DivA |
| 4 | Unbelted | 1 | BizA\DivA |
| 10 | CellPhone | 2 | BizA\DivA\Region1 |
| 164 | FoodDrink | 2 | BizA\DivA\Region1 |
| 1 | Unbelted | 2 | BizA\DivA\Region1 |
| 7 | Smoking | 2 | BizA\DivA\Region1 |
| 3 | Motion | 2 | BizA\DivA\Region1 |
| 1 | Motion | 2 | BizA\DivA\Region2 |
| 39 | RollingStop | 2 | BizA\DivA\Region2 |
| 7 | CellPhone | 2 | BizA\DivA\Region2 |
| 3 | Smoking | 2 | BizA\DivA\Region2 |
| 347 | FoodDrink | 2 | BizA\DivA\Region2 |
| 12 | Obstruction | 2 | BizA\DivA\Region2 |
| 3 | Unbelted | 2 | BizA\DivA\Region2 |
Where you get the total alerts by alert type for each group including their own alerts and that of its descendants.
I'm trying to do this in redshift and I'm totally stumped. Would appreciate the help of anyone familiar with recursive totals in redshift.
