分析函数四：Winodwing子句

4.Windowing 子句
RANGE UNBOUNDED PRECEDING意味着将分析函数用于当前组中从第一行到当前行的每一行。默认的窗口是一个固定的窗口，仅仅在一组的第一行开始，一直持续到当前行。

SELECT ename, deptno, sal,
  AVG(sal) over (PARTITION BY deptno
                 ORDER BY deptno
                 ROWS 2 preceding) sum_ename_deptno
FROM emp;

1    CLARK        10    2450.00    2450
2    KING        10    5000.00    3725
3    MILLER    10    1300.00    2916.66666666667
4    SMITH        20    800.00    800
5    ADAMS        20    1100.00    950
6    FORD        20    3000.00    1633.33333333333
7    SCOTT        20    3000.00    2366.66666666667
8    JONES        20    2975.00    2991.66666666667
9    ALLEN        30    1600.00    1600
10    BLAKE        30    2850.00    2225
11    MARTIN    30    1250.00    1900
12    JAMES        30    950.00    1683.33333333333
13    TURNER    30    1500.00    1233.33333333333
14    WARD        30    1250.00    1233.33333333333

这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行的SAL列的总和。

要使用窗口，必须使用Order by子句


5.Range窗口
Range窗口根据WHERE子句将行集中在一起。例如，有"range 5 preceding"，将产生一个滑动的窗口，它在组中拥有所有当前行以前5行的集合。

一般来说Range窗口仅对NUMBERS和DATES起作用。

count(*) over (order by hiredate asc range 100 preceding)

-- 向前查找hiredate在100天内的记录行
SELECT ename, deptno, sal, hiredate,
  count(*) over (order by hiredate asc range 100 preceding)
FROM emp ORDER BY hiredate;

1    SMITH        20    800.00    1980-12-17    1
2    ALLEN        30    1600.00    1981-2-20    2
3    WARD        30    1250.00    1981-2-22    3
4    JONES        20    2975.00    1981-4-2    3
5    BLAKE        30    2850.00    1981-5-1    4
6    CLARK        10    2450.00    1981-6-9    3
7    TURNER    30    1500.00    1981-9-8    2
8    MARTIN    30    1250.00    1981-9-28    2
9    KING        10    5000.00    1981-11-17    3
10    JAMES        30    950.00    1981-12-3    5
11    FORD        20    3000.00    1981-12-3    5
12    MILLER    10    1300.00    1982-1-23    4
13    SCOTT        20    3000.00    1987-4-19    1
14    ADAMS        20    1100.00    1987-5-23    2
查找当前行以前的所有行，前面所有行的HIREDATE在当前行的HIREDATE的100天以内

-- 向后查找hiredate在100天内的记录行
SELECT ename, deptno, sal, hiredate,
  count(*) over (order by hiredate DESC range 100 preceding)
FROM emp ORDER BY hiredate;

1    SMITH        20    800.00    1980-12-17    3
2    ALLEN        30    1600.00    1981-2-20    4
3    WARD        30    1250.00    1981-2-22    3
4    JONES        20    2975.00    1981-4-2    3
5    BLAKE        30    2850.00    1981-5-1    2
6    CLARK        10    2450.00    1981-6-9    2
7    TURNER    30    1500.00    1981-9-8    5
8    MARTIN    30    1250.00    1981-9-28    4
9    KING        10    5000.00    1981-11-17    4
10    JAMES        30    950.00    1981-12-3    3
11    FORD        20    3000.00    1981-12-3    3
12    MILLER    10    1300.00    1982-1-23    1
13    SCOTT        20    3000.00    1987-4-19    2
14    ADAMS        20    1100.00    1987-5-23    1

-- 向前查找hiredate在100天内的记录行
SELECT ename, sal, hiredate, hiredate-100 windowtop,
  first_value(ename) over(ORDER BY hiredate ASC
                          RANGE 100 preceding) ename_prec,
    first_value(hiredate)
  over (ORDER BY hiredate ASC
        RANGE 100 preceding) hiredate_prec
FROM emp
ORDER BY hiredate ASC;

