In PostgreSQL the first_value() function returns the value from the first row in the current window frame.
We can use this function to get the value from the first row in a result set, or from the first row in the current partition, or some other window frame that’s been specified.
Continue readingIn MySQL, we can use the sys.ps_is_consumer_enabled() function to check whether a given Performance Schema consumer is enabled.
The function returns YES or NO, depending on whether or not the specified Performance Schema consumer is enabled. It returns NULL if the argument is NULL.
PostgreSQL has an ordered-set aggregate function called mode() that allows us to get the mode from a given column.
The mode is the most frequently occurring value.
Null values are ignored, so if null occurs the most, the mode() function will return the second most common value.
If you’re getting an error that reads “ERROR: invalid input syntax for type integer” in PostgreSQL, it’s probably because you’re calling the ntile() function with an argument of the wrong data type.
This error can occur when we pass the wrong type to the ntile() function. We can get a different error depending on the type, but this error can occur when we pass a string.
To fix this issue, be sure to pass a positive integer to the function.
Continue readingIn MariaDB, the MEDIAN() function returns the median value of a range of values.
We can use the MEDIAN() function in our queries to get a column’s median value across its partition or the whole result set.
In SQL Server, PERCENTILE_DISC() is a window function that returns a percentile value based on a discrete distribution of the input column. Basically, it returns the first value in the set whose ordered position is the same or more than the specified fraction.
The output of PERCENTILE_DISC() is equal to a specific column value (unlike the PERCENTILE_CONT() function, which could calculate a value that isn’t in the column).
When we call PERCENTILE_DISC() we specify the percentile to use. It then performs its calculation based on that percentile.
In SQL Server, if you’re getting an error that reads “The function ‘PERCENT_RANK’ must have an OVER clause”, it’s because you’re calling the PERCENT_RANK() function without an OVER clause.
The PERCENT_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, be sure to include an OVER clause when calling the PERCENT_RANK() function.
Most of the major RDBMSs allow us to create window functions in SQL queries.
A window function is a special aggregate-like function that enables partitioning and ordering of data within a result set. Unlike regular aggregate functions, which collapse multiple rows into a single result, window functions retain individual rows in the output, augmenting them with additional computed values.
The following example demonstrates how to create a window function in SQL.
Continue readingIn SQL Server, PERCENTILE_CONT() is a window function that calculates a percentile based on a continuous distribution of the column value.
When we call the function, we specify the percentile to use. It then performs its calculation based on that percentile.
Continue readingIn SQL Server, LEAD() is a window function that allows us to access a value from a later row in the same result set, without the need to perform a self-join.
We specify the row as an offset from the current row. An offset of 1 means it gets the value from the next row, an offset of 2 means two rows forward, and so on.