Analytic Functions II : The Windowing Clause First Read Analytic Functions Part I, An Introduction. In this part we will have a detailed discussion on the windowing clause of an analytic function. As was said earlier that this clause defines a sliding window of data, within the group, on which the analytic function acts upon.For example, the clause 'RANGE UNBOUNDED PRECEDING' acts upon every row from the first row till the current row. We can set up data windows based on two criteria: RANGES of data values or ROWS offset from the current row Range Windows Range windows collect rows togather based on a 'Where' clause. For exmaple, the clause 'range 5 preceding' will generate a window of rows, that are within 5 units of the current row and preceding it. This units may either be numeric comparisions or date comparisions. Any other datatypes are not valid for range comparision. an example Code (Text): Select count(*) over (order by hiredate asc range 50 preceding) from emp; This will select all the rows in the partition such that the hiredate is within 50 days of the current row's hiredate.In this case,since the hiredate is sorted in ascending order,the values in the window would consist of all the rows in the current group such that the 'hiredate' was less then the current row's 'hiredate' and within 50 days of it. If the query would have been : Code (Text): Select count(*) over (order by hiredate desc range 50 preceding) from emp; then the rows selected would have 'hiredate' greater then current row's hiredate and within 50 days ot it. The following example would make this clearer. Code (Text): select ename, sal, hiredate, hiredate-50 "50_days_prior", first_value(ename) over (order by hiredate asc range 50 preceding) first_ename, first_value(hiredate) over (order by hiredate asc range 50 preceding) first_hdate from emp order by hiredate asc ENAME SAL HIREDATE 50_days_p FIRST_ENAM FIRST_HDA ---------- ---------- --------- --------- ---------- --------- SMITH 800 17-DEC-80 28-OCT-80 SMITH 17-DEC-80 ALLEN 1600 20-FEB-81 01-JAN-81 ALLEN 20-FEB-81 WARD 1250 22-FEB-81 03-JAN-81 ALLEN 20-FEB-81 JONES 2975 02-APR-81 11-FEB-81 ALLEN 20-FEB-81 BLAKE 2850 01-MAY-81 12-MAR-81 JONES 02-APR-81 CLARK 2450 09-JUN-81 20-APR-81 BLAKE 01-MAY-81 TURNER 1500 08-SEP-81 20-JUL-81 TURNER 08-SEP-81 MARTIN 1250 28-SEP-81 09-AUG-81 TURNER 08-SEP-81 KING 5000 17-NOV-81 28-SEP-81 MARTIN 28-SEP-81 FORD 3000 03-DEC-81 14-OCT-81 KING 17-NOV-81 JAMES 950 03-DEC-81 14-OCT-81 KING 17-NOV-81 MILLER 1300 23-JAN-82 04-DEC-81 MILLER 23-JAN-82 SCOTT 3000 19-APR-87 28-FEB-87 SCOTT 19-APR-87 ADAMS 1100 23-MAY-87 03-APR-87 SCOTT 19-APR-87 14 rows selected. The function first_value() returns the value of the column provided from the first row in the window. In the above query, we sorted the partition by hiredate ascending. We used the function first_value() to select the value of first 'ename' and the value of first 'hiredate' in the sorted list. Now, if we look at BLAKE, we can see that his hiredate was 01-MAY-81 and 50 days prior to it was 12-MAR-81.The function then defined the window as every row that preceded the row of Blake within the sorted list and where the hiredate was between 01-MAY-81 and 12-MAR-81. The first value for that window was that of Jones (having the earliest possible hire date which is within 50 days of Balke's hire date). Now what would happen if we sortred on descending order instead of ascending order ? Code (Text): select ename, sal, hiredate, hiredate+50 "within_next_50_days", first_value(ename) over (order by hiredate desc range 50 preceding) first_ename, first_value(hiredate) over (order by hiredate desc range 50 preceding) first_hdate from emp order by hiredate desc ENAME SAL HIREDATE within_ne FIRST_ENAM FIRST_HDA ---------- ---------- --------- --------- ---------- --------- ADAMS 1100 23-MAY-87 12-JUL-87 ADAMS 23-MAY-87 SCOTT 3000 19-APR-87 08-JUN-87 ADAMS 23-MAY-87 MILLER 1300 23-JAN-82 14-MAR-82 MILLER 23-JAN-82 FORD 3000 03-DEC-81 22-JAN-82 FORD 03-DEC-81 JAMES 950 03-DEC-81 22-JAN-82 FORD 03-DEC-81 KING 5000 17-NOV-81 06-JAN-82 FORD 03-DEC-81 MARTIN 1250 28-SEP-81 17-NOV-81 KING 17-NOV-81 TURNER 1500 08-SEP-81 28-OCT-81 MARTIN 28-SEP-81 CLARK 2450 09-JUN-81 29-JUL-81 CLARK 09-JUN-81 BLAKE 2850 01-MAY-81 20-JUN-81 CLARK 09-JUN-81 JONES 2975 02-APR-81 22-MAY-81 BLAKE 01-MAY-81 WARD 1250 22-FEB-81 13-APR-81 JONES 02-APR-81 ALLEN 1600 20-FEB-81 11-APR-81 JONES 02-APR-81 SMITH 800 17-DEC-80 05-FEB-81 SMITH 17-DEC-80 14 rows selected. If we look at BLAKE again, this time the window is different since thw data partition is sorted differently. BLAKE's window of 'range 50 preceding' is CLARKE since CLARKE's hiredate is the last hiredate preceding BLAKE's record and within 50 days of it.