1    SMITH        800.00    1980-12-17    1980-9-8    SMITH        1980-12-17
2    ALLEN        1600.00    1981-2-20    1980-11-12    SMITH        1980-12-17
3    WARD        1250.00    1981-2-22    1980-11-14    SMITH        1980-12-17
4    JONES        2975.00    1981-4-2    1980-12-23    ALLEN        1981-2-20
5    BLAKE        2850.00    1981-5-1    1981-1-21    ALLEN        1981-2-20
6    CLARK        2450.00    1981-6-9    1981-3-1    JONES        1981-4-2
7    TURNER    1500.00    1981-9-8    1981-5-31    CLARK        1981-6-9
8    MARTIN    1250.00    1981-9-28    1981-6-20    TURNER    1981-9-8
9    KING        5000.00    1981-11-17    1981-8-9    TURNER    1981-9-8
10    FORD        3000.00    1981-12-3    1981-8-25    TURNER    1981-9-8
11    JAMES        950.00    1981-12-3    1981-8-25    TURNER    1981-9-8
12    MILLER    1300.00    1982-1-23    1981-10-15    KING        1981-11-17
13    SCOTT        3000.00    1987-4-19    1987-1-9    SCOTT        1987-4-19
14    ADAMS        1100.00    1987-5-23    1987-2-12    SCOTT        1987-4-19


-- 向后查找hiredate在100天内的记录行
SELECT ename, sal, hiredate, hiredate-100 windowtop,
  first_value(ename) over(ORDER BY hiredate DESC
                          RANGE 100 preceding) ename_prec,
    first_value(hiredate)
  over (ORDER BY hiredate ASC
        RANGE 100 preceding) hiredate_prec
FROM emp
ORDER BY hiredate ASC;

1    SMITH        800.00    1980-12-17    1980-9-8    WARD        1980-12-17
2    ALLEN        1600.00    1981-2-20    1980-11-12    BLAKE        1980-12-17
3    WARD        1250.00    1981-2-22    1980-11-14    BLAKE        1980-12-17
4    JONES        2975.00    1981-4-2    1980-12-23    CLARK        1981-2-20
5    BLAKE        2850.00    1981-5-1    1981-1-21    CLARK        1981-2-20
6    CLARK        2450.00    1981-6-9    1981-3-1    TURNER    1981-4-2
7    TURNER    1500.00    1981-9-8    1981-5-31    FORD        1981-6-9
8    MARTIN    1250.00    1981-9-28    1981-6-20    FORD        1981-9-8
9    KING        5000.00    1981-11-17    1981-8-9    MILLER    1981-9-8
10    FORD        3000.00    1981-12-3    1981-8-25    MILLER    1981-9-8
11    JAMES        950.00    1981-12-3    1981-8-25    MILLER    1981-9-8
12    MILLER    1300.00    1982-1-23    1981-10-15    MILLER    1981-11-17
13    SCOTT        3000.00    1987-4-19    1987-1-9    ADAMS        1987-4-19
14    ADAMS        1100.00    1987-5-23    1987-2-12    ADAMS        1987-4-19



-- 查询当前行和前100天内的所有行sal的平均值
-- 查询当前行和后100天内的所有行sal的平均值
SELECT ename, hiredate ,sal,
    AVG(sal) over (ORDER BY hiredate ASC RANGE 100 preceding)
    avg_sal_100_days_before,
    AVG(sal) over (ORDER BY hiredate DESC RANGE 100 preceding)
    avg_sal_100_days_after
FROM emp
ORDER BY hiredate

1    SMITH        1980-12-17    800.00    800            1216.66666666667
2    ALLEN        1981-2-20    1600.00    1200            2168.75
3    WARD        1981-2-22    1250.00    1216.66666666667    2358.33333333333
4    JONES        1981-4-2    2975.00    1941.66666666667    2758.33333333333
5    BLAKE        1981-5-1    2850.00    2168.75        2650
6    CLARK        1981-6-9    2450.00    2758.33333333333    1975
7    TURNER    1981-9-8    1500.00    1975            2340
8    MARTIN    1981-9-28    1250.00    1375            2550
9    KING        1981-11-17    5000.00    2583.33333333333    2562.5
10    JAMES        1981-12-3    950.00    2340            1750
11    FORD        1981-12-3    3000.00    2340            1750
12    MILLER    1982-1-23    1300.00    2562.5        1300
13    SCOTT        1987-4-19    3000.00    3000            2050
14    ADAMS        1987-5-23    1100.00    2050            1100