Oracle窗口从句

yftwfsgqto

对Oracle窗口一直搞不清楚，最近看了Laurent Schneider 写的"Advanced SQL Programming"一书对于这部分的描述，感觉写的很清楚，故摘译下来，以备查看。
窗口
    partition从句并不是限制分析函数运算范围的唯一方法。当使用ROWS BETWEEN从句时，行就会被排序，一个窗口就会被定义。

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;

引用

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从句进行合计可以产生累进的合计值。

SELECT
ENAME,
SAL,
SUM(SAL) OVER (ORDER BY ENAME ROWS UNBOUNDED PRECEDING) CUMSUM
FROM
EMP;

引用

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


最低的边界就是UNBOUNDED PRECEDING(第一行)，当前行是CURRENT ROW，最高的行是UNBOUNDED FOLLOWING(最后一行)。
当前行之前和之后的行使用n PRECEDING和n FOLLOWING来获得，n是指与当前行的相对位置。
当没有声明BETWEEN时，窗口默认于当前行结束。

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


为当前行和上一行计算平均值。窗口开始于当前行之前的上一行，结束于当前行。
RANGE与ROWS类似，但间隔不是行数，而是数值或者时间。

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，结果是明确的。当有两行得到的是同一个值，它们要么一起包含在窗口里，要么一起不包含在窗口里。

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;

引用

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


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)的间隔。

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;

引用

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


排序的键是雇用日期中的月。上面的栏目计算了当前雇员入职前一个月内入职雇员的平均薪水，入职当前月内入职雇员的平均薪水，入职后一个月内入职雇员的平均薪水，以及入职前后三个月内入职雇员的平均薪水。