# Window Functions

## column values vs dense_rank vs rank vs row_number

here you can find the functions.

With the table wf_example created in previous example, run:

The result is:

• dense_rank orders VALUES of i by appearance in window. `i=1` appears, so first row has dense_rank, next and third i value does not change, so it is `dense_rank` shows 1 - FIRST value not changed. fourth row `i=2`, it is second value of i met, so `dense_rank` shows 2, andso for the next row. Then it meets value `i=3` at 6th row, so it show 3. Same for the rest two values of i. So the last value of `dense_rank` is the number of distinct values of i.

• row_number orders ROWS as they are listed.

• rank Not to confuse with `dense_rank` this function orders ROW NUMBER of i values. So it starts same with three ones, but has next value 4, which means `i=2` (new value) was met at row 4. Same `i=3` was met at row 6. Etc..

## generic example

Preparing data:

Running:

Result:

Explanation:

dist_by_i: `dense_rank() over (order by i)` is like a row_number per distinct values. Can be used for the number of distinct values of i (`count(DISTINCT i)` wold not work). Just use the maximum value.

prev_t: `lag(t) over ()` is a previous value of t over the whole window. mind that it is null for the first row.

nth: `nth_value(i, 6) over ()` is the value of sixth rows column i over the whole window

num_by_i: `count(true) over (partition by i)` is an amount of rows for each value of i

num_all: `count(true) over ()` is an amount of rows over a whole window

ntile: `ntile(3) over()` splits the whole window to 3 (as much as possible) equal in quantity parts