What does the over function do in SQL

In SQL, the OVER function is a window function, which is used to perform aggregate calculation on query results and return an additional column as the result. The OVER function can be used in combination with other aggregate functions (such as SUM, AVG, COUNT, etc.).

The OVER function allows us to specify a window that defines the subset of data to which aggregate calculation is applied. This window can be based on the relative position of rows (for example, the first N rows are retrieved in the sort order), or the absolute position of rows (for example, specifying a range or partition). The OVER function performs calculations on the window and adds the results to the query results.

The following is an example showing the basic usage of the OVER function:

 SELECT column1, column2, SUM (column3) OVER ( PARTITION  BY column4 ORDER  BY column5) AS total FROM table_name;

In the above example, we used the SUM() function as the aggregate function, and then used the OVER() function to define the window. The window here is divided into multiple partitions (PARTITION BY column4) and sorted according to the sorting order of column5 (ORDER BY column5). Then, the SUM() function will be executed in each partition, calculate the sum of column3, and name the result "total" through the AS keyword. The final query result will contain the original columns (column1, column2) and the additional aggregate calculation result "total".

The OVER function can also be combined with other window functions (such as ROW_NUMBER, RANK, LEAD, LAG, etc.) to achieve more complex data analysis and report requirements.

It should be noted that the specific window function syntax and available options may vary, because different database management systems may support and implement window functions in different ways. Therefore, when using the OVER function, please refer to the official documents or manuals of the specific database for its specific usage and support.