Navigation functions

GoogleSQL for BigQuery supports navigation functions.Navigation functions are a subset of window functions. To create awindow function call and learn about the syntax for window functions,seeWindow function_calls.

Navigation functions generally compute somevalue_expression over a different row in the window frame from thecurrent row. TheOVER clause syntax varies across navigation functions.

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

Function list

NameSummary
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])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...][window_frame_clause]

Description

Returns the value of thevalue_expression for the first row in the currentwindow frame.

This function includesNULL values in the calculation unlessIGNORE NULLS ispresent. IfIGNORE NULLS is present, the function excludesNULL values fromthe calculation.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

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

Return Data Type

Same type asvalue_expression.

Examples

The following example computes the fastest time for each division.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,FORMAT_TIMESTAMP('%X',finish_time)ASfinish_time,division,FORMAT_TIMESTAMP('%X',fastest_time)ASfastest_time,TIMESTAMP_DIFF(finish_time,fastest_time,SECOND)ASdelta_in_secondsFROM(SELECTname,finish_time,division,FIRST_VALUE(finish_time)OVER(PARTITIONBYdivisionORDERBYfinish_timeASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASfastest_timeFROMfinishers);/*-----------------+-------------+----------+--------------+------------------+ | name            | finish_time | division | fastest_time | delta_in_seconds | +-----------------+-------------+----------+--------------+------------------+ | Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                | | Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                | | Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              | | Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              | | Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              | | Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             | | Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                | | Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              | | Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              | | Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              | +-----------------+-------------+----------+--------------+------------------*/

LAG

LAG(value_expression[,offset[,default_expression]])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]

Description

Returns the value of thevalue_expression on a preceding row. Changing theoffset value changes which preceding row is returned; the default value is1, indicating the previous row in the window frame. An error occurs ifoffset is NULL or a negative value.

The optionaldefault_expression is used if there isn't a row in the windowframe at the specified offset. This expression must be a constant expression andits type must be implicitly coercible to the type ofvalue_expression. If leftunspecified,default_expression defaults to NULL.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

  • value_expression can be any data type that can be returned from anexpression.
  • 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 asvalue_expression.

Examples

The following example illustrates a basic use of theLAG function.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LAG(name)OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASpreceding_runnerFROMfinishers;/*-----------------+-------------+----------+------------------+ | name            | finish_time | division | preceding_runner | +-----------------+-------------+----------+------------------+ | Carly Forte     | 03:08:58    | F25-29   | NULL             | | Sophia Liu      | 02:51:45    | F30-34   | NULL             | | Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       | | Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      | | Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      | | Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   | | Lisa Stelzner   | 02:54:11    | F35-39   | NULL             | | Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    | | Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  | | Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    | +-----------------+-------------+----------+------------------*/

This next example uses the optionaloffset parameter.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LAG(name,2)OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)AStwo_runners_aheadFROMfinishers;/*-----------------+-------------+----------+-------------------+ | name            | finish_time | division | two_runners_ahead | +-----------------+-------------+----------+-------------------+ | Carly Forte     | 03:08:58    | F25-29   | NULL              | | Sophia Liu      | 02:51:45    | F30-34   | NULL              | | Nikki Leith     | 02:59:01    | F30-34   | NULL              | | Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        | | Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       | | Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       | | Lisa Stelzner   | 02:54:11    | F35-39   | NULL              | | Lauren Matthews | 03:01:17    | F35-39   | NULL              | | Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     | | Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   | +-----------------+-------------+----------+-------------------*/

The following example replaces NULL values with a default value.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LAG(name,2,'Nobody')OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)AStwo_runners_aheadFROMfinishers;/*-----------------+-------------+----------+-------------------+ | name            | finish_time | division | two_runners_ahead | +-----------------+-------------+----------+-------------------+ | Carly Forte     | 03:08:58    | F25-29   | Nobody            | | Sophia Liu      | 02:51:45    | F30-34   | Nobody            | | Nikki Leith     | 02:59:01    | F30-34   | Nobody            | | Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        | | Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       | | Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       | | Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            | | Lauren Matthews | 03:01:17    | F35-39   | Nobody            | | Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     | | Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   | +-----------------+-------------+----------+-------------------*/

LAST_VALUE

LAST_VALUE(value_expression[{RESPECT|IGNORE}NULLS])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...][window_frame_clause]

Description

Returns the value of thevalue_expression for the last row in the currentwindow frame.

This function includesNULL values in the calculation unlessIGNORE NULLS ispresent. IfIGNORE NULLS is present, the function excludesNULL values fromthe calculation.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

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

Return Data Type

Same type asvalue_expression.

Examples

The following example computes the slowest time for each division.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,FORMAT_TIMESTAMP('%X',finish_time)ASfinish_time,division,FORMAT_TIMESTAMP('%X',slowest_time)ASslowest_time,TIMESTAMP_DIFF(slowest_time,finish_time,SECOND)ASdelta_in_secondsFROM(SELECTname,finish_time,division,LAST_VALUE(finish_time)OVER(PARTITIONBYdivisionORDERBYfinish_timeASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASslowest_timeFROMfinishers);/*-----------------+-------------+----------+--------------+------------------+ | name            | finish_time | division | slowest_time | delta_in_seconds | +-----------------+-------------+----------+--------------+------------------+ | Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                | | Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             | | Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              | | Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              | | Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              | | Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                | | Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              | | Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              | | Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               | | Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                | +-----------------+-------------+----------+--------------+------------------*/

LEAD

LEAD(value_expression[,offset[,default_expression]])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...]

