# Window

Function Name | Description |
---|---|

COUNT | Returns the total number of records for the specified expression. |

COVAR_POP | Returns the population covariance for non-NULL pairs across all input values. |

COVAR_SAMP | Returns the sample covariance for non-NULL pairs across all input values. |

CUME_DIST | Returns the cumulative distribution of the current row with regard to other values within the same window partition. |

DENSE_RANK | Returns the rank of the current row within its partition and ordering. Rows that are equal will have the same rank. |

FIRST_VALUE | Returns the first value within an ordered group of a result set. |

LAG | Returns the row before the current one in a partition based on the `ORDER BY` clause without the need for a self-join. If there are no rows, this function returns `NULL` . |

LEAD | Returns the row after the current one in the same result set without the need for a self-join. If there are no rows, this function returns `NULL` . |

MAX | Returns the maximum value among the non-NULL input expressions. |

MIN | Returns the minimum value among the non-NULL input expressions. |

NDV | Returns an approximate distinct value number, similar to `COUNT(DISTINCT col)` . NDV can return results faster than using the combination of COUNT and DISTINCT while using a constant amount of memory, resulting in less memory usage for columns with high cardinality. |

NTILE | Equally splits the rows in each partition into ranked parts specified by the integer value and starting from 1. This function requires the `ORDER BY` clause. |

PERCENT_RANK | Returns the relative rank of the current row in the partition based on the `ORDER BY` clause. The displayed percentage ranges from 0.0 to 1.0. |

RANK | Returns the rank of the current row within its partition and placement order. Rows that are equal have the same rank. However, the count of tied rows is added to the next rank, instead of being incremented by one. The rank value starts at 1 and increases sequentially. |

ROW_NUMBER | Returns the row number for the current row based on the `ORDER BY` clause within each partition. Rows containing identical values receive different row numbers. |

SUM | Returns the sum of non-NULL input expressions. |

VAR_POP | Returns the population variance of non-NULL records. |

VAR_SAMP | Returns the sample variance of non-NULL records. |

## Window Function Syntax

A window function performs a calculation across a set of table rows that has some relationship to the current row. This is comparable to how an aggregate function can run a calculation. The difference is that a window function does not group rows into a single output row. With a window function, the rows retain their separate identities.

A window function call uses the `OVER()`

clause directly following the window function's name and argument(s). The `OVER()`

clause may use the following optional arguments:

`PARTITION BY`

: Defines multiple window partitions.`ORDER BY`

: Orders rows within each partition.

`window_function (expression) OVER (`

[ PARTITION BY expressionlist ]

[ ORDER BY fieldlist ] )

## Aggregate Window Functions

The `OVER()`

clause can be used with regular aggregate functions such as:

- AVG
- COUNT
- MAX
- MIN
- SUM

## Examples

The following example uses the sample table provided below to show the `OVER()`

clause used with the `SUM`

aggregate function.

`select `

product_id,

branch,

amount,

SUM(amount) OVER (partition by branch order by amount DESC) as total_branch_amount from transactions

product_id | branch | amount | total_branch_amount |
---|---|---|---|

Product1 | A | 30.0 | 30.0 |

Product2 | A | 24.0 | 54.0 |

Product3 | A | 2.0 | 56.0 |

Product3 | B | 45.0 | 45.0 |

Product2 | B | 10.0 | 55.0 |

Product1 | B | 3.0 | 58.0 |

## General-Purpose Window Functions

The `OVER()`

clause can be used with the following functions:

Function | Return Type | Description |
---|---|---|

CUME_DIST() | Double | Calculates the cumulative distribution of the current row within the window partition. |

DENSE_RANK() | BIGINT | Returns the rank of the current row within its partition and ordering. Rows that are equal have the same rank. |

LAG() | Same as input | Returns the row before the current one in a partition. If there are no rows, returns null. |

LEAD() | Same as input | Returns the row after the current one in a partition. If there are no rows, returns null. |

NTILE([integer] ntile) | Integer | NTILE function equally splits the rows in each partition into N ranked parts. Has to be used with an `ORDER BY` clause. |

PERCENT_RANK() | Double | Returns the percent rank of the current row in the partition based on the order by clause. |

RANK() | BIGINT | Returns the rank of the current row within its partition and ordering. Rows that are equal have the same rank. However, the count of tied rows is added to the next rank, instead of being incremented by just one. |

ROW_NUMBER() | BIGINT | Returns the row number for the current row based on the order by clause within each partition. |

For more information about Window Functions, see SQL Window Functions.