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.
Tag: what is
Introduction to the LAST_VALUE() Function in MySQL
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.
Introduction to the FIRST_VALUE() Function in MySQL
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.
Understanding the DENSE_RANK() Function in MySQL
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).
Continue readingUnderstanding the RANK() Function in MySQL
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.
Continue readingUnderstanding the PERCENT_RANK() Function in MySQL
In MySQL, PERCENT_RANK() is a window function that returns the percentage of partition values less than the value in the current row, excluding the highest value.
We can use PERCENT_RANK() to evaluate the relative standing of a value within a query result set or partition. Return values range from 0 to 1.
Understanding the CUME_DIST() Function in MySQL
In MySQL, CUME_DIST() is a window function that returns the cumulative distribution of a value within a group of values. This is the percentage of partition values less than or equal to the value in the current row. The return values range from 0 to 1.
Understanding the sys.sys_get_config() Function in MySQL
In MySQL, we can use the sys.sys_get_config() function to get a configuration option value from the sys_config table.
Understanding the PARTITION BY Clause in SQL
Sometimes when we run SQL queries we might want to group the results by a given column. A common way to do this is with the GROUP BY clause.
But sometimes the GROUP BY clause isn’t enough.
Sometimes we want the results presented in a way that the GROUP BY clause doesn’t cater for. The GROUP BY is perfect if we want to collapse groups of rows into a single row, so that we can get an aggregate value for each set of collapsed rows. But sometimes we don’t want this. Sometimes we want to see all of the rows, as well as the aggregate values.
This is where the PARTITION BY clause comes in.
Understanding the WINDOW Clause in MySQL
In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.
Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.
When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.