In MySQL, we can use the LEAD() function to get the value of a subsequent row. For example we can get a value from the next row, or the one after that, and so on.
This enables us to do things like compute the difference between a value in the current row and a value in a following row. We can do this even if both values are in the same column.
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 readingBelow 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 readingEver 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.
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.
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 readingIn MySQL, ROW_NUMBER() is a window function that returns the number of the current row within its partition. Numbering starts at 1 and increments sequentially.
In MySQL, the NTILE() function is a window function that divides a partition into a given number of groups (buckets) and returns the bucket number of the current row within its partition.
In MySQL, the LEAD() function is a window function that 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.
Basically, it returns the value from a later row.
Continue readingIn MySQL, the LAG() function is a window function that 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.
Basically, it returns the value from a previous row.
Continue reading