AVG [Analytic]
Computes an average of an expression in a group within a window. AVG
returns the same data type as the expression's numeric data type.
The AVG
analytic function differs from the
aggregate function, which computes the average of an expression over a group of rows.AVG
Behavior Type
Syntax
AVG ( expression ) OVER ( [ window-partition-clause ] [ window-order-clause ] [ window-frame-clause ] )
Parameters
expression |
Any data that can be implicitly converted to a numeric data type. |
OVER()
|
Overflow Handling
By default, Vertica allows silent numeric overflow when you call this function on numeric data types. For more information on this behavior and how to change it, see Numeric Data Type Overflow with SUM, SUM_FLOAT, and AVG.Examples
The following query finds the sales for that calendar month and returns a running/cumulative average (sometimes called a moving average) using the default window of RANGE UNBOUNDED PRECEDING AND CURRENT ROW
:
=> SELECT calendar_month_number_in_year Mo, SUM(product_price) Sales, AVG(SUM(product_price)) OVER (ORDER BY calendar_month_number_in_year)::INTEGER Average FROM product_dimension pd, date_dimension dm, inventory_fact if WHERE dm.date_key = if.date_key AND pd.product_key = if.product_key GROUP BY Mo; Mo | Sales | Average ----+----------+---------- 1 | 23869547 | 23869547 2 | 19604661 | 21737104 3 | 22877913 | 22117374 4 | 22901263 | 22313346 5 | 23670676 | 22584812 6 | 22507600 | 22571943 7 | 21514089 | 22420821 8 | 24860684 | 22725804 9 | 21687795 | 22610470 10 | 23648921 | 22714315 11 | 21115910 | 22569005 12 | 24708317 | 22747281 (12 rows)
To return a moving average that is not a running (cumulative) average, the window can specify ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
:
=> SELECT calendar_month_number_in_year Mo, SUM(product_price) Sales, AVG(SUM(product_price)) OVER (ORDER BY calendar_month_number_in_year ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)::INTEGER Average FROM product_dimension pd, date_dimension dm, inventory_fact if WHERE dm.date_key = if.date_key AND pd.product_key = if.product_key GROUP BY Mo; Mo | Sales | Average ----+----------+---------- 1 | 23869547 | 22117374 2 | 19604661 | 22313346 3 | 22877913 | 22584812 4 | 22901263 | 22312423 5 | 23670676 | 22694308 6 | 22507600 | 23090862 7 | 21514089 | 22848169 8 | 24860684 | 22843818 9 | 21687795 | 22565480 10 | 23648921 | 23204325 11 | 21115910 | 22790236 12 | 24708317 | 23157716 (12 rows)