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 AVG aggregate function, which computes the average of an expression over a group of rows.

Behavior Type

Immutable

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()

See Analytic Functions.

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)

See Also