Introduction to the OVER() Clause in SQL

By /
Add A Heading (10)

The OVER() clause is used to perform a calculation on the current row from the table with other rows of the table. The OVER() clause works along with the window function. The simple function or clauses available in the SQL language can not perform a calculation on an individual element from a specific row. OVER() clause will help you to perform such tricky operations on your database. Let’s deep dive into the concept of the OVER() clause to understand the examples and workings.

What is the OVER() Clause?

The OVER() clause is always used with the windows function where the subset of rows from the result sets are used for calculation. The rows are grouped by the OVER() clause and then provided to the windows function.

The OVER() clause is mainly divided into two parts:

  1. PARTITION BY: The PARTITION BY will help you to divide the result into parts, groups, or sets of rows.
  2. ORDER BY: The ORDER BY will help you to decide the order of rows in the partition so, the window function can work properly on it.

OVER() is mostly employed together with analytic or window functions. You can conduct complicated analytics using these data for analysis and reports. PARTITION BY clause will help to partition or group your data meaningfully using OVER(). This allows you to carry out calculations and grouping in individual subgroups of your data.

Some examples include computing metrics for a specific category and also for products within that specific category as well as metrics for specific periods of time. The rows are processed within each partition in the order specified by the ORDER BY clause of the OVER() clause. Sequence calculations that include run totals, cumulative sums, and time-based analyses require this information.

Let’s see a basic example of the OVER() clause for better understanding.

Examples of OVER() Clause in SQL

In this basic example, we are considering a table containing the column of product and revenue. Let’s try to calculate the revenue of each product available in the table. The total revenue will be calculated after summing up all the products because we are not executing PARTITION BY and ORDER BY. Therefore, the total revenue column from the outcome should be the sum of all the products. Let’s see the query and results for a better understanding.

Example 1: Basic Example of OVER() Clause

SELECT
    Product,
    Revenue,
    SUM(Revenue) OVER() AS TotalRevenue
FROM SalesTable;Code language: SQL (Structured Query Language) (sql)
Basic Example Of OVER Clause
Basic Example Of OVER Clause

The revenue contribution of each product is calculated, and the total revenue is also mentioned in the third column across the products. Now, let’s try out more complex examples based on the OVER() clause.

Example 2: OVER() Clause With PARTITION BY and ORDER BY

In this second example, we are using the OVER() Clause along with the PARTITION BY and ORDER BY. Now you can tell the real difference between the just OVER() clause and after the use of PARTITION BY and ORDER BY. We are using the same data here as above for better understanding. In the result section, we will get the different total revenue for each product. The total revenue is 650, but the Cumulative Revenue is different! Let’s see a query and results.

SELECT
    Product,
    Date,
    Revenue,
    SUM(Revenue) OVER(PARTITION BY Product ORDER BY Date) AS CumulativeRevenue
FROM SalesTable
ORDER BY Product, Date;Code language: SQL (Structured Query Language) (sql)
OVER Clause Example2
OVER Clause Example 2

In the results, you can notice the difference.

Application of OVER() Clause

Below are some of the most commonly used applications of the OVER() clause.

1. Perform Complex Calculations

With the OVER() clause, you will have a chance to make complex calculations about your data including cumulative sums, moving averages and running totals without using complex subqueries and temporary tables.

2. Grouping Data

The PARTITION BY clause divides your data into relevant groups or partitions. It proves very effective while carrying out the calculations within some sets of your dataset including but not limited to products, customers, and categories.

3. Used With Window Functions

This makes it possible to aggregate data in partitioned tables using window functions along with the OVER() operator. It is possible to add functions such as SUM(), AVG(), and COUNT() to certain sets of rows, permitting more in-depth research.

The OVER() clause forms part of the SQL standard and, therefore, several RDBMS support it, enabling you to come up with portable queries.

Summary

In this article, we have discussed the OVER() clause. The OVER() clause is mainly used with the window functions to perform complex operations. The basic example of the OVER() clause and one more complex example with PARTITION BY and ORDER BY is given. The OVER()clause offers calculations such as cumulative sums, moving averages, ranks, and percentiles very easily. It reduces the amount of necessary SQL code for such operations. A quick introduction to the application of the OVER() clause is also given. Hope you will enjoy this article. 

Reference

https://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful