I'm trying to add an aggregate function column to an existing result set. I've tried variations of OVER(), UNION, but cannot find a solution.
Example current result set:
ID ATTR VALUE
1 score 5
1 score 7
1 score 9
Example desired result set:
ID ATTR VALUE STDDEV (score)
1 score 5 2
1 score 7 2
1 score 9 2
Thank you
Seems like you're after:
stddev(value) over (partition by attr) stddev(value) over (partition by id, attr)It just depend on what you need to partition by. Based on sample data the attr should be enough; but I could see possibly the ID and attr.
Example:
With CTE (ID, Attr, Value) as (
SELECT 1, 'score', 5 from dual union all
SELECT 1, 'score', 7 from dual union all
SELECT 1, 'score', 9 from dual union all
SELECT 1, 'Z', 1 from dual union all
SELECT 1, 'Z', 5 from dual union all
SELECT 1, 'Z', 8 from dual)
SELECT A.*, stddev(value) over (partition by attr)
FROM cte A
ORDER BY attr, value
DOCS show that by adding an order by to the analytic, one can acquire the cumulative standard deviation per record.
Giving us:
+----+-------+-------+------------------------------------------+
| ID | attr | value | stdev |
+----+-------+-------+------------------------------------------+
| 1 | Z | 1 | 3.51188458428424628280046822063322249225 |
| 1 | Z | 5 | 3.51188458428424628280046822063322249225 |
| 1 | Z | 8 | 3.51188458428424628280046822063322249225 |
| 1 | score | 5 | 2 |
| 1 | score | 7 | 2 |
| 1 | score | 9 | 2 |
+----+-------+-------+------------------------------------------+