In SQL Server, PERCENT_RANK() is a window function that calculates and returns the relative rank of a row within a group of rows, expressed as a value between 0 and 1.
In other words, it returns the percentage of partition values less than the value in the current row, excluding the highest value.
Continue readingMySQL 8.0.17 introduced a couple of functions that allow us to validate JSON documents against their schemas. These are listed below, with examples.
Continue readingIn SQL Server, the CUME_DIST() function calculates and returns the cumulative distribution of a value within a group of values. This is the relative position of a specified value in a group of values.
Below is a list of functions that can be used as window functions in MySQL.
Some are nonaggregate functions that can only be used as window functions, while others are aggregate functions that can be used as window functions if required.
Continue readingIn PostgreSQL the lead() function returns the value from a subsequent row to the current row, specified by the given offset.
The offset specifies how many rows after the current row to get the value from. For example, an offset of 1 gets the value from the next row.
In PostgreSQL the lag() function returns the value from a previous row, specified by the given offset.
The offset specifies how many rows prior to the current row to get the value from. For example, an offset of 1 gets the value from the previous row.
In PostgreSQL the nth_value() function is a window function that returns the value from the given row of the current window frame. We provide the column and row number as an argument when we call the function.
In PostgreSQL, we can use the cume_dist() function to return the cumulative distribution of a value within a group of values.
It calculates this as follows: (the number of partition rows preceding or peers with current row) / (total partition rows).
The return value ranges from 1/N to 1.
In PostgreSQL, we can use the percent_rank() function to return the relative rank of each row, expressed as a percentage ranging from 0 to 1 inclusive.
In PostgreSQL, the ntile() function is a window function that divides a partition into the specified number of groups (buckets), distributing the rows as equally as possible, and returns the bucket number of the current row within its partition.