Analytic function concepts in Standard SQL

An analytic function computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.

An analytic function includes an OVER clause, which defines a window of rows around the row being evaluated. For each row, the analytic function result is computed using the selected window of rows as input, possibly doing aggregation.

With analytic functions you can compute moving averages, rank items, calculate cumulative sums, and perform other analyses.

The following functions can be used as analytic functions: navigation functions, numbering functions, and aggregate analytic functions

Analytic function syntax

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

Notation rules

Square brackets "[ ]" indicate optional clauses.
Parentheses "( )" indicate literal parentheses.
The vertical bar "|" indicates a logical OR.
Curly braces "{ }" enclose a set of options.
A comma followed by an ellipsis within square brackets "[, ... ]" indicates that the preceding item can repeat in a comma-separated list.

Description

An analytic function computes results over a group of rows. You can use the following syntax to build an analytic function:

analytic_function_name: The function that performs an analytic operation. For example, the numbering function RANK() could be used here.
argument_list: Arguments that are specific to the analytic function. Some functions have them, some do not.
OVER: Keyword required in the analytic function syntax preceding the OVER clause.
over_clause: References a window that defines a group of rows in a table upon which to use an analytic function.
window_specification: Defines the specifications for the window.
window_frame_clause: Defines the window frame for the window.
rows_range: Defines the physical rows or a logical range for a window frame.

Notes

An analytic function can appear as a scalar expression operand in two places in the query:

The SELECT list. If the analytic function appears in the SELECT list, its argument list and OVER clause can't refer to aliases introduced in the same SELECT list.
The ORDER BY clause. If the analytic function appears in the ORDER BY clause of the query, its argument list can refer to SELECT list aliases.

An analytic function can't refer to another analytic function in its argument list or its OVER clause, even indirectly through an alias.

An analytic function is evaluated after aggregation. For example, the GROUP BY clause and non-analytic aggregate functions are evaluated first. Because aggregate functions are evaluated before analytic functions, aggregate functions can be used as input operands to analytic functions.

Returns

A single result for each row in the input.

Defining the `OVER` clause

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

Description

The OVER clause references a window that defines a group of rows in a table upon which to use an analytic function. You can provide a named_window that is defined in your query, or you can define the specifications for a new window.

Notes

If neither a named window nor window specification is provided, all input rows are included in the window for every row.

Examples using the OVER clause

These queries use window specifications:

Compute a grand total
Compute a subtotal
Compute a cumulative sum
Compute a moving average
Compute the number of items within a range
Get the most popular item in each category
Get the last value in a range
Compute rank

These queries use a named window:

Get the last value in a range
Use a named window in a window frame clause

Defining the window specification

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
  [ window_frame_clause ]

Description

Defines the specifications for the window.

named_window: The name of an existing window that was defined with a WINDOW clause.

PARTITION BY: Breaks up the input rows into separate partitions, over which the analytic function is independently evaluated.
- Multiple partition expressions are allowed in the PARTITION BY clause.
- An expression can't contain floating point types, non-groupable types, constants, or analytic functions.
- If this optional clause is not used, all rows in the input table comprise a single partition.
ORDER BY: Defines how rows are ordered within a partition. This clause is optional in most situations, but is required in some cases for navigation functions.
window_frame_clause: For aggregate analytic functions, defines the window frame within the current partition. The window frame determines what to include in the window. If this clause is used, ORDER BY is required except for fully unbounded windows.

Notes

If neither the ORDER BY clause nor window frame clause are present, the window frame includes all rows in that partition.

For aggregate analytic functions, if the ORDER BY clause is present but the window frame clause is not, the following window frame clause is used by default:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

For example, the following queries are equivalent:

SELECT book, LAST_VALUE(item)
  OVER (ORDER BY year)
FROM Library

SELECT book, LAST_VALUE(item)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library

Rules for using a named window in the window specification

If you use a named window in your window specifications, these rules apply:

The specifications in the named window can be extended with new specifications that you define in the window specification clause.
You can't have redundant definitions. If you have an ORDER BY clause in the named window and the window specification clause, an error is thrown.

