Example of Oracle over () function usage

Oracle over () functions are commonly used functions. The following describes two examples of using Oracle over () functions () functions have a deeper understanding.

1. Let's take a simple look. Note that the over (...) conditions are different,
Use sum (sal) over (order by ename)... to query the "continuous" sum of employees' salaries,
Note that if the over (order by ename) clause does not have an order by clause, the sum is not "continuous,
Put them together and try the following differences:

 
 

  
  
SQL> select deptno, ename, sal,
  
  
Sum (sal) over (order by ename) continuous summation,
  
  
Sum (sal) over () sum, -- sum (sal) over () is equivalent to sum (sal)
  
  
100 * round (sal/sum (sal) over (), 4) "share (% )"
  
  
From emp
  
  
/
 
 

Deptno ename sal sum of continuous sum (%)

 
 

  
  
------------------------------------------------------------
  
  
20 ADAMS 1100 1100 29025
  
  
30 ALLEN 1600 2700 29025 5.51
  
  
30 BLAKE 2850 5550 29025 9.82
  
  
10 Clarke 2450 8000 29025 8.44
  
  
20 FORD 3000 11000 29025 10.34
  
  
30 JAMES 950 11950 29025 3.27
  
  
20 JONES 2975 14925 29025 10.25
  
  
10 KING 5000 19925 29025 17.23
  
  
30 MARTIN 1250 21175 29025 4.31
  
  
10 MILLER 1300 22475 29025 4.48
  
  
20 SCOTT 3000 25475 29025 10.34
  
  
20 SMITH 800 26275 29025 2.76
  
  
30 TURNER 1500 27775 29025 5.17
  
  
30 WARD 1250 29025 29025 4.31
  
  
 
  
  
14 rows have been selected.
  
  
 
 
 

2. The Oracle over () function uses subpartitions to find the continuous sum of salaries of each department. Note: partition by department. Note that the over (...) conditions are different,

 
 

  
  
Sum (sal) over (partition by deptno order by ename) calculates the sum
  
  
Sum (sal) over (partition by deptno) sum by department
  
  
Sum (sal) over (order by deptno, ename) does not calculate the sum based on the Department's "continuous"
  
  
Sum (sal) over () does not calculate the total number of all employees by department. The effect is equivalent to sum (sal ).
  
  
 
  
  
SQL> select deptno, ename, sal,
  
  
Sum (sal) over (partition by deptno order by ename) Department continuous sum, -- department salary "continuous" sum
  
  
Sum (sal) over (partition by deptno) Department sum, -- the sum of Department statistics, the sum of the same department unchanged
  
  
100 * round (sal/sum (sal) over (partition by deptno), 4) "department share (% )",
  
  
Sum (sal) over (order by deptno, ename) continuous sum, -- the salary of all departments "continuous" sum
  
  
Sum (sal) over () sum, -- sum (sal) over () is equivalent to sum (sal), total salary of all employees.
  
  
100 * round (sal/sum (sal) over (), 4) "total share (% )"
  
  
From emp
  
  
/
 
 

Deptno ename sal Department continuous sum Department total department share (%) total sum of continuous sum (%)

 
 

  
  
----------------------------------------------------------------------------
  
  
10 CLARK 2450 2450 8750 28 2450 29025 8.44
  
  
KING 5000 7450 8750 57.14 7450 29025
  
  
MILLER 1300 8750 8750 14.86 8750 29025 4.48
  
  
 
  
  
20 ADAMS 1100 1100 10875 10.11 9850 29025
  
  
FORD 3000 4100 10875 27.59 12850 29025
  
  
JONES 2975 7075 10875 27.36 15825 29025 10.25
  
  
SCOTT 3000 10075 10875 27.59 18825 29025
  
  
SMITH 800 10875 10875 7.36 19625 29025 2.76
  
  
 
  
  
30 ALLEN 1600 1600 9400 17.02 21225 29025 5.51
  
  
BLAKE 2850 4450 9400 30.32 24075 29025 9.82
  
  
JAMES 950 5400 9400 10.11 25025 29025 3.27
  
  
MARTIN 1250 6650 9400 13.3 26275 29025
  
  
TURNER 1500 8150 9400 15.96 27775 29025 5.17
  
  
WARD 1250 9400 9400 13.3 29025 29025
  
  
 
  
  
14 rows have been selected.
 
 

Use of Oracle to_char Functions

Implementation of oracle function return table

Learn about the Oracle FBI Index

How to uninstall an Oracle database in Windows

Multiple table Connection Methods in Oracle