SQL OVER Function Usage Guide

In SQL, the OVER function is used to calculate the results of window functions. Window functions are a type of function used to perform calculations on a specified window or group in query results. The OVER function can be used in conjunction with window functions to calculate and return results for specified groups or windows in query results.

The OVER function is used in the SELECT clause of a SELECT statement, with the following syntax:

窗口函数() OVER (PARTITION BY 列名1, 列名2,... ORDER BY 列名3, 列名4,...)

Window functions are a type of aggregate function, such as SUM, AVG, COUNT, and so on. The PARTITION BY clause is used to specify the columns for grouping, while the ORDER BY clause is used to specify the sorting columns.

The purpose of the OVER function is to divide query results into multiple groups or windows, and apply window functions to each group or window for calculation. It can be used to calculate cumulative values, rankings, row numbers, etc. within each group.

For example, consider the following Sales table:

1. Order ID, Product, Quantity, and Price for each item are as follows:
– Order 1: Product A, Quantity 10, Price $5
– Order 2: Product A, Quantity 5, Price $10
– Order 3: Product B, Quantity 8, Price $15
– Order 4: Product B, Quantity 12, Price $20

To calculate the cumulative sales volume and cumulative sales revenue for each product, window functions can be used.

SELECT OrderID, Product, Quantity, Price,
    SUM(Quantity) OVER (PARTITION BY Product ORDER BY OrderID) AS TotalQuantity,
    SUM(Quantity * Price) OVER (PARTITION BY Product ORDER BY OrderID) AS TotalPrice
FROM Sales;

Here, we are using the SUM function as a window function to group the data by Product using the PARTITION BY clause and then sorting by OrderID. The results are as follows:

OrderID
Product
Quantity
Price
Total Quantity
Total Price

1
A
10
5
10
50

2
A
5
10
15
100

3
B
8
15
8
120

4
B
12
20
20
360

It can be seen that the TotalQuantity column calculates the cumulative sales volume of each product, while the TotalPrice column calculates the cumulative sales amount.