AVG

Description

Syntax

AVG(
input_field)
OVER(
 
PARTITION BY
partitioning_field[,
partitioning_field]
 
ORDER BY
ordering_field[ASC | DESC] [,
ordering_field[ASC | DESC]]
 
RANGE
BETWEEN
value
PRECEDING
AND
CURRENT ROW|
 
ROWS
win_boundary|
BETWEEN
win_boundary
AND
win_boundary
)
               
where
win_boundarycan be:
               
 
UNBOUNDED PRECEDING
value
PRECEDING
UNBOUNDED FOLLOWING
value
FOLLOWING
CURRENT ROW

Return Value

Input Parameters

input_field

Required. The field on which to perform the aggregate function. You can use any numeric field or a Currency field.

OVER()

Required.

OVER

must be used within an

AVG

expression.

PARTITION BY

partitioning_field

Required. Use the

PARTITION BY

clause to specify 1 or more fields to use to partition a group of input rows. You can specify any field type except Currency. Example: You specify the Month field as the partitioning field, so Workday groups into a single partition all records that have the same value for Month.

ORDER BY

ordering_field

Required. Use the

ORDER BY

clause to specify how to order the input rows in the partition using the values in the specified field within each partition. You can specify any field type except Currency. However, you must use a numeric field type, such as Integer or Numeric when you use the

RANGE

clause.

You can use the

DESC

or

ASC

keywords to sort in descending order (high to low values, NULLs are last) or ascending order (low to high values, NULLs are first) for each ordering field. If you don't specify a sort order for an ordering field, Workday automatically sorts rows in ascending order.

ROWS | RANGE

Required. The

ROWS

and

RANGE

clauses define the specific number of rows (relative to the current row) within the partition by specifying a window frame. You define the window frame by specifying start and end points within the partition, known as window boundaries. The window frame is the set of input rows in each partition over which to calculate the aggregate expression (average for this function). The window frame can include one, several, or all rows of the partition.

Both

ROWS

and

RANGE

specify the range of rows relative to the current row, but

RANGE

operates logically on values (logical association) and

ROWS

operates physically on rows in the dataset (physical association).

RANGE

limits the window frame to contain rows that have their values within the specified range, relative to the current value.

ROWS

limits the window frame to contain rows that are physically next to the current row.

Use

RANGE

to define absolute window boundaries, such as the past 3 months or year to date. When you use

RANGE

, the

ORDER BY

clause must use a numeric field type, such as Integer or Numeric.

Example: Suppose you have an Integer field called MonthNum that represents the number of the month in the year (values 1 to 12). To specify all values from the past 3 months, you would order by MonthNum and use

RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

. This RANGE clause includes the current month and the previous 2 months, resulting in 3 months total.

When you publish a dataset that contains a window function using

RANGE

, the number of rows in the window must be 1000 or less. If a particular window exceeds 1000 rows, the publish job fails.

win_boundary

Required. The window boundaries define the start and end points of the window frame. Window boundaries are relative to the current row.

A

PRECEDING

clause defines a window boundary that is lower than the current row (the number of rows to include before the current row). The

FOLLOWING

clause defines a window boundary that is greater than the current row (the number of rows to include after the current row).

If you specify only 1 window boundary, then Workday uses the current row as the other boundary in the window frame (either the upper or lower boundary depending on the expression syntax). The

UNBOUNDED

keyword includes all rows in the direction specified. When you need to specify both a start and end of a window frame, use the

BETWEEN

and

AND

keywords.

When specifying a specific number of rows, the

value

must be 100 or less.

Example:

ROWS 2 PRECEDING

means that the window is 3 rows in size, starting with 2 rows preceding until and including the current row.

Example:

ROWS UNBOUNDED FOLLOWING

means that the window starts with the current row and includes the current row and all rows that come after the current row.

Examples

AVG([Sales]) OVER(
 PARTITION BY [Employee]
 ORDER BY [SalesDate] DESC
 ROWS UNBOUNDED PRECEDING)

AVG([Sales]) OVER(
 PARTITION BY [Employee]
 ORDER BY [SalesDate] DESC
 ROWS BETWEEN UNBOUNDED PRECEDING
 AND UNBOUNDED FOLLOWING)

AVG([fieldA]) OVER(
 PARTITION BY [fieldB]
 ORDER BY [Month]
 RANGE 11 PRECEDING)

AVG([fieldA]) OVER(
 PARTITION BY [fieldB]
 ORDER BY [Year]
 RANGE 1 PRECEDING)