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.
DBMS
Database Management Systems
Using the LEAD() Function to Get a Value from a Later Row in PostgreSQL
In 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.
Using the LAG() Function to Get a Value from a Previous Row in PostgreSQL
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.
Understanding the NTH_VALUE() Function in PostgreSQL
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.
Using the CUME_DIST() Function to Get the Cumulative Distribution in PostgreSQL
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.
Overview of the PERCENT_RANK() Function in PostgreSQL
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.
Using the NTILE() Function to Divide a Partition into Buckets in PostgreSQL
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.
2 Ways to List CHECK Constraints in MySQL
Ever since the release of MySQL 8.0.16, we’ve had the ability to create CHECK constraints in MySQL. At some point, we may want to return a list of CHECK constraints that have been created in a database or against a given table.
Fortunately, we can use the information_schema.check_constraints view to do just that.
We can alternatively use the information_schema.table_constraints view to get the same info.
Using the RANK() Function to Add a “Rank” Column in PostgreSQL
PostgreSQL has a window function called rank() that returns the rank of the current row, with gaps.
“With gaps” means that it returns the same rank for any ties (i.e. two or more rows with the same value), but then subsequent ranks jump forward to account for the ties.
This means that there’s the potential for noncontiguous rank values. For example it could go 1, 2, 5, etc if several rows are ranked at 2. If there are no ties, then the rank values will be contiguous.