The WINDOW clause is an optional clause that we can use in our SQL queries to create a named window. The named window can then be used as part of a window function.
When creating a window function, a SQL developer will often define it directly in the OVER clause. But that’s not the only way to do it. We can alternatively use the WINDOW clause to define it in a named window, and then refer to that named window in the OVER clause.
In MySQL, ROW_NUMBER() is a window function that returns the number of the current row within its partition. Numbering starts at 1 and increments sequentially.
In MySQL, the NTILE() function is a window function that divides a partition into a given number of groups (buckets) and returns the bucket number of the current row within its partition.
In MySQL, the LEAD() function is a window function that returns the value of a given expression from the row that leads (follows) the current row by a given number of rows within its partition.
Basically, it returns the value from a later row.
In MySQL, the LAG() function is a window function that returns the value of a given expression from the row that lags (precedes) the current row by a given number of rows within its partition.
Basically, it returns the value from a previous row.
In MySQL, the NTH_VALUE() function is a window function that returns the value of a given expression from the from the N-th row of the window frame, where N is a number that we specify when calling the function.
In MySQL, the LAST_VALUE() function is a window function that returns the value of the given expression from the last row of the window frame.
In MySQL, the FIRST_VALUE() function is a window function that returns the value of the given expression from the first row of the window frame.
In MySQL, the DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.
By “without gaps” this means that it returns contiguous rank numbers whenever there are peers. Peers are considered ties and receive the same rank. In such cases, the next rank value is one greater than the current one (i.e. the one that the peers receive).
In MySQL, RANK() is a window function that returns the rank of the current row within its partition, with gaps.
By “gaps” this means that it returns noncontiguous rank numbers whenever there are peers. Peers are considered ties and receive the same rank, but in such cases, we get a gap between this rank value and the next rank value.