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.
Tag: functions
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.
Understanding the sys.extract_schema_from_file_name() Function in MySQL
In MySQL, the sys.extract_schema_from_file_name() function is a stored function in the sys schema.
It accepts a path name, and returns the path component that represents the schema name.
This enables us to get the schema from a given file path.
Continue reading3 JSON Utility Functions in MySQL
MySQL provides us with a few utility functions that act on JSON values, or strings that can be parsed as JSON values.
Below are three JSON utility functions that we can use for such tasks.
Continue readingUsing the sys.format_statement() Function in MySQL to Truncate a SQL Statement
In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.
This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).
Continue readingUnderstanding the PS_THREAD_ID() Function in MySQL
In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.
If no thread ID exists for the connection, then NULL is returned.
The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.