Oracle Analytic functions

for the analysis function, you should pay attention to:1The parse function is performed after the end of the SQL query (the execution of the order by in the SQL statement is special), that is, the order by in the SQL statement also affects the result of the analysis function execution, see: SQL> SelectDeptno,2empno,3ename,4Sal,5HireDate,6Last_value (SAL) Over(Partition bydeptno) Last_value7     fromEMP8   whereDeptno=  -;D eptno EMPNO ename SAL hiredate last_value------ ----- ---------- --------- ----------- -----------     -  7499ALLEN1600.00 1981- Geneva- -          950     -  7521WARD1250.00 1981- Geneva- A          950     -  7654MARTIN1250.00 1981- the- -          950     -  7698BLAKE2850.00 1981- to- on          950     -  7844TURNER1500.00 1981- the- ,          950     -  7900JAMES950.00 1981- A-Geneva          9506rows Selectedsql> SelectDeptno,2empno,3ename,4Sal,5HireDate,6Last_value (SAL) Over(Partition bydeptno) Last_value7     fromEMP8   whereDeptno=  -  9   Order  bydeptno,mgr;deptno EMPNO ename SAL hiredate last_value------ ----- ---------- --------- ----------- -----------     -  7499ALLEN1600.00 1981- Geneva- -         2850     -  7521WARD1250.00 1981- Geneva- A         2850     -  7654MARTIN1250.00 1981- the- -         2850     -  7900JAMES950.00 1981- A-Geneva         2850     -  7844TURNER1500.00 1981- the- ,         2850     -  7698BLAKE2850.00 1981- to- on         28506rows Selectedsql> SelectDeptno,2empno,3ename,4Sal,5HireDate,6Last_value (SAL) Over(Partition bydeptno) Last_value7     fromEMP8   whereDeptno=  -  9   Order  byDeptno,mgrdesc;D eptno EMPNO ename SAL hiredate last_value------ ----- ---------- --------- ----------- -----------     -  7698BLAKE2850.00 1981- to- on         1250     -  7499ALLEN1600.00 1981- Geneva- -         1250     -  7521WARD1250.00 1981- Geneva- A         1250     -  7900JAMES950.00 1981- A-Geneva         1250     -  7844TURNER1500.00 1981- the- ,         1250     -  7654MARTIN1250.00 1981- the- -         12506rows selected from the above results we analyze: a) If the order by in the SQL statement satisfies the ordering required by the parse function analysis, then the sort in the SQL statement will be executed first, the analysis function analysis will not have to sort B) if the order in the SQL statement By does not satisfy the ordering required when parsing function analysis, the sort in the SQL statement will last after parsing function analysis is finished2. The analysis function contains three analysis clauses: grouping (Partition by), Sort (Order  byWindow window is the range of data to be processed when analyzing function analysis, taking sum, which is the record in the sum window instead of the entire group, so we need to assign the window to the first row of data in the group to the current row when we want the accumulated value of a field. If you specify that the window is from the first row in the group to the last row, then each sum value in the group will be the same, that is, the sum of the entire group. Window clauses Here I only say the rows of the window, range mode is not mentioned here, in addition to the sliding window does not mention. In a window clause we often use three properties that specify the first row, the current row, and the last row. The first line is unbounded preceding, and the current row is Currentrow, the last line is unbounded following. The window clause appears, and the ORDER BY clause must be specified, such as: Last_value (SAL) Over(Partition byDeptnoOrder  bySal rowsbetweenunbounded preceding andunbounded following) The above example specifies that the window is the entire grouping. When the window clause is omitted: a) If an order by is present, the default window is unbounded preceding and  CurrentROWB) If an order by is omitted at the same time, the default window is unbounded preceding andunbounded following if the grouping is omitted, all records are treated as a group: a) If an order by is present, the default window is unbounded preceding and  Currentrowb) If you omit order by then the window defaults to unbounded preceding andunbounded following hope the above information is helpful to everyone!

Oracle Analytic functions