Oracle Analytical Functions
Oracle, a leading database application, features many analytic functions that allow developers to perform tasks in a Structured Query Language, or SQL, as opposed to a procedural language, which comes with far more constraints. Examples of Oracle analytic functions include Avg, Count, Lag, Percent Rank and Variance.
Other People Are Reading
-
Function
-
Analytic functions combine a group of rows to compute an aggregate value. Each specific function accomplishes the feat in a different way, yet all analytic functions provide a distinct advantage over customary aggregate functions in that they can return multiple rows for each group. Users typically employ analytic functions to compute such different types of aggregates as cumulative, moving, centered or reporting.
Features
-
While the syntax for each function may vary slightly, the basic syntax structure for an Oracle analytic function consists of the same three components: the analytic function, the arguments and the analytic cause. The syntax may be typed as "analytic_function ([arguments]) OVER (analytic_clause)". The analytic function can incorporate between zero and three arguments, with arguments consisting of any numeric data. The analytic clause portion may consist of a query partition clause, an order-by-class cause or a windowing clause.
-
Types
-
Each of the three analytic clauses in an analytical function performs a special task. A query partition clause divides the result set into groups of data, separating the data into partitions. This feature allows for greater customization and more efficient organization of data. The order by clause takes things one step further, ordering rows within a partition for even more precision. Finally, the windowing clause works as an extension of the order by clause, determining a specific range of rows from within the ordered partition to affect. A windowing clause may be used only if an order by clause is already present.
Considerations
-
When entering the syntax for the given analytic function, you may add certain terms to further define the resulting data. For instance, ASC and DESC represent "ascending" and "descending," respectively, when specifying an ordering sequence. Adding the terms NULLS FIRST or NULLS LAST determines whether returned rows with no data should be displayed first or last. When working with a windowing clause, ROWS indicates the targeted window in physical units, while RANGE specifies the window as a logical set of rows. You may also define a window's starting point with CURRENT ROW or even UNBOUNDED PRECEDING, which places the starting point at the first row of the partition. The term UNBOUNDED FOLLOWING specifies the window ends at the partition's final row.
-
References
- Photo Credit Thinkstock Images/Comstock/Getty Images
