只对百分比的行应用MIN() OVER

Question

我有以下查询:

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

结果如下:查询结果

我希望列min_sal_by_dept_and_job排除sal列的X %。假设是50%因此，在本例中，包含sal50和1250的前两行将被排除，min_sal_by_dept_and_job将不再是1250，而是1500，因为它是1500和1600之间的最小值。简单地说，我希望min()只应用于特定百分比的行。

Answer 1

根据你上面的解释，你可以试试这个:

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;