Window Functions (MySQL Misc)

Window functions are a powerful feature in MySQL that allow you to perform aggregate calculations over a subset of rows, or "window", defined by a partition. In this tutorial, we'll cover the syntax, examples, and uses of window functions in MySQL.

Syntax

The syntax for using a window function in MySQL is as follows:

SELECT column1, column2, ..., function(column) OVER (PARTITION BY partition_columns ORDER BY order_columns [ASC/DESC]) FROM table_name;

The basic components are:

• SELECT: The columns selected in the query.
• function(column): The aggregate function that is applied to the window.
• OVER: A keyword that indicates that we are using a window function.
• PARTITION BY: Defines the partitioning columns that separate the "windows".
• ORDER BY: Defines the order in which rows are processed.
• ASC/DESC: Determines the direction of the sort.

Example

Let's say we have a table sales that has columns date, product, and sales_amount. We want to calculate the total sales for each product for the current month, as well as for all months.

SELECT
    date,
    product,
    sales_amount,
    SUM(sales_amount)
        OVER(PARTITION BY product
             ORDER BY date
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_sales_month,
    SUM(sales_amount)
        OVER(PARTITION BY product
             ORDER BY date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_sales_all
FROM sales

We use the SUM function as the aggregate function. We partition the data by product, order by date, and define two different windows - one for the current month (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), and one for all months (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

Output

The output of the above query will be:

+------------+---------+--------------+-------------------+----------------+
| date       | product | sales_amount | total_sales_month | total_sales_all |
+------------+---------+--------------+-------------------+----------------+
| 2021-06-01 | Widget1 | 5000         | 5000              | 90000          |
| 2021-06-05 | Widget1 | 2000         | 7000              | 90000          |
| 2021-07-12 | Widget1 | 50000        | 50000             | 90000          |
| 2021-06-12 | Widget2 | 10000        | 10000             | 150000         |
| 2021-06-15 | Widget2 | 50000        | 60000             | 150000         |
| 2021-07-05 | Widget2 | 40000        | 100000            | 150000         |
| 2021-07-15 | Widget2 | 50000        | 150000            | 150000         |
| 2021-06-01 | Widget3 | 10000        | 10000             | 10000          |
| 2021-07-01 | Widget3 | 5000         | 15000             | 10000          |
+------------+---------+--------------+-------------------+----------------+

Explanation

In the example above, we use the SUM function as the aggregate function. We partition the table by product, order the rows by date, and define two different windows:

• total_sales_month: This window is calculated for each month (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and shows the running total sales for each product for the current month.
• total_sales_all: This window is calculated over all months (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) and shows the total sales for each product for all months.

Use

Window functions are helpful for performing more complex calculations on data than simple aggregates (like SUM, AVG, MIN, MAX, etc.) by allowing the calculation of values based on the groupings defined by the PARTITION BY clause.

You can use window functions to calculate running totals and averages, cumulative sums, median values, and more.

Important Points

• A window function operates on a subset of rows, known as the "window".
• The PARTITION BY clause divides the rows into partitions based on the specified columns.
• The ORDER BY clause determines the order in which the rows are processed.
• The ROWS and RANGE keywords determine how to form the window.
• There are many different functions that can be used as aggregate functions in window functions.

Summary

In this tutorial, we discussed the syntax of window functions in MySQL, as well as provided an example of how to use them to perform complex calculations on data. We also covered the output, explanation, use cases, and important points related to window functions in MySQL.