In MySQL, the FIRST_VALUE() function is a window function that returns the value of the given expression from the first row of the window frame.
Tag: functions
Understanding the DENSE_RANK() Function in MySQL
In MySQL, the DENSE_RANK() function is a window function that returns the rank of the current row within its partition, without gaps.
By “without gaps” this means that it returns contiguous rank numbers whenever there are peers. Peers are considered ties and receive the same rank. In such cases, the next rank value is one greater than the current one (i.e. the one that the peers receive).
Continue readingUnderstanding the RANK() Function in MySQL
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.
Continue readingUnderstanding the PERCENT_RANK() Function in MySQL
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.
Understanding the CUME_DIST() Function in MySQL
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.
Understanding the sys.sys_get_config() Function in MySQL
In MySQL, we can use the sys.sys_get_config() function to get a configuration option value from the sys_config table.
Introduction to the OVER Clause in SQL
When running SQL database queries, sometimes we need to use a window function in order to get the results we’re looking for. A window function is an aggregate-like function that enables partitioning and ordering of data within a result set.
The OVER clause is what enables us to create a window function.
The examples below demonstrate how we can incorporate the OVER clause in our SQL queries.
Understanding the sys.quote_identifier() Function in MySQL
In MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.
The string is returned enclosed by backticks (`), with each instance of a backtick doubled.
This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.
Continue readingUnderstanding Window Functions in SQL
Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.
In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.
Continue readingIntroduction to the sys.ps_thread_stack() Function in MySQL
In MySQL, the sys.ps_thread_stack() function returns a JSON formatted stack of all statements, stages, and events within the Performance Schema for a given thread ID.