# Window

## Index

[Row Number Functions](#row-number-functions)

* [ROW\_NUMBER](#row_number)

[Rank Functions](#rank-functions)

* [RANK](#rank)
* [PERCENT\_RANK](#percent_rank)
* [DENSE\_RANK](#dense_rank)
* [CUME\_DIST](#cume_dist)

[Lead or Lag Functions](#lead-or-lag-functions)

* [LEAD](#lead)
* [LAG](#lag)

[Nth Value Functions](#nth-value-functions)

* [FIRST\_VALUE](#first_value)
* [LAST\_VALUE](#last_value)
* [NTH\_VALUE](#nth_value)

[Other Window Functions](#other-window-functions)

* [NTILE](#ntile)
* [RATIO\_*TO*\_REPORT](#ratio_to_report)

## Details

### Row  Number Functions

#### ROW\_NUMBER

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
ROW_NUMBER() OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

Return the number of the current row starting with 1. Window frame clause is not allowed for this function.

Example:

```sql
SELECT ROW_NUMBER() OVER (), * FROM TEST;
SELECT ROW_NUMBER() OVER (ORDER BY ID), * FROM TEST;
SELECT ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

### Rank Functions

#### RANK

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
RANK() OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

Returns the rank of the current row. The rank of a row is the number of  rows that precede this row plus 1. If two or more rows have the same values in **`ORDER BY`** columns, these rows get the same rank from the first row with same values. <mark style="color:red;">It means that gaps in ranks are possible</mark>. This function requires window order clause. Window frame clause is not allowed for this function.

See **`Rank`** aggregate for a hypothetical set function with the same name.

Example:

```sql
SELECT RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

#### DENSE\_RANK

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
RANK() OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

Return the dense rank of the current row. The rank of a row is the number of groups of rows with the same values in **`ORDER BY`** columns that precede group with row plug 1. If two or more rows have the same values in **`ORDER BY`** columns, these rows get the same rank. <mark style="color:red;">Gaps in ranks are not possible</mark>. This function requires window order clause. Window frame clause is not allowed for this function.

See **`DENSE_RANK`** aggregate for a hypothetical set function with the same name.

Example:

```sql
SELECT DENSE_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT DENSE_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

#### PERCENT\_RANK

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
PERCENT_RANK() OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

Returns the relative rank of the current row. The relative rank is calculated as (**`RANK`** - 1) / (**`NR`** - 1), where **`RANK`** is a rank of the row and **`NR`** is a number of rows in window partition with this row. Note that result is always 0 if window order clause is not specified. Window frame clause is not allowed for this function.

See PERENT\_RANK aggregate for a hypothetical set function with the same name.

Example:

```sql
SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

#### CUME\_DIST

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
CUME_DIST() OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

Returns the relative rank of the current row. The relative rank is calculated as **`NP`** / **`NR`** where **`NP`** is a number of rows that precede the current row or have the same values in **`ORDER BY`** columns and **`NR`** is a number of rows in window partition with this row. Note that result is always 1 if window order clause is not specified. Window frame clause is not allowed for this function.

Example:

```sql
SELECT CUME_DIST() OVER (ORDER BY ID), * FROM TEST; 
SELECT CUME_DIST() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

### Lead or Lag Functions

#### LEAD

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
LEAD(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

TODO

#### LAG

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
LAG(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

TODO

### Nth Value Functions

#### FIRST\_VALUE

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
FIRST_VALUE(value) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

TODO

#### LAST\_VALUE

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
LAST_VALUE(value) [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

TODO

#### NTH\_VALUE

{% hint style="info" %}
{% code overflow="wrap" %}

```sql
NTH_VALUE(value, nInt) [FROM {FIRST|LAST}] [{RESPECT|IGNORE} NULLS]
OVER windowNameOrSpecification
```

{% endcode %}
{% endhint %}

TODO

### Other Window Functions

#### **NTILE**

{% hint style="info" %}

<pre class="language-sql" data-overflow="wrap"><code class="lang-sql"><strong>NTILE(long) OVER WindowNameOrSpecification
</strong></code></pre>

{% endhint %}

Distributes the rows into a specified number of groups. The Number of groups should be a positive long value. **`NTLE`** returns the 1-based number of the group to which the current row belongs. First groups will have more rows if the number of rows is not divisible by number of groups. For example, if 5 rows are distributed into 2 groups this function returns 1 for the first 3 rows and 2 for the last rows. This function requires window order clause. Window frame clause is not allowed for this function.

Example:

```sql
SELECT NTILE(10) OVER (ORDER BY ID), * FROM TEST;
SELECT NTILE(10) OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```

#### **RATIO\_TO\_REPORT**

{% hint style="info" %}

<pre class="language-sql"><code class="lang-sql"><strong>RATIO_TO_REPORT(VALUE)
</strong><strong>OVER WindowNameOrSpecification
</strong></code></pre>

{% endhint %}

Returns the ratio of a value to the sum of all values. If the argument is **`NULL`** or the sum of all values is 0, then the value of the function is **`NULL`**. Window ordering and window frame clauses are not allowed for this function.

Note: Window function in Guinsoo may require a lot of memory for large queries.

Example:

```sql
SELECT X, RATIO_TO_REPORT(X) OVER (PARTITION BY CATEGORY), CATEGORY FROM TEST;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://ciusji.gitbook.io/guinsoo/reference/window.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.