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).
Understanding 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.
Understanding 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.
Compute a Moving Average in SQL
SQL provides us with an easy way to automatically compute the moving average of a given column.
The moving average (also known as the rolling average, running average, moving mean (MM), or rolling mean) is a series of averages of different selections of the full data set. The moving average changes as we move through the data set. We can add the moving average to our SQL query result sets to see how it changes across the result set.
Fix ERROR 3579 “Window name ‘…’ is not defined” in MySQL
If you’re getting error 3579 in MySQL, which reads something like “Window name ‘wf3’ is not defined“, it’s probably because you’re referring to a named window that doesn’t exist.
To fix this issue, make sure you refer to a named window that exists.
Create a Running Total in SQL
We can use SQL to create a running total of a given column. By this I mean, we can create a column that calculates the cumulative sum of a given column.
The running total/cumulative sum is the sum of all rows up until the current row. The result is that the sum increases (or decreases in the case of negative values) with each row returned in the result set. Each row’s value is added to the cumulative amount from all prior rows, so for any given row, we get the total sum for all rows up to that point – the “running total”.
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.
Fix ERROR 3580 “There is a circularity in the window dependency graph” in MySQL
If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.