Window Functions

Window functions are specialized SQL analytic functions that come in several forms: windowed aggregates and ranking functions are the most common forms.

A windowed aggregate is similar to a standard aggregate function, but is more flexible. Windowed aggregates can produce result sets over a window frame. A window frame defines a moving target for the evaluation of the function. For example, you can compute moving averages and sums over a set of ordered rows. You can also reset these calculations within a result set by defining window partitions.

Yellowbrick supports the following windowed aggregates:
  • SUM
  • COUNT
  • AVG
  • MIN
  • MAX

COUNT(DISTINCT) is not supported as a windowed aggregate. The other aggregate functions in this list are supported as both regular aggregate functions and aggregate window functions.

The following ranking and row numbering window functions operate on ordered and partitioned sets of rows.
  • CUME_DIST
  • RANK
  • DENSE_RANK
  • PERCENT_RANK
  • ROW_NUMBER
  • NTILE
Note: The ORDER BY clause is not required as part of the window definition for the ranking window functions, but the absence of this clause is likely to produce meaningless results.
Yellowbrick also supports two other types of window functions:
  • LAG and LEAD functions
  • FIRST_VALUE and LAST_VALUE functions

Window frames with RANGE and ROWS are supported.

Window function definitions with a FILTER clause are not supported.

You can use the WINDOW clause in the SELECT statement as a shortcut to define the same behavior for multiple window functions in the same query.