Compare_Over_PartitionBy1.htm
Compare :

The query_partition_clause segrregates the result set into partitions, or groups, of data.
  • SELECT empno, deptno, sal,
    AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal
    FROM emp
  •  SELECT ename,empno, deptno, sal, round(AVG(sal) OVER (), 2) AS avg_sal FROM emp
 
OVER():

PostgreSQL

Oracle :

 
OVER(Partition BY)

SELECT ename,empno,deptno, sal,round(AVG(sal)over(partition by deptno), 2) AGVSAL FROM emp

PostgreSQL

Oracle: