oracle over partition by

The OVER PARTITION BY clause in Oracle is used to divide rows into partitions and apply aggregate functions to each partition separately. It is often used in conjunction with analytical functions to perform complex calculations on a specific set of rows.

For example, consider a table that contains sales data for different regions and products. We can use the OVER PARTITION BY clause to calculate the total sales for each region and product combination separately:

SELECT region, product, sales, SUM(sales) OVER (PARTITION BY region, product) as total_sales FROM sales_data;

This query will return the sales data along with a new column that shows the total sales for each region and product combination. The OVER PARTITION BY clause divides the rows into partitions based on the region and product columns, and the SUM function is applied to each partition separately.

In addition to aggregate functions, the OVER PARTITION BY clause can also be used with other analytical functions such as ROW_NUMBER, RANK, and DENSE_RANK to perform various calculations on partitions