1

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 = 7782
  • 1875 for empno = 7839
  • NULL for empno = 7934

What would be the best approach to achieve this?

Arklur
  • 173
  • 1
  • 11

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117