Oracle窗口从句简析对Oracle窗口一直搞不清楚，最近看了LaurentSchneider写的"AdvancedSQLProgramming"一书对于这部分的描述，感觉写的很清楚，故摘译下来，以备查看。www.2cto.com窗口partitio...S
对Oracle窗口一直搞不清楚，最近看了Laurent Schneider 写的"Advanced SQL Programming"一书对于这部分的描述，感觉写的很清楚，故摘译下来，以备查看。
www.2cto.com
窗口
partition从句并不是限制分析函数运算范围的唯一方法。当使用ROWS BETWEEN从句时，行就会被排序，一个窗口就会被定义。
Sql代码
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
引用
ENAME HIREDATE SAL MAX_BEFORE MAX_AFTER
---------- --------- ---------- ---------- ----------
SMITH 17-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
对于每一行而言，当前行之前的最高薪水和之后的最高薪水会被返回。ORDER BY从句在这儿不是用于排名，而是用于声明一个窗口。
用ORDER BY从句进行合计可以产生累进的合计值。
Sql代码
SELECT
ENAME,
SAL,
SUM(SAL) OVER (ORDER BY ENAME ROWS UNBOUNDED PRECEDING) CUMSUM
FROM
EMP;
www.2cto.com
引用
ENAME SAL CUMSUM
---------- ---------- ----------
ADAMS 1100 1100
ALLEN 1600 2700
BLAKE 2850 5550
CLARK 2450 8000
FORD 3000 11000
JAMES 950 11950
JONES 2975 14925
KING 5000 19925
MARTIN 1250 21175
MILLER 1300 22475
SCOTT 3000 25475
SMITH 800 26275
TURNER 1500 27775
WARD 1250 29025
www.2cto.com
最低的边界就是UNBOUNDED PRECEDING(第一行)，当前行是CURRENT ROW，最高的行是UNBOUNDED FOLLOWING(最后一行)。
当前行之前和之后的行使用n PRECEDING和n FOLLOWING来获得，n是指与当前行的相对位置。
当没有声明BETWEEN时，窗口默认于当前行结束。
Sql代码
SELECT
ENAME,
SAL,
AVG(SAL) OVER (ORDER BY SAL ROWS 1 PRECEDING) AVG
FROM
EMP;
引用
ENAME SAL AVG
---------- ---------- ----------
SMITH 800 800
JAMES 950 875
ADAMS 1100 1025
WARD 1250 1175
MARTIN 1250 1250
MILLER 1300 1275
TURNER 1500 1400
ALLEN 1600 1550
CLARK 2450 2025
BLAKE 2850 2650
JONES 2975 2912.5
SCOTT 3000 2987.5
FORD 3000 3000
KING 5000 4000
为当前行和上一行计算平均值。窗口开始于当前行之前的上一行，结束于当前行。
www.2cto.com
RANGE与ROWS类似，但间隔不是行数，而是数值或者时间。
Sql代码
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;
引用
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,&＃39;MM&＃39;)
RANGE BETWEEN
INTERVAL &＃39;1&＃39; MONTH PRECEDING
AND
INTERVAL &＃39;1&＃39; MONTH PRECEDING
) "PREVIOUS",
AVG(SAL)
OVER
(
ORDER BY
TRUNC(HIREDATE,&＃39;MM&＃39;)
RANGE CURRENT ROW
) "CURRENT",
AVG(SAL)
OVER
(
ORDER BY
TRUNC(HIREDATE,&＃39;MM&＃39;)
RANGE BETWEEN
INTERVAL &＃39;1&＃39; MONTH FOLLOWING
AND
INTERVAL &＃39;1&＃39; MONTH FOLLOWING
) "NEXT",
AVG(SAL)
OVER
(
ORDER BY
TRUNC(HIREDATE,&＃39;MM&＃39;)
RANGE BETWEEN
INTERVAL &＃39;1&＃39; MONTH PRECEDING
AND
INTERVAL &＃39;1&＃39; MONTH FOLLOWING
) "3MONTHS"
FROM
EMP
ORDER BY
HIREDATE;
引用 www.2cto.com
ENAME HIREDATE SAL PREVIOUS CURRENT NEXT 3MONTHS
---------- --------- -------- -------- -------- -------- --------
SMITH 17-DEC-80 800 800 800
ALLEN 20-FEB-81 1600 1425 1425
WARD 22-FEB-81 1250 1425 1425
JONES 02-APR-81 2975 2975 2850 2913
BLAKE 01-MAY-81 2850 2975 2850 2450 2758
CLARK 09-JUN-81 2450 2850 2450 2650
TURNER 08-SEP-81 1500 1375 1375
MARTIN 28-SEP-81 1250 1375 1375
KING 17-NOV-81 5000 5000 1975 2983
JAMES 03-DEC-81 950 5000 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
排序的键是雇用日期中的月。上面的栏目计算了当前雇员入职前一个月内入职雇员的平均薪水，入职当前月内入职雇员的平均薪水，入职后一个月内入职雇员的平均薪水，以及入职前后三个月内入职雇员的平均薪水。
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 