The order of clauses matters. PARTITION BY must come first, followed by ORDER BY and window_frame_clause. If you add a named window, its window specifications are processed first.

--this works:
SELECT item, purchases, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW item_window AS (ORDER BY purchases)

--this does not work:
SELECT item, purchases, LAST_VALUE(item)
  OVER (item_window ORDER BY purchases) AS most_popular
FROM Produce
WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

A named window and PARTITION BY can't appear together in the window specification. If you need PARTITION BY, add it to the named window.
You can't refer to a named window in an ORDER BY clause, an outer query, or any subquery.

Examples using the window specification

These queries define partitions in an analytic function:

Compute a subtotal
Compute a cumulative sum
Get the most popular item in each category
Get the last value in a range
Compute rank
Use a named window in a window frame clause

These queries include a named window in a window specification:

Get the last value in a range
Use a named window in a window frame clause

These queries define how rows are ordered in a partition:

Compute a subtotal
Compute a cumulative sum
Compute a moving average
Compute the number of items within a range
Get the most popular item in each category
Get the last value in a range
Compute rank
Use a named window in a window frame clause

Defining the window frame clause

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c
  }

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

The window frame clause defines the window frame around the current row within a partition, over which the analytic function is evaluated. Only aggregate analytic functions can use a window frame clause.

rows_range: A clause that defines a window frame with physical rows or a logical range.
- ROWS: Computes the window frame based on physical offsets from the current row. For example, you could include two rows before and after the current row.
- RANGE: Computes the window frame based on a logical range of rows around the current row, based on the current row’s ORDER BY key value. The provided range value is added or subtracted to the current row's key value to define a starting or ending range boundary for the window frame. In a range-based window frame, there must be exactly one expression in the ORDER BY clause, and the expression must have a numeric type.
Tip: If you want to use a range with a date, use ORDER BY with the UNIX_DATE() function. If you want to use a range with a timestamp, use the UNIX_SECONDS(), UNIX_MILLIS(), or UNIX_MICROS() function.
frame_between: Creates a window frame with a lower and upper boundary. The first boundary represents the lower boundary. The second boundary represents the upper boundary. Only certain boundary combinations can be used, as show in the preceding syntax.
- Define the beginning of the window frame with unbounded_preceding, numeric_preceding, numeric_following, or current_row.
  - unbounded_preceding: The window frame starts at the beginning of the partition.
  - numeric_preceding or numeric_following: The start of the window frame is relative to the current row.
  - current_row: The window frame starts at the current row.
- Define the end of the window frame with numeric_preceding, numeric_following, current_row, or unbounded_following.
  - numeric_preceding or numeric_following: The end of the window frame is relative to the current row.
  - current_row: The window frame ends at the current row.
  - unbounded_following: The window frame ends at the end of the partition.
frame_start: Creates a window frame with a lower boundary. The window frame ends at the current row.
- unbounded_preceding: The window frame starts at the beginning of the partition.
- numeric_preceding: The start of the window frame is relative to the current row.
- current_row: The window frame starts at the current row.
numeric_expression: An expression that represents a numeric type. The numeric expression must be a constant, non-negative integer or parameter.

Notes

If a boundary extends beyond the beginning or end of a partition, the window frame will only include rows from within that partition.

You can't use a window frame clause with navigation functions and numbering functions, such as RANK().

Examples using the window frame clause

These queries compute values with ROWS:

Compute a cumulative sum
Compute a moving average
Get the most popular item in each category
Get the last value in a range
Use a named window in a window frame clause

These queries compute values with RANGE:

Compute the number of items within a range

These queries compute values with a partially or fully unbound window:

Compute a grand total
Compute a subtotal
Compute a cumulative sum
Get the most popular item in each category
Compute rank

These queries compute values with numeric boundaries:

Compute a cumulative sum
Compute a moving average
Compute the number of items within a range
Get the last value in a range
Use a named window in a window frame clause

These queries compute values with the current row as a boundary:

Compute a grand total
Compute a subtotal
Compute a cumulative sum

Referencing a named window

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

