Oracle window clause analysis

Oracle window clause analysis is unclear about the Oracle window. Recently, I read the description of this Part in the "Advanced SQL Programming" written by Laurent Schneider, and I feel that it is clearly written, for viewing. The partition clause in the www.2cto.com window is not the only method that limits the scope of the analysis function operation. When the rows between clause is used, the ROWS are sorted, and a window is defined. SQL code SELECT ENAME, HIREDATE, SAL, MAX (SAL) OVER (ORDER BY HIREDATE, ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) MAX_BEFORE, MAX (SAL) OVER (ORDER BY HIREDATE, ename rows between 1 following and unbounded following) MAX_AFTER from emp order by hiredate, ENAME; www.2cto.com reference ename hiredate sal MAX_BEFORE MAX_AFTER ---------- ------------ 17 SMITH -DEC-80 800 5000 ALLEN 20-FEB-81 1600 800 5000 WARD 22-FEB-81 1250 1600 5000 JONES 02-APR-81 2975 1600 5000 BLAKE 01-MAY-81 2850 2975 5000 CLARK 09-JUN-81 2450 2975 5000 TURNER 08-SEP-81 1500 2975 5000 MARTIN 28-SEP-81 1250 2975 5000 KING 17-NOV-81 5000 2975 3000 FORD 03-DEC-81 3000 5000 3000 JAMES 03-DEC-81 950 5000 3000 MILLER 23-JAN-82 1300 5000 3000 SCOTT 19-APR-87 3000 5000 1100 ADAMS 23-MAY- 87 1100 5000 for each row, the highest salary before the current row and the highest salary after the current row will be returned. The order by clause is not used for ranking, but for declaring a window. Aggregate using the order by clause can generate progressive aggregate values. SQL code SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY ENAME ROWS UNBOUNDED PRECEDING) CUMSUM FROM EMP; www.2cto.com reference ename sal cumsum ---------- ADAMS 1100 1100 ALLEN 1600 2700 BLAKE 2850 5550 CLARK 2450 8000 FORD 3000 11000 JAMES 950 11950 JONES 2975 KING 14925 5000 MARTIN 19925 1250 MILLER 21175 SCOTT 1300 25475 SMITH 800 26275 TURNER 1500 27775 WARD 1250 29025 www. 2cto.com the lowest boundary is unbounded preceding (the first ROW), the current row, and the highest ROW is unbounded following (the last ROW ). The rows before and after the current row are obtained using n PRECEDING and n FOLLOWING. n refers to the relative position of the current row. If BETWEEN is not declared, the window ends with the current row by default. SQL code SELECT ENAME, SAL, AVG (SAL) OVER (ORDER BY SAL ROWS 1 PRECEDING) AVG FROM EMP; reference ename sal avg ---------- SMITH 800 800 950 JAMES 875 ADAMS 1100 1025 WARD 1250 1175 MARTIN 1250 1250 MILLER 1300 1275 TURNER 1500 ALLEN 1400 CLARK 1600 1550 blke 2450 2025 JONES 2850 2650 SCOTT 3000 2987.5 FORD 3000 3000 KING 5000 calculates the average value for the current row and the previous row. The window begins with the previous row before the current row and ends with the current row. The RANGE of www.2cto.com is similar to ROWS, but the interval is not the number of ROWS, but the value or time. SQL code SELECT ENAME, SAL, SAL *. 9 LOW, SAL * 1.1 HIGH, COUNT (*) OVER (order by sal range between sal *. 1 preceding and sal *. 1 FOLLOWING) count from emp; reference ename sal low high count ---------- SMITH 800 720 880 1 JAMES 950 855 1 ADAMS 1045 1100 990 1 WARD 1210 1250 3 MARTIN 1125 1375 1250 3 MILLER 1125 1375 1300 1170 3 TURNER 1500 1350 1650 2 ALLEN 1600 1440 1760 2 CLARK 2450 2205 2695 1 BLAKE 2850 2565 3135 4 JONES 2975 2677.5 3272.5 4 SCOTT 3000 2700 3300 4 FORD 3000 2700 3300 4 KING 5000 4500 5500 1 sorting keywords are salary. In comparison with the employee, the salary is within the floating range of 10%. When RANGE is used, the result is clear. When two rows get the same value, they are either included in the window or not included in the window. SQL code SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUMROWS, SUM (SAL) OVER (ORDER BY SAL RANGE UNBOUNDED PRECEDING) SUMRANGE FROM EMP; reference www.2cto.com ename sal sumrows sumrange ---------- SMITH 800 800 800 JAMES 950 1750 1750 ADAMS 1100 2850 WARD 2850 1250 MARTIN 4100 5350 1250 MILLER 5350 5350 TURNER 1300 6650 6650 ALLEN 1600 9750 9750 CLARK 2450 12200 12200 BLAKE 2850 15050 JONES 15050 2975 SCOTT 18025 18025 3000 FORD 21025 24025 3000 KING 24025 24025 5000 www.2cto.com Scott and Ford both earned 29025 salaries. The RANGE analysis function is clear. Both ROWS return the same value, but use the ROWS analysis function. Each row returns different values. Rows current row points to a unique ROW, and range current row points to the sorting keyword that is equal to all ROWS of the current row. When order by without window clauses is used, the default window of the analysis function supporting window clauses is range between unbounded preceding and current row. For date and timestamp, the interval can be several days, days to seconds (day-to-seconds), or years to months (year-to-month. SQL code SELECT ENAME, HIREDATE, SAL, AVG (SAL) OVER (ORDER BY TRUNC (HIREDATE, 'mm ') range between interval '1' month preceding and interval '1' month preceding) "PREVIOUS", AVG (SAL) OVER (order by trunc (HIREDATE, 'mm') range current row) "CURRENT", AVG (SAL) OVER (order by trunc (HIREDATE, 'mm') range between interval '1' month following and interval '1' month following) "NEXT ", AVG (SAL) OVER (ORDE R by trunc (HIREDATE, 'mm') range between interval '1' month preceding and interval '1' month following) "3 MONTHS" from emp order by hiredate; reference jsonename hiredate sal previous current next 3 MONTHS ---------- --------- -------- SMITH 17-DEC-80 800 800 800 ALLEN 20-FEB-81 1600 1425 WARD 22-FEB-81 1425 1250 JONES 02-APR-81 1425 1425 BL AKE 01-MAY-81 2850 2975 2850 2450 2758 CLARK 09-JUN-81 2450 2850 2450 2650 TURNER 08-SEP-81 1500 1375 1375 1250 MARTIN 28-SEP-81 1375 1375 5000 KING 17-NOV-81 5000 1975 2983 950 5000 JAMES 03-DEC-81 1975 1300 2563 FORD 03-DEC-81 3000 5000 1975 1300 2563 MILLER 23-JAN-82 1300 1975 1300 1750 SCOTT 19-APR-87 3000 3000 1100 2050 ADAMS 23-MAY-87 1100 3000 1100 2050 sort keys that are in the hiring date month. The above section calculates the average salary of new employees in the previous month before their employment, the average salary of new employees in the current month, and the average salary of new employees in the next month, and the average salary of new employees within three months before and after their employment.