[Oracle] analysis functions (4)-OrderBy statements

If order by exists in the analysis function, a default window opening clause will be added! It indicates that the first row of the partition is the current row;
If order by is not found in the analysis function, the default window is all in the partition;
After the Order by clause, you can add nulls last. For example, order by comm desc nulls last indicates to ignore the empty row of the comm column during sorting.

If order BY clause is used, between unbounded preceding AND current row are grouped into the first row to the current row.
Without between AND, without order BY, it is the first row of the Group to the last row of the group; BETWEEN unbounded preceding and unbounded following

In addition, remember that in the RANGE window, order by can only have one column; in the ROWS window, order by can have multiple columns.

The following is an example:

SELECT emp_id, ename, dept_id, hire_date, sal, SUM (sal) OVER (partition by dept_id order by hire_date) sum_sal1, SUM (sal) OVER (partition by dept_id order by hire_date DESC) sum_sal2, SUM (sal) OVER (partition by dept_id order by hire_date DESC nulls LAST) sum_sal3, SUM (sal) OVER (partition by dept_id) sum_sal4, SUM (sal) OVER () sum_sal5 FROM emp; EMP_ID ENAME DEPT_ID HIRE_DATE SAL 1_1_1_sum_sal4 SUM_SAL5 ------ ----- ------- ---------- 100 Stev 10 01-1 month-90 7000 7000 7000 7000 7000 36000 Tom 20 101-89 2000 2000 10000 10000 10000 36000 102 Mike 20 13-1 month-93 8000 10000 8000 8000 10000 36000 John 50 18-7 month-96 120 1000 1000 19000 16000 19000 Joy 50 10-4 month-97 36000 5000 18000 15000 19000 36000 123 Kate 50 10-10 month-97 5000 10000 14000 11000 19000 36000 Jess 50 16-11 month-99 124 6000 16000 9000 6000 19000 Rich 50 36000 122 3000 19000 3000 19000 19000 36000