Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B1033902 


View PDF 
Oracle OLAP offers the following types of numeric functions:
General numeric functions for typical mathematical processing (for example, ranking and finding logs and tangets). For listing. see Table A9, "General Numeric Functions".
Financial functions. For listing. see Table A10, "Financial Functions".
Statistical functions. For listing. see Table A11, "Statistical Functions".
Timeseries functions such as LAG and MOVINGMIN. For listing. see Table A12, "TimeSeries Functions".
Aggregation functions, such as COUNT and TOTAL. For listing. see Table A13, "Aggregation Functions"
Table A9, "General Numeric Functions" lists the OLAP DML functions for calculation.
Table A9 General Numeric Functions
Function  Description 

ABS 
Calculates the absolute value of an expression. 
ANTILOG 
Calculates the value of e (the base of natural logarithms) raised to a specific power. 
ANTILOG10 
Calculates the value of 10 raised to a specified power. 
ARCCOS 
Calculates the angle value (in radians) of a specified cosine. 
ARCSIN 
Calculates the angle value (in radians) of a specified sine. 
ARCTAN 
Calculates the angle value (in radians) of a specified tangent. 
ARCTAN2 
Returns a fullrange (0  2 pi) numeric value indicating the arc tangent of a given ratio. 
BITAND 
Computes an AND operation on the bits of two integers. 
CEIL 
Returns the smallest whole number greater than or equal to a specified number. 
COS 
Calculates the cosine of an angle expression. 
COSH 
Calculates the hyperbolic cosine of an angle expression. 
DECODE 
Compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not. 
EXP 
Returns e raised to the nth power, where e equals 2.71828183.... 
FLOOR 
Returns the largest whole number equal to or less than a specified number. 
GREATEST 
Returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison. 
INSTRB 
Calculates the integer part of a decimal number by truncating its decimal fraction. 
LEAST 
Returns the smallest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison. 
LOG function 
Computes the natural logarithm of an expression. 
LOG10 
Computes the logarithm base 10 of an expression. 
MAX 
Calculates the larger value of two expressions. 
MIN 
Calculates the smaller value of two expressions. 
NULLIF 
Compares one expression with another and returns NA when the expressions are equal, or the base expression when they are not. 
REM 
Returns the remainder after one numeric expression is divided by another. 
ROUND (for numbers) 
Returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number. 
SIGN 
Returns a value that indicates if a specified number is less than, equal to, or greater than 0 (zero). 
SIN 
Calculates the sine of an angle expression. The result returned by SIN is a decimal value with the same dimensions as the specified expression. 
SINH 
Calculates the hyperbolic sine of an angle expression. 
SQRT 
Computes the square root of an expression. 
TAN 
Calculates the tangent of an angle expression. 
TANH 
Calculates the hyperbolic tangent of an angle expression. 
TRUNC (for numbers) 
Truncates a number to a specified number of decimal places. 
WIDTH_BUCKET 
Returns the bucket number into which the value of an expression would fall after being evaluated. 
Table A10, "Financial Functions" lists the OLAP DML functions for financial calculation.
Table A10 Financial Functions
Function  Description 

DEPRDECL 
Calculates the depreciation expenses for a series of assets. DEPRDECL uses the declining balance method to depreciate the assets over the specified lifetime of the assets. 
DEPRDECLSW 
Calculates the depreciation expenses for a series of assets. DEPRDECLSW uses a variation on the declining balance method to depreciate assets over the specified lifetime of the assets. 
DEPRSL 
Calculates the depreciation expenses for a series of assets. DEPRSL uses the straightline method to depreciate the assets over the specified lifetime of the assets. 
DEPRSOYD 
Calculates the depreciation expenses for a series of assets. DEPRSOYD uses the sumofyears'digits method to depreciate the assets over the specified lifetime of the assets. 
FINTSCHED 
Calculates the interest portion of the payments on a series of fixedrate installment loans that are paid off over a specified number of time periods. 
FPMTSCHED 
Calculates a payment schedule (principal plus interest) for paying off a series of fixedrate installment loans over a specified number of time periods. 
GROWRATE 
Calculates the growth rate of a timeseries expression, based on the first and last values of the series. 
IRR 
Computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for a period that makes the net present value of the corresponding cash flows equal to zero. 
NPV 
Computes the net present value of a series of cash flow values. 
VINTSCHED 
Calculates the interest portion of the payments on a series of variablerate installment loans that are paid off over a specified number of time periods. 
VPMTSCHED 
Calculates a payment schedule (principal plus interest) for paying off a series of variablerate installment loans over a specified number of time periods. 
Table A11, "Statistical Functions" lists the OLAP DML functions for statistical calculation.
Table A11 Statistical Functions
Statement  Description 

CATEGORIZE 
Groups the values of a numeric expression into categories. 
CORRELATION 
Returns the correlation coefficients for the pairs of data values in two expressions. 
NORMAL 
Returns a random value from a normal distribution with a specified mean and standard deviation. The result returned by NORMAL is dimensioned by all the dimensions of the mean and standard deviation expressions. 
RANDOM 
Produces a number that is randomly distributed between specified low and high boundaries. 
STDDEV 
Calculates the standard deviation of the values of an expression. 
Table A12, "TimeSeries Functions" lists the OLAP DML timeseries functions.
Table A12 TimeSeries Functions
Function  Description 

CUMSUM 
Computes cumulative totals over a dimension. 
LAG 
Returns the values of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension. 
LAGABSPCT 
Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression. 
LAGDIF 
Returns the difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression. 
LAGPCT 
Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression. 
LEAD 
Returns the values of a dimensioned variable or expression at a specified offset of a dimension subsequent to the current value of that dimension. 
MOVINGAVERAGE 
Computes a series of averages for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGAVERAGE computes the average of the data in the range specified, relative to the current dimension value. 
MOVINGMAX 
Returns a series of maximum values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMAX searches the data for the maximum value in the range specified, relative to the current dimension value. 
MOVINGMIN 
Returns a series of minimum values for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMIN searches the data for the minimum value in the range specified, relative to the current dimension value. 
MOVINGTOTAL 
Computes a series of totals for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGTOTAL computes the total of the data in the range specified, relative to the current dimension value. 
Table A13, "Aggregation Functions" lists the OLAP DML aggregation functions. The OLAP DML also provides an aggmap object that you can use to aggregate data, see "Aggregation Statements" for a list of related OLAP DML statements.
Table A13 Aggregation Functions
Statements  Description 

ANY 
Returns YES when any values of a Boolean expression are TRUE, or NO when none of the values are TRUE. 
AVERAGE 
Calculates the average of the values of an expression. 
COUNT 
Retrieves the number of TRUE values of a Boolean expression, or 0 (zero) if no values of the expression are TRUE. 
EVERY 
Returns YES when every value of a Boolean expression is TRUE, or NO if any value of the expression is FALSE. 
LARGEST 
Returns the largest value of an expression. You can use this function to compare numeric values or date values. 
MEDIAN 
Calculates the median of the values of an expression. 
MODE 
Returns the mode (the most frequently occurring value) of a numeric expression; or NA when there are no duplicate values in the data. 
NONE 
Returns YES when no values of a Boolean expression are TRUE; or NO when any value of the expression is true. 
PERCENTAGE 
Computes the percent of total for each value in a numeric expression. 
SMALLEST 
Returns the smallest value of an expression. You can use this function to compare numeric values or date values. 
TCONVERT 
Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion. 
TOTAL 
Calculates the total of the values of an expression. 