MySQL includes some nonaggregate window functions that allow us to get a value from a specific row. We can use such functions to do things like, compare the value in the specified row with the value in the current row, even if both values are in the same column.
Below are five functions that we can use to do this.
Continue readingMany SQL databases have a window function called NTILE() function that divides a rowset or partition into a given number of groups (buckets). The function typically returns the bucket number of the current row within its partition.
The LAG() function is a window function that’s available in many SQL databases. It 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.
In other words, the LAG() function returns a value from a previous row.
Many relational database management systems (RDBMSs) have a LEAD() function that allows us to retrieve a value from a following row.
The SQL LEAD() function 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.
Many RDBMSs include both a rank() and a dense_rank() function in their list of window function offerings. At first glance, these functions might appear to do the same thing. However, there’s one important difference between these functions, and you will definitely need to be aware of this difference when choosing which function to use.
The SQL ROW_NUMBER() function is a window function that assigns and returns a row number of each row in a query partition or result set. Numbering starts at 1 and increments sequentially.
This enables us to add a “row number” column to our queries.
Continue readingSome relational database management systems (RDBMSs) have a MEDIAN() function that calculates the median value within a range of values.
It returns the middle value or an interpolated value that would be the middle value once the values are sorted.
The SQL MEDIAN() function typically operates on numeric expressions, but depending on the RDBMS, may also operate on other data types, such as datetime expressions.
Depending on the RDBMS, the MEDIAN() function can be used as a window function, as an aggregate function, or as both.
Some SQL relational database management systems (RDBMSs) have a MODE() function that returns the most frequently occurring value from all row values in a column.
The mode is the value that appears most frequently in a data set.
Continue readingSome SQL databases have a PERCENTILE_DISC() function that calculates a percentile based on a discrete distribution of a range of column values.
We specify the percentile to use when we call the function.
Depending on the DBMS, PERCENTILE_DISC() can be used as a window function, as an aggregate function, or as both.
The PERCENTILE_DISC() function always returns a value from the underlying data. This is in contrast to the PERCENTILE_CONT() function, which can interpolate between adjacent values to return a value that’s not in the underlying data.
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.