Description

Returns the value of thevalue_expression on a subsequent row. Changing theoffset value changes which subsequent row is returned; the default value is1, indicating the next row in the window frame. An error occurs ifoffset isNULL or a negative value.

The optionaldefault_expression is used if there isn't a row in the windowframe at the specified offset. This expression must be a constant expression andits type must be implicitly coercible to the type ofvalue_expression. If leftunspecified,default_expression defaults to NULL.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

  • value_expression can be any data type that can be returned from anexpression.
  • 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 asvalue_expression.

Examples

The following example illustrates a basic use of theLEAD function.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LEAD(name)OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)ASfollowed_byFROMfinishers;/*-----------------+-------------+----------+-----------------+ | name            | finish_time | division | followed_by     | +-----------------+-------------+----------+-----------------+ | Carly Forte     | 03:08:58    | F25-29   | NULL            | | Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     | | Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     | | Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  | | Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner | | Lauren Reasoner | 03:10:14    | F30-34   | NULL            | | Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews | | Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   | | Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      | | Suzy Slane      | 03:06:24    | F35-39   | NULL            | +-----------------+-------------+----------+-----------------*/

This next example uses the optionaloffset parameter.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LEAD(name,2)OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)AStwo_runners_backFROMfinishers;/*-----------------+-------------+----------+------------------+ | name            | finish_time | division | two_runners_back | +-----------------+-------------+----------+------------------+ | Carly Forte     | 03:08:58    | F25-29   | NULL             | | Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      | | Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   | | Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  | | Meghan Lederer  | 03:07:41    | F30-34   | NULL             | | Lauren Reasoner | 03:10:14    | F30-34   | NULL             | | Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    | | Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       | | Desiree Berry   | 03:05:42    | F35-39   | NULL             | | Suzy Slane      | 03:06:24    | F35-39   | NULL             | +-----------------+-------------+----------+------------------*/

The following example replaces NULL values with a default value.

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,finish_time,division,LEAD(name,2,'Nobody')OVER(PARTITIONBYdivisionORDERBYfinish_timeASC)AStwo_runners_backFROMfinishers;/*-----------------+-------------+----------+------------------+ | name            | finish_time | division | two_runners_back | +-----------------+-------------+----------+------------------+ | Carly Forte     | 03:08:58    | F25-29   | Nobody           | | Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      | | Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   | | Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  | | Meghan Lederer  | 03:07:41    | F30-34   | Nobody           | | Lauren Reasoner | 03:10:14    | F30-34   | Nobody           | | Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    | | Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       | | Desiree Berry   | 03:05:42    | F35-39   | Nobody           | | Suzy Slane      | 03:06:24    | F35-39   | Nobody           | +-----------------+-------------+----------+------------------*/

NTH_VALUE

NTH_VALUE(value_expression,constant_integer_expression[{RESPECT|IGNORE}NULLS])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]ORDERBYexpression[{ASC|DESC}][,...][window_frame_clause]

Description

Returns the value ofvalue_expression at the Nth row of the current windowframe, where Nth is defined byconstant_integer_expression. Returns NULL ifthere is no such row.

This function includesNULL values in the calculation unlessIGNORE NULLS ispresent. IfIGNORE NULLS is present, the function excludesNULL values fromthe calculation.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

  • value_expression can be any data type that can be returned from anexpression.
  • constant_integer_expression can be any constant expression that returns aninteger.

Return Data Type

Same type asvalue_expression.

Examples

WITHfinishersAS(SELECT'Sophia Liu'asname,TIMESTAMP'2016-10-18 2:51:45'asfinish_time,'F30-34'asdivisionUNIONALLSELECT'Lisa Stelzner',TIMESTAMP'2016-10-18 2:54:11','F35-39'UNIONALLSELECT'Nikki Leith',TIMESTAMP'2016-10-18 2:59:01','F30-34'UNIONALLSELECT'Lauren Matthews',TIMESTAMP'2016-10-18 3:01:17','F35-39'UNIONALLSELECT'Desiree Berry',TIMESTAMP'2016-10-18 3:05:42','F35-39'UNIONALLSELECT'Suzy Slane',TIMESTAMP'2016-10-18 3:06:24','F35-39'UNIONALLSELECT'Jen Edwards',TIMESTAMP'2016-10-18 3:06:36','F30-34'UNIONALLSELECT'Meghan Lederer',TIMESTAMP'2016-10-18 3:07:41','F30-34'UNIONALLSELECT'Carly Forte',TIMESTAMP'2016-10-18 3:08:58','F25-29'UNIONALLSELECT'Lauren Reasoner',TIMESTAMP'2016-10-18 3:10:14','F30-34')SELECTname,FORMAT_TIMESTAMP('%X',finish_time)ASfinish_time,division,FORMAT_TIMESTAMP('%X',fastest_time)ASfastest_time,FORMAT_TIMESTAMP('%X',second_fastest)ASsecond_fastestFROM(SELECTname,finish_time,division,finishers,FIRST_VALUE(finish_time)OVERw1ASfastest_time,NTH_VALUE(finish_time,2)OVERw1assecond_fastestFROMfinishersWINDOWw1AS(PARTITIONBYdivisionORDERBYfinish_timeASCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING));/*-----------------+-------------+----------+--------------+----------------+ | name            | finish_time | division | fastest_time | second_fastest | +-----------------+-------------+----------+--------------+----------------+ | Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           | | Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       | | Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       | | Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       | | Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       | | Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       | | Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       | | Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       | | Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       | | Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       | +-----------------+-------------+----------+--------------+----------------*/

