| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
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 A-9, "General Numeric Functions".
Financial functions. For listing. see Table A-10, "Financial Functions".
Statistical functions. For listing. see Table A-11, "Statistical Functions".
Time-series functions such as LAG and MOVINGMIN. For listing. see Table A-12, "Time-Series Functions".
Aggregation functions, such as COUNT and TOTAL. For listing. see Table A-13, "Aggregation Functions"
Table A-9, "General Numeric Functions" lists the OLAP DML functions for calculation.
Table A-9 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 full-range (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 A-10, "Financial Functions" lists the OLAP DML functions for financial calculation.
Table A-10 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 straight-line 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 sum-of-years'-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 fixed-rate 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 fixed-rate installment loans over a specified number of time periods. |
| GROWRATE |
Calculates the growth rate of a time-series 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 variable-rate 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 variable-rate installment loans over a specified number of time periods. |
Table A-11, "Statistical Functions" lists the OLAP DML functions for statistical calculation.
Table A-11 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 A-12, "Time-Series Functions" lists the OLAP DML time-series functions.
Table A-12 Time-Series 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 A-13, "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 A-13 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. |
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
