I would like to access the value of the "current row" on which I write the analytic expression on. For example, given the following sample data:
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;
Let's say I would like to calculate the average (using the deptno for partitioning) only if the salary is smaller than the salary value of the "outer row"
Here is the query that calculates the average for everyone in the specific window, the row that is commented out what I would like to be able to do, "pseudocode".
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
;
So, while avg_dept_sal returns ~2916 for deptno = 10, for each row, with avg_dept_sal_2 should return:
1300 for empno = 77821875 for empno = 7839NULL for empno = 7934What would be the best approach to achieve this?
Use a RANGE window in the analytic function:
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;
Which, for the sample data, outputs:
EMPNO DEPTNO SAL AVG_DEPT_SAL AVG_DEPT_SAL_2 7934 10 1300 2916.666666666666666666666666666666666667 null 7782 10 2450 2916.666666666666666666666666666666666667 1300 7839 10 5000 2916.666666666666666666666666666666666667 1875 7369 20 800 2175 null 7876 20 1100 2175 800 7566 20 2975 2175 950 7788 20 3000 2175 1625 7902 20 3000 2175 1625 7900 30 950 1566.666666666666666666666666666666666667 null 7654 30 1250 1566.666666666666666666666666666666666667 950 7521 30 1250 1566.666666666666666666666666666666666667 950 7844 30 1500 1566.666666666666666666666666666666666667 1150 7499 30 1600 1566.666666666666666666666666666666666667 1237.5 7698 30 2850 1566.666666666666666666666666666666666667 1310
db<>fiddle here