6.Row窗口
Row窗口是物理单元，是包括在窗口中的行的物理数。
count(*) over (order by x ROWS 5 preceding)
窗口包括6行，当前行以及该行前面的5行。利用ROW分区，就没有RANGE的限制，数据可以是任意类型的，且order by 可包括很多列
升序：
SELECT ename, sal, hiredate ,
First_Value(ename) over (ORDER BY hiredate ASC ROWS 5 preceding)
ename_prec,
First_Value(hiredate) over (ORDER BY hiredate ASC ROWS 5 preceding)
hiredate_prec
FROM emp
ORDER BY hiredate ASC;
1 SMITH 800.00 1980-12-17 SMITH 1980-12-17
2 ALLEN 1600.00 1981-2-20 SMITH 1980-12-17
3 WARD 1250.00 1981-2-22 SMITH 1980-12-17
4 JONES 2975.00 1981-4-2 SMITH 1980-12-17
5 BLAKE 2850.00 1981-5-1 SMITH 1980-12-17
6 CLARK 2450.00 1981-6-9 SMITH 1980-12-17
7 TURNER 1500.00 1981-9-8 ALLEN 1981-2-20
8 MARTIN 1250.00 1981-9-28 WARD 1981-2-22
9 KING 5000.00 1981-11-17 JONES 1981-4-2
10 JAMES 950.00 1981-12-3 BLAKE 1981-5-1
11 FORD 3000.00 1981-12-3 CLARK 1981-6-9
12 MILLER 1300.00 1982-1-23 TURNER 1981-9-8
13 SCOTT 3000.00 1987-4-19 MARTIN 1981-9-28
14 ADAMS 1100.00 1987-5-23 KING 1981-11-17
降序：
SELECT ename, sal, hiredate ,
First_Value(ename) over (ORDER BY hiredate DESC ROWS 5 preceding)
ename_prec,
First_Value(hiredate) over (ORDER BY hiredate DESC ROWS 5 preceding)
hiredate_prec
FROM emp
ORDER BY hiredate DESC;
1 ADAMS 1100.00 1987-5-23 ADAMS 1987-5-23
2 SCOTT 3000.00 1987-4-19 ADAMS 1987-5-23
3 MILLER 1300.00 1982-1-23 ADAMS 1987-5-23
4 JAMES 950.00 1981-12-3 ADAMS 1987-5-23
5 FORD 3000.00 1981-12-3 ADAMS 1987-5-23
6 KING 5000.00 1981-11-17 ADAMS 1987-5-23
7 MARTIN 1250.00 1981-9-28 SCOTT 1987-4-19
8 TURNER 1500.00 1981-9-8 MILLER 1982-1-23
9 CLARK 2450.00 1981-6-9 JAMES 1981-12-3
10 BLAKE 2850.00 1981-5-1 FORD 1981-12-3
11 JONES 2975.00 1981-4-2 KING 1981-11-17
12 WARD 1250.00 1981-2-22 MARTIN 1981-9-28
13 ALLEN 1600.00 1981-2-20 TURNER 1981-9-8
14 SMITH 800.00 1980-12-17 CLARK 1981-6-9
SELECT ename, sal, hiredate ,
First_Value(ename) over (ORDER BY hiredate ASC ROWS 5 preceding)
ename_prec,
First_Value(hiredate) over (ORDER BY hiredate ASC ROWS 5 preceding)
hiredate_prec
FROM emp
ORDER BY hiredate DESC;
1 ADAMS 1100.00 1987-5-23 KING 1981-11-17
2 SCOTT 3000.00 1987-4-19 MARTIN 1981-9-28
3 MILLER 1300.00 1982-1-23 TURNER 1981-9-8
4 JAMES 950.00 1981-12-3 BLAKE 1981-5-1
5 FORD 3000.00 1981-12-3 CLARK 1981-6-9
6 KING 5000.00 1981-11-17 JONES 1981-4-2
7 MARTIN 1250.00 1981-9-28 WARD 1981-2-22
8 TURNER 1500.00 1981-9-8 ALLEN 1981-2-20
9 CLARK 2450.00 1981-6-9 SMITH 1980-12-17
10 BLAKE 2850.00 1981-5-1 SMITH 1980-12-17
11 JONES 2975.00 1981-4-2 SMITH 1980-12-17
12 WARD 1250.00 1981-2-22 SMITH 1980-12-17
13 ALLEN 1600.00 1981-2-20 SMITH 1980-12-17
14 SMITH 800.00 1980-12-17 SMITH 1980-12-17
Count(*) 计算有多少行参与计算
SELECT ename, hiredate, sal,
AVG(sal) over(ORDER BY hiredate ASC ROWS 5 preceding) avg_5_before,
COUNT(*) over(ORDER BY hiredate ASC ROWS 5 preceding) obs_before,
AVG(sal) over(ORDER BY hiredate DESC ROWS 5 preceding) avg_5_after,
COUNT(*) over(ORDER BY hiredate DESC ROWS 5 preceding) obs_after
FROM emp
ORDER BY hiredate;
1 SMITH 1980-12-17 800.00 800 1 1987.5 6
2 ALLEN 1981-2-20 1600.00 1200 2 2104.16666666667 6
3 WARD 1981-2-22 1250.00 1216.66666666667 3 2045.83333333333 6
4 JONES 1981-4-2 2975.00 1656.25 4 2670.83333333333 6
5 BLAKE 1981-5-1 2850.00 1895 5 2675 6
6 CLARK 1981-6-9 2450.00 1987.5 6 2358.33333333333 6
7 TURNER 1981-9-8 1500.00 2104.16666666667 6 2166.66666666667 6
8 MARTIN 1981-9-28 1250.00 2045.83333333333 6 2416.66666666667 6
9 KING 1981-11-17 5000.00 2670.83333333333 6 2391.66666666667 6
10 JAMES 1981-12-3 950.00 2333.33333333333 6 1587.5 4
11 FORD 1981-12-3 3000.00 2358.33333333333 6 1870 5
12 MILLER 1982-1-23 1300.00 2166.66666666667 6 1800 3
13 SCOTT 1987-4-19 3000.00 2416.66666666667 6 2050 2
14 ADAMS 1987-5-23 1100.00 2391.66666666667 6 1100 1
6.Specifying 窗口
● UNBOUNDED PRECEDING 这个窗口从当前分区的第一行开始，并结束于正在处理的当前行
● CURRENT ROW 该窗口从当前行开始(并结束)
● Numeric Expression PRECEDING
● Numeric Expression FOLLOWING
SELECT deptno, ename, hiredate,
COUNT(*) over(PARTITION BY deptno
ORDER BY hiredate NULLS FIRST
RANGE 100 preceding) cnt_range,
COUNT(*) over(PARTITION BY deptno
ORDER BY hiredate NULLS FIRST
ROWS 2 preceding) cnt_rows
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, hiredate;
1 10 CLARK 1981-6-9 1 1
2 10 KING 1981-11-17 1 2
3 10 MILLER 1982-1-23 2 3
4 20 SMITH 1980-12-17 1 1
5 20 JONES 1981-4-2 1 2
6 20 FORD 1981-12-3 1 3
7 20 SCOTT 1987-4-19 1 3
8 20 ADAMS 1987-5-23 2 3
使用BETWEEN
SELECT ename, hiredate,
first_value(ename) over
(ORDER BY hiredate ASC
RANGE BETWEEN 100 preceding AND 100 following),
last_value(ename) over
(ORDER BY hiredate ASC
RANGE BETWEEN 100 preceding AND 100 following)
FROM emp
ORDER BY hiredate ASC;
1 SMITH 1980-12-17 SMITH WARD
2 ALLEN 1981-2-20 SMITH BLAKE
3 WARD 1981-2-22 SMITH BLAKE
4 JONES 1981-4-2 ALLEN CLARK
5 BLAKE 1981-5-1 ALLEN CLARK
6 CLARK 1981-6-9 JONES TURNER
7 TURNER 1981-9-8 CLARK JAMES
8 MARTIN 1981-9-28 TURNER JAMES
9 KING 1981-11-17 TURNER MILLER
10 FORD 1981-12-3 TURNER MILLER
11 JAMES 1981-12-3 TURNER MILLER
12 MILLER 1982-1-23 KING MILLER
13 SCOTT 1987-4-19 SCOTT ADAMS
14 ADAMS 1987-5-23 SCOTT ADAMS
阅读(1040) | 评论(0) | 转发(0) |
