SQL Functions Window Functions

Syntax

<function name> OVER (PARTITION BY <partition column> ORDER BY <order column> ROWS <n> PRECEDING/FOLLOWING)

Example

Let's suppose we have a table named sales with the following data:

| id | region | month | sales |
|----|--------|-------|-------|
| 1  | A      | Jan   | 100   |
| 2  | A      | Feb   | 200   |
| 3  | A      | Mar   | 300   |
| 4  | B      | Jan   | 50    |
| 5  | B      | Feb   | 75    |
| 6  | B      | Mar   | 100   |

Now, let's suppose we want to calculate the running total of sales per region. We can achieve this using the SUM window function:

SELECT id, region, month, sales, SUM(sales) OVER (PARTITION BY region ORDER BY month) as running_total
FROM sales;

Output

| id | region | month | sales | running_total |
|----|--------|-------|-------|---------------|
| 1  | A      | Jan   | 100   | 100           |
| 2  | A      | Feb   | 200   | 300           |
| 3  | A      | Mar   | 300   | 600           |
| 4  | B      | Jan   | 50    | 50            |
| 5  | B      | Feb   | 75    | 125           |
| 6  | B      | Mar   | 100   | 225           |

Explanation

The SUM window function returns the sum of the sales column for each row in the result set. However, the `OVER clause is used to specify that the sum should be calculated over each partition (region) and that the ordering should be done by month. This means that the sum will be calculated for each region separately, and that it will take into account the sales up to and including the current month.

Use

Window functions are used to perform calculations over a "window" or "frame" of rows in a table. This is particularly useful for calculating running totals, moving averages, and other types of rolling calculations.

Important Points

• Window functions are supported by most modern SQL databases, including PostgreSQL, MySQL, SQL Server, and Oracle.
• Window functions are used to perform calculations over a "window" or "frame" of rows in a table.
• Window functions allow you to perform rolling calculations, such as running totals or moving averages.
• The OVER clause is used to specify the partition and ordering of the window frame.
• The syntax for window functions is similar to that of aggregate functions, but with the addition of the OVER clause.

Summary

In summary, window functions are a powerful feature of SQL that allow you to perform calculations over a "window" or "frame" of rows in a table. They are particularly useful for calculating running totals, moving averages, and other types of rolling calculations. The syntax for window functions is similar to that of aggregate functions, but with the addition of the OVER clause.