Navigation functions

GoogleSQL for Bigtable supports navigation functions. Navigation functions are a subset of window functions. To create a window function call and learn about the syntax for window functions, see Window function_calls.

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.

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

Function list

Name	Summary
`FIRST_VALUE`	Gets a value for the first row in the current window frame.
`LAG`	Gets a value for a preceding row.
`LAST_VALUE`	Gets a value for the last row in the current window frame.
`LEAD`	Gets a value for a subsequent row.
`NTH_VALUE`	Gets a value for the Nth row of the current window frame.
`PERCENTILE_CONT`	Computes the specified percentile for a value, using linear interpolation.
`PERCENTILE_DISC`	Computes the specified percentile for a discrete value.

`FIRST_VALUE`

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
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 ]

Description

Returns the value of the value_expression for the first row in the current window frame.

This function includes NULL values in the calculation unless IGNORE NULLS is present. If IGNORE NULLS is present, the function excludes NULL values from the calculation.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any data type that an expression can return.

Return Data Type

Same type as value_expression.

Examples

The following example computes the fastest time for each division.

`LAG`

LAG (value_expression[, offset [, default_expression]])
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

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

Description

Returns the value of the value_expression on a preceding row. Changing the offset value changes which preceding row is returned; the default value is 1, indicating the previous row in the window frame. An error occurs if offset is NULL or a negative value.

The optional default_expression is used if there isn't a row in the window frame at the specified offset. This expression must be a constant expression and its type must be implicitly coercible to the type of value_expression. If left unspecified, default_expression defaults to NULL.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any data type that can be returned from an expression.
offset must be a non-negative integer literal or parameter.
default_expression must be compatible with the value expression type.

Return Data Type

Same type as value_expression.

Examples

The following example illustrates a basic use of the LAG function.

This next example uses the optional offset parameter.

The following example replaces NULL values with a default value.

`LAST_VALUE`

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
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 ]

Description

Returns the value of the value_expression for the last row in the current window frame.

This function includes NULL values in the calculation unless IGNORE NULLS is present. If IGNORE NULLS is present, the function excludes NULL values from the calculation.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any data type that an expression can return.

Return Data Type

Same type as value_expression.

Examples

The following example computes the slowest time for each division.

`LEAD`

LEAD (value_expression[, offset [, default_expression]])
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

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

Description

Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any data type that can be returned from an expression.
offset must be a non-negative integer literal or parameter.
default_expression must be compatible with the value expression type.

Return Data Type

Same type as value_expression.

Examples

The following example illustrates a basic use of the LEAD function.

This next example uses the optional offset parameter.

The following example replaces NULL values with a default value.

`NTH_VALUE`

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])
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 ]

Description

Returns the value of value_expression at the Nth row of the current window frame, where Nth is defined by constant_integer_expression. Returns NULL if there is no such row.

This function includes NULL values in the calculation unless IGNORE NULLS is present. If IGNORE NULLS is present, the function excludes NULL values from the calculation.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any data type that can be returned from an expression.
constant_integer_expression can be any constant expression that returns an integer.

Return Data Type

Same type as value_expression.

Examples

`PERCENTILE_CONT`

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]

Description

Computes the specified percentile value for the value_expression, with linear interpolation.

This function ignores NULL values if RESPECT NULLS is absent. If RESPECT NULLS is present:

Interpolation between two NULL values returns NULL.
Interpolation between a NULL value and a non-NULL value returns the non-NULL value.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression is a FLOAT64 expression.
percentile must be a FLOAT64 literal in the range [0, 1].

Return Data Type

FLOAT64

Examples

The following example computes the value for some percentiles from a column of values while ignoring nulls.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

 /*-----+-------------+--------+--------------+-----+
  | min | percentile1 | median | percentile90 | max |
  +-----+-------------+--------+--------------+-----+
  | 0   | 0.03        | 1.5    | 2.7          | 3   |
  +-----+-------------+--------+--------------+-----*/

The following example computes the value for some percentiles from a column of values while respecting nulls.

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

/*------+-------------+--------+--------------+-----+
 | min  | percentile1 | median | percentile90 | max |
 +------+-------------+--------+--------------+-----+
 | NULL | 0           | 1      | 2.6          | 3   |
 +------+-------------+--------+--------------+-----*/

`PERCENTILE_DISC`

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])
OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]

Description

Computes the specified percentile value for a discrete value_expression. The returned value is the first sorted value of value_expression with cumulative distribution greater than or equal to the given percentile value.

This function ignores NULL values unless RESPECT NULLS is present.

To learn more about the OVER clause and how to use it, see Window function calls.

Supported Argument Types

value_expression can be any orderable type.
percentile must be a FLOAT64 literal in the range [0, 1].

Return Data Type

Same type as value_expression.

Examples

The following example computes the value for some percentiles from a column of values while ignoring nulls.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

/*------+-----+--------+-----+
 | x    | min | median | max |
 +------+-----+--------+-----+
 | c    | a   | b      | c   |
 | NULL | a   | b      | c   |
 | b    | a   | b      | c   |
 | a    | a   | b      | c   |
 +------+-----+--------+-----*/

The following example computes the value for some percentiles from a column of values while respecting nulls.

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

/*------+------+--------+-----+
 | x    | min  | median | max |
 +------+------+--------+-----+
 | c    | NULL | a      | c   |
 | NULL | NULL | a      | c   |
 | b    | NULL | a      | c   |
 | a    | NULL | a      | c   |
 +------+------+--------+-----*/

Navigation functions Stay organized with collections Save and categorize content based on your preferences.

Function list

FIRST_VALUE

LAG

LAST_VALUE

LEAD

NTH_VALUE

PERCENTILE_CONT

PERCENTILE_DISC

Navigation functions

`FIRST_VALUE`

`LAG`

`LAST_VALUE`

`LEAD`

`NTH_VALUE`

`PERCENTILE_CONT`

`PERCENTILE_DISC`