Recipe11.8.Shifting Row Values
Recipe 11.8. Shifting Row ValuesProblemYou want to return each employee's name and salary along with the next highest and lowest salaries. If there are no higher or lower salaries, you want the results to wrap (first SAL shows last SAL and vice versa). You want to return the following result set: ENAME SAL FORWARD REWIND ---------- ---------- ---------- ---------- SMITH 800 950 5000 JAMES 950 1100 800 ADAMS 1100 1250 950 WARD 1250 1250 1100 MARTIN 1250 1300 1250 MILLER 1300 1500 1250 TURNER 1500 1600 1300 ALLEN 1600 2450 1500 CLARK 2450 2850 1600 BLAKE 2850 2975 2450 JONES 2975 3000 2850 SCOTT 3000 3000 2975 FORD 3000 5000 3000 KING 5000 800 3000 SolutionFor Oracle users, the window functions LEAD OVER and LAG OVER make this problem easy to solve and the resulting queries very readable. With other RDBMSs you can use scalar subqueries, though ties will present a problem. Because of the problem with ties, the RDBMSs without support for window functions enable only an approximate solution to this problem. DB2, SQL Server, MySQL, and PostgreSQLUse a scalar subquery to find next and prior salaries relative to each salary: 1 select e.ename, e.sal, 2 coalesce( 3 (select min(sal) from emp d where d.sal > e.sal), 4 (select min(sal) from emp) 5 ) as forward, 6 coalesce( 7 (select max(sal) from emp d where d.sal < e.sal), 8 (select max(sal) from emp) 9 ) as rewind 10 from emp e 11 order by 2 OracleUse the window functions LAG OVER and LEAD OVER to access prior and next rows relative to the current row: 1 select ename,sal, 2 nvl(lead(sal)over(order by sal),min(sal)over()) forward, 3 nvl(lag(sal)over(order by sal),max(sal)over()) rewind 4 from emp DiscussionDB2, SQL Server, MySQL, and PostgreSQLThe scalar subquery solution is not a true solution to the problem. It's an approximation that will fail in the event any two records contain the same value for SAL. It's the best you can do without having window functions available. OracleThe window functions LAG OVER and LEAD OVER will (by default and unless otherwise specified) return values from the row before and after the current row, respectively. You define what "before" or "after" means in the ORDER BY portion of the OVER clause. If you examine the solution, the first step is to return the next and prior rows relative to the current row, ordered by SAL: select ename,sal, lead(sal)over(order by sal) forward, lag(sal)over(order by sal) rewind from emp ENAME SAL FORWARD REWIND ---------- ---------- ---------- ---------- SMITH 800 950 JAMES 950 1100 800 ADAMS 1100 1250 950 WARD 1250 1250 1100 MARTIN 1250 1300 1250 MILLER 1300 1500 1250 TURNER 1500 1600 1300 ALLEN 1600 2450 1500 CLARK 2450 2850 1600 BLAKE 2850 2975 2450 JONES 2975 3000 2850 SCOTT 3000 3000 2975 FORD 3000 5000 3000 KING 5000 3000 Notice that REWIND is NULL for employee SMITH and FORWARD is NULL for employee KING; that is because those two employees have the lowest and highest salaries, respectively. The requirement in the problem section should NULL values exist in FORWARD or REWIND is to "wrap" the results meaning that, for the highest SAL, FORWARD should be the value of the lowest SAL in the table, and for the lowest SAL, REWIND should be the value of the highest SAL in the table. The window functions MIN OVER and MAX OVER with no partition or window specified (i.e., an empty parenthesis after the OVER clause) will return the lowest and highest salaries in the table, respectively. The results are shown below: select ename,sal, nvl(lead(sal)over(order by sal),min(sal)over( )) forward, nvl(lag(sal)over(order by sal),max(sal)over( )) rewind from emp ENAME SAL FORWARD REWIND ---------- ---------- ---------- ---------- SMITH 800 950 5000 JAMES 950 1100 800 ADAMS 1100 1250 950 WARD 1250 1250 1100 MARTIN 1250 1300 1250 MILLER 1300 1500 1250 TURNER 1500 1600 1300 ALLEN 1600 2450 1500 CLARK 2450 2850 1600 BLAKE 2850 2975 2450 JONES 2975 3000 2850 SCOTT 3000 3000 2975 FORD 3000 5000 3000 KING 5000 800 3000 Another useful feature of LAG OVER and LEAD OVER is the ability to define how far forward or back you would like to go. In the example for this recipe, you go only one row forward or back. If want to move three rows forward and five rows back, doing so is simple. Just specify the values 3 and 5 as shown below: select ename,sal, lead(sal,3)over(order by sal) forward, lag(sal,5)over(order by sal) rewind from emp ENAME SAL FORWARD REWIND ---------- ---------- ---------- ---------- SMITH 800 1250 JAMES 950 1250 ADAMS 1100 1300 WARD 1250 1500 MARTIN 1250 1600 MILLER 1300 2450 800 TURNER 1500 2850 950 ALLEN 1600 2975 1100 CLARK 2450 3000 1250 BLAKE 2850 3000 1250 JONES 2975 5000 1300 SCOTT 3000 1500 FORD 3000 1600 KING 5000 2450 |
EAN: 2147483647
Pages: 235