I have a table like this:
| values | frequencies | grpng |
|---|---|---|
| 2 | 1 | cat1 |
| 3 | 2 | cat1 |
| 4 | 1 | cat1 |
| 2 | 2 | cat2 |
| 1 | 1 | cat2 |
| 5 | 2 | cat2 |
I want to generate the standard deviation (population sd) per group (cat1, cat2) not with a window function but by grouping wrt to the grpng variable. I see two options:
- Expand the values using the frequencies and then use the standard sql sd dev function.
- Directly group and get the sd dev manually if possible.
Can you suggest a solution? For the first option I am not able to find a function to expand in Impala.
My desired outcome is:
| sddev | grpng |
|---|---|
| 0.70710678118655 | cat1 |
| 1.6733200530682 | cat2 |