Apply MIN() OVER only on percentage of the rows

Question

I have the following query:

SELECT *
FROM empno,
     ename,
     deptno,
     sal,
     job,
     MIN(sal) OVER (PARTITION BY deptno, job) AS min_sal_by_dept_and_job
FROM emp;

With this result: Query result

I want the column min_sal_by_dept_and_job to exclude X percent of the sal column. Let's say 50% so in this case the first two rows with sal 1250 and 1250 will be excluded and min_sal_by_dept_and_job will no longer be 1250 but 1500 because it is the min from 1500 and 1600. Simply said, I want the min() to be applied only on certain percentage of the rows.

as far as I understand from your explanations in `min_sal_by_dept_and_job` column should be inserted second min value. Is that true? — HRK, Jun 23 '22 at 10:10
Currently `min_sal_by_dept_and_job` shows the minimum of 1250, 1250, 1500, 1600 which is 1250 for each row. I need to exclude the first 50 percent of the rows in the calculation. In this case since we have 4 rows, I need to exclude the first two, so that the `MIN()` will be applied on the second two rows - the minimum of 1500 and 1600. As a result `min_sal_by_dept_and_job` should be 1500 for each row. — user3687808, Jun 23 '22 at 10:44
@AleixCC it is fixed. It is percentage of rows. I gave it as 50% because it is easier. The actual business requirement is to exclude 5% of the lowest salaries in the calculation. — user3687808, Jun 23 '22 at 10:48
Ok, so it could be based on percentiles then, right? All salaries lower (and including) the 5th percentile should not be counted in the calculation? — Aleix CC, Jun 23 '22 at 11:24

score 1 · Answer 1 · answered Jun 23 '22 at 11:24

1

According to your explanations above you can try this one:

SELECT e.empno, ename, deptno, sal, job, 
       MIN(SAL) OVER (PARTITION BY deptno, job) AS min_sal_by_dept_and_job 
 FROM (SELECT empno, ename, deptno, sal, job,
              count(*) over () as cnt_rows,
              row_number() OVER (PARTITION BY deptno, job ORDER BY sal DESC) AS ROW_num
         FROM emp) e 
WHERE ROW_NUM <=0.5*cnt_rows
GROUP BY empno, ename, deptno, sal, job;

answered Jun 23 '22 at 11:24

HRK

336
1
2
8

Unfortunately it returns exactly the same. From the example above: `min_sal_by_dept_and_job` is the same(1250) for `DEPTNO = 30' and `JOB = SALESMAN` where it should be 1500 – user3687808 Jun 23 '22 at 11:52
It looks like there are more than 4 records for `DEPTNO = 30` and `JOB = SALESMAN` and the count of populated value `SAL = 1250` is more than 2 rows, i.e. is it possible in the second 50 percent ( the included rows in your report) there is a value 1250 -> if - yes => the records will be included in your report. – HRK Jun 23 '22 at 12:27
Unfortunately, there are not. There are 14 rows in total and only 4 for `DEPTNO = 30` and `JOB = SALESMAN` The demo setup can be taken from here: [Demo setup](https://oracle-base.com/articles/misc/min-and-max-analytic-functions) – user3687808 Jun 24 '22 at 11:08

Apply MIN() OVER only on percentage of the rows

1 Answers1