A named window represents a group of rows in a table upon which to use an analytic function. A named window is defined in the WINDOW clause, and referenced in an analytic function's OVER clause. In an OVER clause, a named window can appear either by itself or embedded within a window specification.

Examples

Get the last value in a range
Use a named window in a window frame clause

Navigation function concepts

Navigation functions generally compute some value_expression over a different row in the window frame from the current row. The OVER clause syntax varies across navigation functions.

Requirements for the OVER clause:

PARTITION BY: Optional.
ORDER BY:
1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
2. Required for FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD and LAG.
window_frame_clause:
1. Disallowed for PERCENTILE_CONT, PERCENTILE_DISC, LEAD and LAG.
2. Optional for FIRST_VALUE, LAST_VALUE, and NTH_VALUE.

For all navigation functions, the result data type is the same type as value_expression.

Numbering function concepts

Numbering functions assign integer values to each row based on their position within the specified window.

Example of RANK(), DENSE_RANK(), and ROW_NUMBER():

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| x          | rank       | dense_rank | row_num    |
+---------------------------------------------------+
| 1          | 1          | 1          | 1          |
| 2          | 2          | 2          | 2          |
| 2          | 2          | 2          | 3          |
| 5          | 4          | 3          | 4          |
| 8          | 5          | 4          | 5          |
| 10         | 6          | 5          | 6          |
| 10         | 6          | 5          | 7          |
+---------------------------------------------------+

RANK(): For x=5, rank is 4, since RANK() increments by the number of peers in the previous window ordering group.
DENSE_RANK(): For x=5, dense_rank is 3, since DENSE_RANK() always increments by 1, never skipping a value.
ROW_NUMBER(): For x=5, row_num is 4.

Aggregate analytic function concepts

An aggregate function is a function that performs a calculation on a set of values. Most aggregate functions can be used in an analytic function. These aggregate functions are called aggregate analytic functions.

With aggregate analytic functions, the OVER clause is appended to the aggregate function call; the function call syntax remains otherwise unchanged. Like their aggregate function counterparts, these analytic functions perform aggregations, but specifically over the relevant window frame for each row. The result data types of these analytic functions are the same as their aggregate function counterparts.

Filtering results with the QUALIFY clause

The QUALIFY clause can be used to filter the results of an analytic function. For more information and examples, see the QUALIFY clause.

Analytic function examples

In these examples, the highlighted item is the current row. The bolded items are the rows that are included in the analysis.

Common tables used in examples

The following tables are used in the subsequent aggregate analytic query examples: Produce, Employees, and Farm.

Produce table

Some examples reference a table called Produce:

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

Employees table

Some examples reference a table called Employees:

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

Farm table

Some examples reference a table called Farm:

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

Compute a grand total

This computes a grand total for all items in the Produce table.

