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 FROM, WHERE, GROUP BY, and HAVING clauses.

An empty OVER clause provides the best performance for single threaded queries on a single node.

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 OVER clause also includes a partition clause, rows are sorted within each partition.
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 OVER clauses.

A window name clause cannot specify a window frame clause. However, you can qualify the named window in an OVER clause with a window frame clause.

Requirements

The following requirements apply to analytic functions:

  • All require an OVER clause. Each function has its own OVER clause requirements. For example, you can supply an empty OVER clause 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 SELECT and ORDER 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 and HAVING operators are technically not part of the analytic function. However, they determine input to that function.

See also

In this section