In SQL databases, the RANK() function is a window function that returns the rank of the current row within its partition, with gaps.
By “with gaps” I mean that if there are any ties for a given rank, there will be a gap between that rank value and the next rank value.
If you don’t want such gaps, use the DENSE_RANK() function instead, as it returns the rank without gaps.
Many relational database management systems (RDBMSs) provide us with a range of window functions.
The CUME_DIST() function is a window function that’s commonly implemented in RDBMSs for the purpose of calculating the cumulative distribution across a data set. In other words, it calculates the relative position of a specified value in a group of values.
In PostgreSQL the last_value() function returns the value from the last row of the current window frame.
In PostgreSQL the first_value() function returns the value from the first row in the current window frame.
We can use this function to get the value from the first row in a result set, or from the first row in the current partition, or some other window frame that’s been specified.
Continue readingIn MySQL, we can use the sys.ps_is_consumer_enabled() function to check whether a given Performance Schema consumer is enabled.
The function returns YES or NO, depending on whether or not the specified Performance Schema consumer is enabled. It returns NULL if the argument is NULL.
PostgreSQL has an ordered-set aggregate function called mode() that allows us to get the mode from a given column.
The mode is the most frequently occurring value.
Null values are ignored, so if null occurs the most, the mode() function will return the second most common value.
In MariaDB, the MEDIAN() function returns the median value of a range of values.
We can use the MEDIAN() function in our queries to get a column’s median value across its partition or the whole result set.
In SQL Server, PERCENTILE_DISC() is a window function that returns a percentile value based on a discrete distribution of the input column. Basically, it returns the first value in the set whose ordered position is the same or more than the specified fraction.
The output of PERCENTILE_DISC() is equal to a specific column value (unlike the PERCENTILE_CONT() function, which could calculate a value that isn’t in the column).
When we call PERCENTILE_DISC() we specify the percentile to use. It then performs its calculation based on that percentile.
In SQL Server, PERCENTILE_CONT() is a window function that calculates a percentile based on a continuous distribution of the column value.
When we call the function, we specify the percentile to use. It then performs its calculation based on that percentile.
Continue readingIn SQL Server, LEAD() is a window function that allows us to access a value from a later row in the same result set, without the need to perform a self-join.
We specify the row as an offset from the current row. An offset of 1 means it gets the value from the next row, an offset of 2 means two rows forward, and so on.