访问分析表达式中的"outer row"值

Question

我想访问我在其上写入分析表达式的"当前行"的值。例如，给定以下示例数据：

DROP TABLE emp PURGE;
CREATE TABLE emp (
empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4),
hiredate DATE,
sal      NUMBER(7,2),
comm     NUMBER(7,2),
deptno   NUMBER(2)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

假设我想仅在工资小于"外行"的工资值时才计算平均值(使用deptno进行划分)

这是计算特定窗口中每个人的平均值的查询，该行被注释掉了我希望能够执行的操作，"伪代码"。

SELECT t.empno, t.deptno, t.sal
,AVG(t.sal) OVER (PARTITION BY t.deptno) AS avg_dept_sal
--,AVG(CASE WHEN t.sal < OUTER_VALUE(t.sal) THEN t.sal ELSE NULL END) OVER (PARTITION BY t.deptno) AS avg_dept_sal_2
FROM emp t
;

因此，虽然avg_dept_sal返回 ~2916 对于deptno = 10，但对于每一行，avg_dept_sal_2应返回：

• 1300empno = 7782
• 1875empno = 7839
• NULLempno = 7934

实现这一目标的最佳方法是什么？

Answer 1

在分析函数中使用RANGE窗口：

SELECT empno,
deptno,
sal,
AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal,
AVG(sal) OVER (
PARTITION BY deptno
ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND 0.01 PRECEDING
) AS avg_dept_sal_2
FROM   emp;

对于示例数据，输出：

AVG_DEPT_SAL102916.666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666662916.666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666662020 11002020201566.666666666666666666666666666666666666666666716001566.66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666

EMPNO	DEPTNO	SAL		AVG_DEPT_SAL_2
	7934	1300
7782	10	2450	2916.66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
7839	10	5000
7369	800	2175	空
7876	2175	800
7566	2975	2175		950
7788	3000	2175	1625
7902	3000	2175	1625
7900	30 950		1566.666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
7654	30 1250		1566.66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
7521	30 1250		950
7844	30 1500		1566.66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
7499	30		1566.66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
7698	30	2850