Oracle窗口从句简析(二)
(*)
OVER
(
ORDER BY
SAL
RANGE BETWEEN
SAL*.1 PRECEDING
AND
SAL*.1 FOLLOWING
) COUNT
FROM
EMP;
引用
ENAME SAL LOW HIGH COUNT
---------- ---------- ---------- ---------- ----------
SMITH 800 720 880 1
JAMES 950 855 1045 1
ADAMS 1100 990 1210 1
WARD 1250 1125 1375 3
MARTIN 1250 1125 1375 3
MILLER 1300 1170 1430 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
排序的关键字是薪水。雇员中与之相比，薪水在10%上限浮动范围内的进行统计。
使用RANGE，结果是明确的。当有两行得到的是同一个值，它们要么一起包含在窗口里，要么一起不包含在窗口里。
Sql代码
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;
引用 www.2cto.com
ENAME SAL SUMROWS SUMRANGE
---------- ---------- ---------- ----------
SMITH 800 800 800
JAMES 950 1750 1750
ADAMS 1100 2850 2850
WARD 1250 4100 5350
MARTIN 1250 5350 5350
MILLER 1300 6650 6650
TURNER 1500 8150 8150
ALLEN 1600 9750 9750
CLARK 2450 12200 12200
BLAKE 2850 15050 15050
JONES 2975 18025 18025
SCOTT 3000 21025 24025
FORD 3000 24025 24025
KING 5000 29025 29025
www.2cto.com
Scott和Ford的薪水都是3000。使用RANGE的分析函数是明确的，这两行都返回相同的值，而是用ROWS的分析函数，每一行返回的值不同。
ROWS CURRENT ROW指向唯一的一行，RANGE CURRENT ROW指向排序关键字等于当前行的所有行。
当使用不带有窗口从句的ORDER BY时，支持窗口从句的分析函数的默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。
对于日期和时间戳，间隔可以是几天，日到秒(day-to-seconds)或者年到月(year-to-month)的间隔。
Sql代码
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
(
ORDER BY
TRUNC(HIREDATE,'MM')
RANGE BETWEEN
INTERVAL '1' MONTH PRECEDING
AND
INTERVAL '1' MONTH FOLLOWING
) "3MONTHS"
FROM
EMP
ORDER BY
HIREDATE;
引用 www.2cto.com
ENAME HIREDATE SAL PREVIOUS CURRENT NEXT 3MONTHS
---------- --------- -------- -------- -------- -------- --------
SMITH 17-DEC-80 800