Some relational database management systems (RDBMSs) have a PERCENTILE_CONT() function that calculates a percentile based on a continuous distribution across a range of column values.
We specify the percentile to use when we call the function.
Depending on the RDBMS, PERCENTILE_CONT() can be used as a window function or an aggregate function, or as both.
Many relational database management systems (RDBMSs) provide a window function called PERCENT_RANK() that returns the relative rank of a row within a group of rows.
The relative rank is expressed as a value between 0 and 1.
Continue readingIf you’re getting an error message that reads “The function ‘LEAD’ must have an OVER clause with ORDER BY” in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause when using the LEAD() function.
The LEAD() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
Most RDBMSs have functions that make it easy to calculate the median value from a column in our queries.
Below are three SQL functions we can use to calculate the median value in a column.
Continue readingSome SQL databases have a window function called NTH_VALUE() that allows us to get a value from a given row in the window frame, based on the row number.
More specifically, the function 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.
Continue readingIn SQL databases, LAST_VALUE() is a window function that returns the last value in an ordered set of values. It allows us to get a value from the last row of a query result set or partition.
You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because in many/most DBMSs, the default window frame ends with the current row.
If you’re getting an error that reads “function mode() does not exist” in PostgreSQL, it could be that you’re using the wrong syntax with this function.
The mode() function requires a WITHIN GROUP clause, and we can get the above error if we remove that clause.
In this case, we can fix the error by adding a valid WITHIN GROUP clause.
In SQL databases, the FIRST_VALUE() function is a window function that returns the first value in an ordered set of values.
The exact syntax will probably depend on your DBMS, but it will usually require an OVER clause to determine how the rowset is partitioned and ordered before the window function is applied.
If you’re getting an error that reads “The function ‘ROW_NUMBER’ must have an OVER clause” in SQL Server, it’s probably because you’re calling the ROW_NUMBER() function without an OVER clause.
Window functions such as ROW_NUMBER() require an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause when calling the ROW_NUMBER() function.
Many relational database management systems (RDBMSs) provide a DENSE_RANK() function that we can use in our SQL queries. The SQL DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.
The “without gaps” part is what distinguishes the DENSE_RANK() function from the RANK() function.
DENSE_RANK() returns contiguous rank numbers whenever there are ties, whereas RANK() will leave a gap between the tie and the next rank, resulting in noncontiguous rank numbers.