How Does the `KEEP` Clause Affect `PARTITION BY` in Oracle Aggregate Queries?

PARTITION BY with and without KEEP in Oracle

In Oracle, the PARTITION BY clause enables the partitioning of data into distinct groups for the purpose of applying aggregate functions. It is often beneficial to partition data for performance optimization or logical grouping.

The PARTITION BY clause can be used with or without the KEEP keyword. When KEEP is not used, the entire partition is used for aggregation. On the other hand, when KEEP is used, only the rows that meet a specific condition are retained for aggregation.

To illustrate the difference, consider the following two queries:

Query 1 without KEEP:

SELECT deptno, MIN(sal) OVER (PARTITION BY deptno) AS "Lowest", MAX(sal) OVER (PARTITION BY deptno) AS "Highest"
FROM empl

Query 2 with KEEP:

SELECT deptno, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS "Lowest", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS "Highest"
FROM empl

Query 2 utilizes the KEEP clause to return only the first and last ranked salaries for each department (using the DENSE_RANK function). The overall effect is to eliminate duplicate values and provide distinct minimum and maximum salaries.

To elaborate further:

• OVER (PARTITION BY deptno): Partitions the data based on the deptno column.
• ORDER BY sal: Sorts the rows within each partition by salary in ascending order.
• KEEP (DENSE_RANK FIRST): Retains only the first ranked row for each partition. The DENSE_RANK function assigns unique consecutive ranks to the rows, with identical values sharing the same rank.
• MIN(sal): Calculates the minimum salary for the retained rows within each partition.
• MAX(sal): Calculates the maximum salary for the retained rows within each partition.

In the second query, the MIN and DENSE_RANK FIRST are applied to the sal column, resulting in identical behavior. Therefore, the KEEP clause is redundant in this scenario. However, if a different column were used for calculating the minimum, such as name, the KEEP clause would become relevant.

For example:

MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

This query would return the name corresponding to the lowest salary for each department, excluding any duplicate names associated with the same minimum salary.

The above is the detailed content of How Does the `KEEP` Clause Affect `PARTITION BY` in Oracle Aggregate Queries?. For more information, please follow other related articles on the PHP Chinese website!