All analytic functions in this section with an aggregate counterpart are appended with [Analytics] in the heading to avoid confusion between the two function types.
Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis and reporting tasks—for example:
- Rank the longest-standing customers in a particular state.
- Calculate the moving average of retail volume over a specified time.
- Find the highest score among all students in the same grade.
- Compare the current sales bonus that salespersons received against their previous bonus.
Analytic functions return aggregate results but they do not group the result set. They return the group value multiple times, once per record. You can sort group values, or partitions, using a window
ORDER BY clause, but the order affects only the function result set, not the entire query result set.
analytic‑function(arguments) OVER( [ window-partition-clause ] [ window-order-clause [ window-frame-clause ] ] )
With named window
analytic‑function(arguments) OVER( [ named‑window [ window‑frame‑clause ] ] )
A Vertica analytic function and its arguments.
Specifies how to partition, sort, and window frame function input with respect to the current row. The input data is the result set that the query returns after it evaluates
Groups input rows according to one or more columns or expressions.
If you omit this clause, no grouping occurs and the analytic function processes all input rows as a single partition.
Optionally specifies how to sort rows that are supplied to the analytic function. If the
Only valid for some analytic functions, specifies as input a set of rows relative to the row that is currently being evaluated by the analytic function. After the function processes that row and its window, Vertica advances the current row and adjusts the window boundaries accordingly.
The name of a window that you define in the same query with a window name clause. This definition encapsulates window partitioning and sorting. Named windows are useful when the query invokes multiple analytic functions with similar
A window name clause cannot specify a window frame clause. However, you can qualify the named window in an
The following requirements apply to analytic functions:
- All require an
OVERclause. Each function has its own
OVERclause requirements. For example, you can supply an empty
OVERclause for some analytic aggregate functions such as
SUM. For other functions, window frame and order clauses might be required, or might be invalid.
- Analytic functions can be invoked only in a query's
Analytic functions cannot be nested. For example, the following query is not allowed:
=> SELECT MEDIAN(RANK() OVER(ORDER BY sal) OVER()).
HAVINGoperators are technically not part of the analytic function. However, they determine input to that function.