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.
Tag: how to
Get the Most Populous City in Each District When Using a Window Function in SQL
While it’s true that we can use the SQL MAX() function to get the maximum value in a column, what if we want to return the value from another column instead of the actual maximum value itself? And what if we want it partitioned by category, so that it’s based on the maximum value from each category? And what if we want all values listed out, including those that aren’t the most populous?
In the following example, we use a window function to solve this problem.
Continue reading2 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.
Understanding the DENSE_RANK() Function in PostgreSQL
PostgreSQL has a window function called dense_rank() that returns the rank of the current row, without gaps.
It works the same way that the rank() function works, but without gaps (the rank() function includes gaps).
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.
Continue readingAdd a Column of Row Numbers in PostgreSQL: The ROW_NUMBER() Function
In PostgreSQL, we can use the row_number() function to get each row’s number within its partition. This allows us to create a column with incrementing row numbers that reset with each new partition.
The row_number() function is a window function that’s specifically designed to return the number of the current row within its partition, starting at 1 and incrementing sequentially.
Fix ERROR 3581 “A window which depends on another cannot define partitioning” in MySQL
If you’re getting error 3581 in MySQL, which reads “A window which depends on another cannot define partitioning“, it’s probably because you’re using the PARTITION BY clause in a window function that refers to a named window.
To fix this issue, don’t use the PARTITION BY clause when referring to a named window.
2 Ways to Return Totals and Subtotals in SQL
When we have a query that returns a column with numerical data such as prices or population, we can use the following methods to automatically calculate the totals and subtotals within a category.
Continue reading4 Functions that Return Information about JSON Values in MySQL
MySQL provides us with a wide range of functions for working with JSON documents. Below are four MySQL functions that we can use to get information about JSON values.
Continue readingCompute a Moving Average in SQL
SQL provides us with an easy way to automatically compute the moving average of a given column.
The moving average (also known as the rolling average, running average, moving mean (MM), or rolling mean) is a series of averages of different selections of the full data set. The moving average changes as we move through the data set. We can add the moving average to our SQL query result sets to see how it changes across the result set.
Continue reading