(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
(orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases

SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

Compute a subtotal

This computes a subtotal for each category in the Produce table.

fruit
- (orange, apple) = 10 total purchases
- (orange, apple) = 10 total purchases
vegetable
- (leek, cabbage, lettuce, kale) = 44 total purchases
- (leek, cabbage, lettuce, kale) = 44 total purchases
- (leek, cabbage, lettuce, kale) = 44 total purchases
- (leek, cabbage, lettuce, kale) = 44 total purchases

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Compute a cumulative sum

This computes a cumulative sum for each category in the Produce table. The sum is computed with respect to the order defined using the ORDER BY clause.

fruit
- (orange, apple) = 2 total purchases
- (orange, apple) = 10 total purchases
vegetable
- (leek, cabbage, lettuce, kale) = 2 total purchases
- (leek, cabbage, lettuce, kale) = 11 total purchases
- (leek, cabbage, lettuce, kale) = 21 total purchases
- (leek, cabbage, lettuce, kale) = 44 total purchases

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

This does the same thing as the preceding example. You don't have to add CURRENT ROW as a boundary unless you would like to for readability.

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

In this example, all items in the Produce table are included in the partition. Only preceding rows are analyzed. The analysis starts two rows prior to the current row in the partition.

(orange, leek, apple, cabbage, lettuce, kale) = NULL
(orange, leek, apple, cabbage, lettuce, kale) = NULL
(orange, leek, apple, cabbage, lettuce, kale) = 2
(orange, leek, apple, cabbage, lettuce, kale) = 4
(orange, leek, apple, cabbage, lettuce, kale) = 12
(orange, leek, apple, cabbage, lettuce, kale) = 21

SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

Compute a moving average

This computes a moving average in the Produce table. The lower boundary is 1 row before the current row. The upper boundary is 1 row after the current row.

(orange, leek, apple, cabbage, lettuce, kale) = 2 average purchases
(orange, leek, apple, cabbage, lettuce, kale) = 4 average purchases
(orange, leek, apple, cabbage, lettuce, kale) = 6.3333 average purchases
(orange, leek, apple, cabbage, lettuce, kale) = 9 average purchases
(orange, leek, apple, cabbage, lettuce, kale) = 14 average purchases
(orange, leek, apple, cabbage, lettuce, kale) = 16.5 average purchases

SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

Compute the number of items within a range

This example gets the number of animals that have a similar population count in the Farm table.

(goose, dog, ox, goat, duck, cat) = 4 animals between population range 0-2.
(goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
(goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
(goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
(goose, dog, ox, goat, duck, cat) = 4 animals between population range 2-4.
(goose, dog, ox, goat, duck, cat) = 1 animal between population range 22-24.

SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

Get the most popular item in each category

This example gets the most popular item in each category. It defines how rows in a window are partitioned and ordered in each partition. The Produce table is referenced.

fruit
- (orange, apple) = apple is most popular
- (orange, apple) = apple is most popular
vegetable
- (leek, cabbage, lettuce, kale) = kale is most popular
- (leek, cabbage, lettuce, kale) = kale is most popular
- (leek, cabbage, lettuce, kale) = kale is most popular
- (leek, cabbage, lettuce, kale) = kale is most popular

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

Get the last value in a range

This example gets the most popular item in a specific window frame, using the Produce table. The window frame analyzes up to three rows at a time. Take a close look at the most_popular column for vegetables. Instead of getting the most popular item in a specific category, it gets the most popular item in a specific range in that category.

fruit
- (orange, apple) = apple is most popular
- (orange, apple) = apple is most popular
vegetable
- (leek, cabbage, lettuce, kale) = cabbage is most popular
- (leek, cabbage, lettuce, kale) = lettuce is most popular
- (leek, cabbage, lettuce, kale) = kale is most popular
- (leek, cabbage, lettuce, kale) = kale is most popular

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

This example returns the same results as the preceding example, but it includes a named window called item_window. Some of the window specifications are defined directly in the OVER clause and some are defined in the named window.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

Compute rank

This example calculates the rank of each employee within their department, based on their start date. The window specification is defined directly in the OVER clause. The Employees table is referenced.

department 1
- (Jacob, Anthony, Andrew) = Assign rank 1 to Jacob
- (Jacob, Anthony, Andrew) = Assign rank 2 to Anthony
- (Jacob, Anthony, Andrew) = Assign rank 3 to Andrew
department 2
- (Isabella, Daniel, Jose) = Assign rank 1 to Isabella
- (Isabella, Daniel, Jose) = Assign rank 2 to Daniel
- (Isabella, Daniel, Jose) = Assign rank 3 to Jose

SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

Use a named window in a window frame clause

You can define some of your logic in a named window and some of it in a window frame clause. This logic is combined. Here is an example, using the Produce table.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

You can also get the previous results with these examples:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

The following example produces an error because a window frame clause has been defined twice:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Analytic function concepts in Standard SQL

Analytic function syntax

Defining the OVER clause

Defining the window specification

Defining the window frame clause

Referencing a named window

Navigation function concepts

Numbering function concepts

Aggregate analytic function concepts

Filtering results with the QUALIFY clause

Analytic function examples

Common tables used in examples

Produce table

Employees table

Farm table

Compute a grand total

Compute a subtotal

Compute a cumulative sum

Compute a moving average

Compute the number of items within a range

Get the most popular item in each category

Get the last value in a range

Compute rank

Use a named window in a window frame clause

Defining the `OVER` clause