Analytic Functions
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.
Syntax
General
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 ] ] )
Parameters
analytic‑function(arguments) |
A Vertica analytic function and its arguments. |
OVER
|
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 An empty |
window‑partition‑clause |
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. |
window‑order‑clause |
Optionally specifies how to sort rows that are supplied to the analytic function. If the |
window‑frame‑clause
|
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. |
named‑window |
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 |
Requirements
The following requirements apply to analytic functions:
- All require an
OVER
clause. Each function has its ownOVER
clause requirements. For example, you can supply an emptyOVER
clause for some analytic aggregate functions such asSUM
. 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
SELECT
andORDER BY
clauses. -
Analytic functions cannot be nested. For example, the following query is not allowed:
=> SELECT MEDIAN(RANK() OVER(ORDER BY sal) OVER()).
-
WHERE
,GROUP BY
andHAVING
operators are technically not part of the analytic function. However, they determine input to that function.