Window function description
Analysis function (also called window function) and aggregation function, both are the row set group (a set of rows) to aggregate calculations, the difference is that the aggregation function can only return one value per group (one line), while the window function can return multiple values per group (multiple lines). The set of rows is also called a window and is defined by analytic_clause. The window size depends on the actual number of rows or logical intervals (e.g. time). Each row in the group is the result of a logical calculation based on the window. Triggering an analytic function requires the special keyword OVER to specify the window.
The syntax of the window function.
analytic_function ( [arguments] ) OVER ( [ partition_clause ]
[ order_by_clause [ windowing_clause ] ] )
Among that:
over is the keyword that identifies the analytic function.
analytic_function is the name of the specified analytic function. There are many Oracle analytic functions.
The format of the partition clause is partition by[,value_expr]… The keyword partition by clause logically divides a single result set into N groups based on the conditions of the partition expression. Here “partition by” and “group by” are synonyms.
The sort clause order-by-clause specifies how the data exists within the partition. The format is:
order [ siblings ] by { expr | position | c_alias } [ asc | desc ] [ nulls first | nulls last ]
Among that:
(1), asc|desc: specifies the order of ordering.
(2), nulls first|nulls last: specifies that the return line containing the null value should appear in the first or last position in the ordered sequence.
- windowing clause windowing-clause gives a fixed or variable data windowing method, the analysis function will operate on these data. In a set of windows based on any change or fixed, the clause can be used to allow the analysis function to calculate its value.
Top comments (0)