OVER

MySQL

<window function> OVER ([PARTITION BY <expression>[,…]] [ORDER BY <expression> [ASC|DESC][,…] [ROWS|RANGE <frame specification>]])

• <window function>: A built-in function in SQL used for performing calculations across a set of table rows related to the current row. These include cumulative, moving, and aggregate functions such as SUM, AVG, MAX, MIN, COUNT, and row_number.
• over (): Defines window partitions to perform the calculation over. It is a required clause when calling a window function.
• partition by <expression>[,…]: Defines a column or columns to group data into separate partitions on which the window function will be applied. Each partition forms a subset of the data, operating independently from other partitions.
• order by <expression> [asc|desc][,…]: Specifies an ordering of rows within a partition. The window function is computed across the ordered rows within each partition. ASC (ascending) or DESC (descending) can be used to define the order; if not specified, ASC is default. Multiple Expressions can be used, separated by commas.
• rows|range <frame specification>: Specifies the rows or range of values to include in the frame. The frame is a set of rows related to the current row from which the values are derived for processing. Range uses logical offsets (based on values), while Rows uses physical offsets (based on row position).

Example

SELECT customer_name,
       total_purchase,
       RANK() OVER (ORDER BY total_purchase DESC) as purchase_rank
FROM customers;

Output

| customer_name | total_purchase | purchase_rank |
| :------------ | :------------- | :------------ |
|   John Doe    |       500      |       1       |
|   Jane Doe    |       300      |       2       |
|   Mary Smith  |       200      |       3       |

Explanation

The OVER() clause is used here along with RANK() function to rank customers based on their total purchases. Customers with highest total purchases get the top ranks. In our result set, ‘John Doe’, who made the highest total purchase is ranked as 1 and so on.

PostgreSQL

function OVER ([ PARTITION BY partition_expression ] [ ORDER BY sort_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] … ])

• partition by partition_expression: Defines how data should be divided into partitions. Each partition is treated separately for the window function. partition_expression can be any expression that is evaluated for each row in the partition.
• order by sort_expression: Specifies how to sort rows in each partition. Rows are ordered by the values of the sort_expression. sort_expression can be any expression that returns a value that can be sorted.
• asc | desc: Determines the order direction for sort_expression. ASC sorts from lowest to highest. DESC sorts from highest to lowest.
• nulls first | last: Specifies where to place NULL values in the sort order. NULLS FIRST places NULL values at the start of the sort order. NULLS LAST places NULL values at the end of the sort order.

Example

SELECT
    emp_name,
    emp_department,
    emp_salary,
    AVG(emp_salary) OVER (PARTITION BY emp_department)
FROM
    employees;

Output

| emp_name | emp_department | emp_salary | avg       |
|----------|----------------|------------|-----------|
| John     | HR             | 3000       | 3500.00   |
| Katie    | HR             | 4000       | 3500.00   |
| Tom      | Development   | 7000       | 6500.00   |
| David    | Development   | 6000       | 6500.00   |
| Sam      | Sales         | 5000       | 5000.00   |

Explanation

In this example, the OVER keyword in the SELECT statement is used with the PostgreSQL AVG function. This statement calculates the average salary (AVG(emp_salary)) for employees within the same department (PARTITION BY emp_department), with the result shown in the avg column.

SQL Server

OVER([PARTITION BY value_expression , … [ n ]] [ORDER BY clause])

• partition by value_expression: This parameter divides the result set produced by the FROM clause into partitions to which the function is applied. The value_expression parameter is any column or any valid SQL Server expression. If no PARTITION BY clause is specified, the function is applied to all rows in the result set.
• order by clause: This parameter defines the logical order in which the operation is performed. ORDER BY is applied after PARTITION BY and determines the order in which the rows are visited by the OVER clause. The function is applied to the rows in the order specified by this clause. If this clause is not specified, the order of the rows is undefined.

Example

SELECT
    SalesOrderID,
    TotalDue,
    AVG(TotalDue) OVER(PARTITION BY SalesOrderID) as Average
FROM
    Sales.SalesOrderHeader
WHERE
    SalesOrderID IN (43659, 43660);

Output

| SalesOrderID | TotalDue  |    Average     |
|--------------|-----------|----------------|
|    43659     |   23153.2339 |   23153.2339 |
|    43660     |     1457.3288|      1457.3288   |

Explanation

This code demonstrates the use of the OVER clause in SQL Server. The OVER clause creates a window of rows for each SalesOrderID and calculates the average of the TotalDue within that window. Therefore, the Average column in the output provides the average TotalDue for each SalesOrderID.

Oracle

OVER([PARTITION BY column_name [, column_name …]] [ORDER BY column_name [, column_name …] [ASC|DESC] [NULLS {FIRST|LAST}]])

• partition by column_name: Divides the result set into partitions. The window function is applied to each partition separately. The column_name parameters specify the columns by which the result set will be partitioned.
• order by column_name: Determines the order in which the rows in a partition are processed. The column_name parameters define the columns by which the result set will be sorted.
• asc|desc: Specifies ascending (ASC) or descending (DESC) order for the result set. If ASC is specified, the rows are arranged from the smallest to the largest. If DESC is specified, the rows are sorted from the largest to smallest.
• nulls {first|last}: Determines the placement of NULL values in the sorted result set. If FIRST is specified, NULL values are presented before all other values in the result set. If LAST is specified, NULL values are presented after all other values.

Example

SELECT emp_no, salary,
RANK() OVER (ORDER BY salary DESC) salary_rank
FROM salaries
WHERE emp_no BETWEEN 10001 AND 10020;

Output

EMP_NO  | SALARY   | SALARY_RANK
--------|----------|-------------
10017   | 80026    | 1
10011   | 66956    | 2
10009   | 60929    | 3
10006   | 60098    | 4
10013   | 57094    | 5
10015   | 55425    | 6
10019   | 53550    | 7
10010   | 48973    | 8
10016   | 48472    | 9
10002   | 46868    | 10

Explanation

This example demonstrates the use of the RANK() function with the OVER clause in Oracle SQL. This query ranks employees by their salary in descending order. This result indicates that the employee ‘10017’ has the highest salary by ranking first, followed by the second highest salary employee ‘10011’ ranked second, and so on.