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

This modified text is an extract of the original Stack Overflow Documentation created by following contributors and released under CC BY-SA 3.0