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

PARTITION BY with and without KEEP in Oracle

The PARTITION BY clause in Oracle allows you to group rows in a table and perform aggregate functions on those groups. The KEEP clause, when used with the PARTITION BY clause, allows you to specify which rows to keep when calculating the aggregate function.

Consider the following two queries:

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

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

Both queries seem to have the same result: they calculate the lowest and highest salaries for each department. However, there is a subtle difference between the two queries.

In the first query, the MIN and MAX functions are applied to all rows in each partition. In the second query, the KEEP clause is used to specify that only the first and last rows in each partition should be included in the calculation.

The KEEP clause can be used with any aggregate function. It is particularly useful when you want to find the first or last occurrence of a value in a set of rows.

For example, the following query finds the first and last names of employees with the highest salaries in each department:

SELECT empno,
   deptno,
   sal,
   MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS "First Highest Salary",
   MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS "Last Highest Salary"
FROM empl
GROUP BY deptno

The KEEP clause can also be used to optimize performance. By specifying that only the first or last rows in each partition should be included in the calculation, you can reduce the amount of data that the database needs to process. This can improve the performance of your query, especially if you are working with a large dataset.

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