PERCENTILE_CONT

PERCENTILE_CONT(value_expression,percentile[{RESPECT|IGNORE}NULLS])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]

Description

Computes the specified percentile value for the value_expression, with linearinterpolation.

This function ignores NULLvalues ifRESPECT NULLS is absent. IfRESPECT NULLS is present:

  • Interpolation between twoNULL values returnsNULL.
  • Interpolation between aNULL value and a non-NULL value returns thenon-NULL value.

To learn more about theOVER clause and how to use it, seeWindow function calls.

PERCENTILE_CONT can be used with differential privacy. To learn more, seeDifferentially private aggregate functions.

Supported Argument Types

  • value_expression andpercentile must have one of the following types:
    • NUMERIC
    • BIGNUMERIC
    • FLOAT64
  • percentile must be a literal in the range[0, 1].

Return Data Type

The return data type is determined by the argument types with the followingtable.

INPUTNUMERICBIGNUMERICFLOAT64
NUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

Examples

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

SELECTPERCENTILE_CONT(x,0)OVER()ASmin,PERCENTILE_CONT(x,0.01)OVER()ASpercentile1,PERCENTILE_CONT(x,0.5)OVER()ASmedian,PERCENTILE_CONT(x,0.9)OVER()ASpercentile90,PERCENTILE_CONT(x,1)OVER()ASmaxFROMUNNEST([0,3,NULL,1,2])ASxLIMIT1;/*-----+-------------+--------+--------------+-----+  | 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 ofvalues while respecting nulls.

SELECTPERCENTILE_CONT(x,0RESPECTNULLS)OVER()ASmin,PERCENTILE_CONT(x,0.01RESPECTNULLS)OVER()ASpercentile1,PERCENTILE_CONT(x,0.5RESPECTNULLS)OVER()ASmedian,PERCENTILE_CONT(x,0.9RESPECTNULLS)OVER()ASpercentile90,PERCENTILE_CONT(x,1RESPECTNULLS)OVER()ASmaxFROMUNNEST([0,3,NULL,1,2])ASxLIMIT1;/*------+-------------+--------+--------------+-----+ | min  | percentile1 | median | percentile90 | max | +------+-------------+--------+--------------+-----+ | NULL | 0           | 1      | 2.6          | 3   | +------+-------------+--------+--------------+-----*/

PERCENTILE_DISC

PERCENTILE_DISC(value_expression,percentile[{RESPECT|IGNORE}NULLS])OVERover_clauseover_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]]

Description

Computes the specified percentile value for a discretevalue_expression. Thereturned value is the first sorted value ofvalue_expression with cumulativedistribution greater than or equal to the givenpercentile value.

This function ignoresNULLvalues unlessRESPECT NULLS is present.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Note: If you're querying a large dataset, you can compute results faster andsave resources by usingKLL functions for approximatepercentile values. For more information, seeSketches.

Supported Argument Types

  • value_expression can be any orderable type.
  • percentile must be a literal in the range[0, 1], with one of thefollowing types:
    • NUMERIC
    • BIGNUMERIC
    • FLOAT64

Return Data Type

Same type asvalue_expression.

Examples

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

SELECTx,PERCENTILE_DISC(x,0)OVER()ASmin,PERCENTILE_DISC(x,0.5)OVER()ASmedian,PERCENTILE_DISC(x,1)OVER()ASmaxFROMUNNEST(['c',NULL,'b','a'])ASx;/*------+-----+--------+-----+ | 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 ofvalues while respecting nulls.

SELECTx,PERCENTILE_DISC(x,0RESPECTNULLS)OVER()ASmin,PERCENTILE_DISC(x,0.5RESPECTNULLS)OVER()ASmedian,PERCENTILE_DISC(x,1RESPECTNULLS)OVER()ASmaxFROMUNNEST(['c',NULL,'b','a'])ASx;/*------+------+--------+-----+ | x    | min  | median | max | +------+------+--------+-----+ | c    | NULL | a      | c   | | NULL | NULL | a      | c   | | b    | NULL | a      | c   | | a    | NULL | a      | c   | +------+------+--------+-----*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-11-24 UTC.