Aggregate Functions OBIEE

Aggregate Functions

Aggregate functions perform operations on multiple values to create summary results.

The aggregate functions cannot be used to form nested aggregation in expressions on logical columns that have a default aggregation rule defined in the Aggregation tab of the Logical Column dialog. To specify nested aggregation, you must define a column with a default aggregation rule and then request the aggregation of the column in a SQL statement.

Aggregate functions include:

·         AGGREGATE AT

·         AVG

·         AVGDISTINCT

·         BOTTOMN

·         COUNT

·         COUNTDISTINCT

·         COUNT(*)

·         FIRST

·         GROUPBYCOLUMN

·         GROUPBYLEVEL

·         LAST

·         MAX

·         MEDIAN

·         MIN

·         NTILE

·         PERCENTILE

·         RANK

·         STDDEV

·         STDDEV_POP

·         SUM

·         SUMDISTINCT

·         TOPN

AGGREGATE AT

This function aggregates columns based on the level or levels you specify. Using AGGREGATE AT guarantees that the aggregate for the measure always occurs at the levels specified after the keyword AT, regardless of the WHERE clause.

Syntax

AGGREGATE(expr AT level [, level1, levelN])

Where:

expr is any expression that references at least one measure column

level is the level at which you want to aggregate. You can optionally specify multiple levels.

You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as AGGREGATE(yearly_sales AT month) because "month" is from the same time dimension that is being used as the measure level for "yearly_sales."

Example

The following example shows the AGGREGATE AT function and example results:

SELECT month, year, AGGREGATE(sales AT Year)

FROM timeseriestestingWHERE year = 1994 AND month = 12

Result:

Month    Year    AGGREGATE AT year12       1994    7396Row count: 1

Because the AGGREGATE AT operator is always executed before the predicates, it always returns the correct total for the time level specified after the keyword AT.

AVG

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of AVG is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(x) in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).

Syntax

AVG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

AVGDISTINCT

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

AVG(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

BOTTOMN

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BOTTOMN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

COUNT

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax:

COUNT(expr)

Where:

expr is any expression.

COUNTDISTINCT

This function adds distinct processing to the COUNT function.

Syntax

COUNT(DISTINCT expr)

Where:

expr is any expression.

COUNT(*)

This function counts the number of rows.

Syntax

COUNT(*)

Example

For example, if a table named Facts contained 200,000,000 rows, the sample request would return the results shown:

SELECT COUNT(*) FROM Facts

Result:

200000000

FIRST

This function selects the first returned value of the expression argument. For example, the FIRST function can calculate the value of the first day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The FIRST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST function returns the first day in each level.

You should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

FIRST(expr)

Where:

expr is any expression that references at least one measure column.

Example

FIRST(sales)

GROUPBYCOLUMN

For use in setting up aggregate navigation. It specifies the logical columns that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data grouped by store and by month, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYCOLUMN(STORE, MONTH)

The GROUPBYCOLUMN function is only for use in configuring a repository. You cannot use it to form SQL statements.

GROUPBYLEVEL

For use in setting up aggregate navigation. It specifies the dimension levels that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data at the store and month levels, and if you have defined dimensions (Geography and Customers) containing these levels, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH)

The GROUPBYLEVEL function is only for use in configuring a repository. You cannot use it to form SQL statements.

LAST

This function selects the last returned value of the expression. For example, the LAST function can calculate the value of the last day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The LAST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the LAST function returns the last day in each level.

You should not use the LAST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

LAST(expr)

Where:

expr is any expression that references at least one measure column.

Example

LAST(sales)

MAX

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MAX(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MAX function resets its values for each group in the query according to specific rules.

MEDIAN

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax

MEDIAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MEDIAN function resets its values for each group in the query according to specific rules.

MIN

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MIN function resets its values for each group in the query according to specific rules.

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Syntax

NTILE(numExpr, numTiles)

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

PERCENTILE

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

The percentile is calculated based on the values in the result set.

Syntax

PERCENTILE(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The PERCENTILE function resets its values for each group in the query according to specific rules.

RANK

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax

RANK(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The RANK function resets its values for each group in the query according to specific rules.

STDDEV

This function returns the standard deviation for a set of values. The return type is always a double. STDEV_SAMP is a synonym for STDDEV.

Syntax

STDDEV([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

The STDDEV function resets its values for each group in the query according to specific rules.

STDDEV_POP

This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

Syntax

STDDEV_POP([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

SUM

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

SUM(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The SUM function resets its values for each group in the query according to specific rules.

SUMDISTINCT

This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

SUM(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

TOPN

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax

TOPN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

The TOPN function resets its values for each group in the query according to specific rules.

Reference :

otn.oracle.com