Database Management Systems
In MySQL, RANK() is a window function that returns the rank of the current row within its partition, with gaps.
By “gaps” this means that it returns noncontiguous rank numbers whenever there are peers. Peers are considered ties and receive the same rank, but in such cases, we get a gap between this rank value and the next rank value.
In MySQL, PERCENT_RANK() is a window function that returns the percentage of partition values less than the value in the current row, excluding the highest value.
We can use PERCENT_RANK() to evaluate the relative standing of a value within a query result set or partition. Return values range from 0 to 1.
In MySQL, CUME_DIST() is a window function that returns the cumulative distribution of a value within a group of values. This is the percentage of partition values less than or equal to the value in the current row. The return values range from 0 to 1.
If you’re getting error 3579 in MySQL, which reads something like “Window name ‘wf3’ is not defined“, it’s probably because you’re referring to a named window that doesn’t exist.
To fix this issue, make sure you refer to a named window that exists.
In MySQL, we can use the sys.sys_get_config() function to get a configuration option value from the sys_config table.
If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.
When we create or alter a CHECK constraint in MySQL, we have the option of setting it to ENFORCED or NOT ENFORCED. This determines whether or not MySQL will enforce the rules of the constraint whenever data that violates the constraint tries to enter the database.
We can use the ALTER TABLE statement to change the enforcement state of an existing CHECK constraint in MySQL, as shown in the following example.
If you’re getting MySQL error 1140, which reads something like “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘world.City.District’; this is incompatible with sql_mode=only_full_group_by“, it could be that you need to introduce a window function to the query.
This error can happen when we want multiple rows to contain aggregate values. For example, we might want to return subtotals of all rows within a group of rows. A window function can help us achieve this outcome.
When we create a CHECK constraint in MySQL, we have the option of defining it as ENFORCED or NOT ENFORCED.
MySQL CHECK constraints are enforced by default, so unless we explicitly define the constraint as NOT ENFORCED, it will automatically be enforced.
In any case, we can use either of the following two methods to check whether or not an existing CHECK constraint is enforced.
If you’re getting MySQL error 3582, which reads something like “Window ‘w1’ has a frame definition, so cannot be referenced by another window“, it’s probably because you’re referencing a named window that has a frame clause.
This can happen when we use the WINDOW clause to define a named window for a window function, and we include a frame clause in that window. When we then try to reference that window, MySQL tells us that we can’t, due to the frame clause.