add generated column with aggregated over a partion and sort

Asked May 06 '22 at 02:43

Active May 06 '22 at 09:32

Viewed 39 times

I am trying to add a calculated column that computes a rolling average of a sorted partition. I can make it work as a query but cannot seem to get the result to become a calculated field.

ALTER TABLE PUBLIC "minutes" 
    ADD COLUMN "green_avg" NUMERIC (10,2) 
    AS GENERATED AVG("price") OVER(PARTITION BY "key"
    ORDER BY "endtime" ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)

edited May 06 '22 at 09:32

Uwe Allner

3,399
9
35
49

asked May 06 '22 at 02:43

Nuljon

You can't use a window function as a generated column expression. – Tim Biegeleisen May 06 '22 at 02:46
Depending on the make and model of the `RDBMS` you are using, which one is it?, you can perhaps have a calculated column in a `View` – Stefan Wuebbe May 06 '22 at 14:24

add generated column with aggregated over a partion and sort

0 Answers0