mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
864 lines
30 KiB
Text
864 lines
30 KiB
Text
create table t1 (
|
|
pk int primary key,
|
|
a int,
|
|
b int,
|
|
c real
|
|
);
|
|
insert into t1 values
|
|
(101 , 0, 10, 1.1),
|
|
(102 , 0, 10, 2.1),
|
|
(103 , 1, 10, 3.1),
|
|
(104 , 1, 10, 4.1),
|
|
(108 , 2, 10, 5.1),
|
|
(105 , 2, 20, 6.1),
|
|
(106 , 2, 20, 7.1),
|
|
(107 , 2, 20, 8.15),
|
|
(109 , 4, 20, 9.15),
|
|
(110 , 4, 20, 10.15),
|
|
(111 , 5, NULL, 11.15),
|
|
(112 , 5, 1, 12.25),
|
|
(113 , 5, NULL, 13.35),
|
|
(114 , 5, NULL, 14.50),
|
|
(115 , 5, NULL, 15.65),
|
|
(116 , 6, 1, NULL),
|
|
(117 , 6, 1, 10),
|
|
(118 , 6, 1, 1.1),
|
|
(119 , 6, 1, NULL),
|
|
(120 , 6, 1, NULL),
|
|
(121 , 6, 1, NULL),
|
|
(122 , 6, 1, 2.2),
|
|
(123 , 6, 1, 20.1),
|
|
(124 , 6, 1, -10.4),
|
|
(125 , 6, 1, NULL),
|
|
(126 , 6, 1, NULL),
|
|
(127 , 6, 1, NULL);
|
|
select pk, a, b, min(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
|
|
max(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
|
|
from t1;
|
|
pk a b min max
|
|
101 0 10 10 10
|
|
102 0 10 10 10
|
|
103 1 10 10 10
|
|
104 1 10 10 10
|
|
105 2 20 20 20
|
|
106 2 20 20 20
|
|
107 2 20 10 20
|
|
108 2 10 10 20
|
|
109 4 20 20 20
|
|
110 4 20 20 20
|
|
111 5 NULL 1 1
|
|
112 5 1 1 1
|
|
113 5 NULL 1 1
|
|
114 5 NULL NULL NULL
|
|
115 5 NULL NULL NULL
|
|
116 6 1 1 1
|
|
117 6 1 1 1
|
|
118 6 1 1 1
|
|
119 6 1 1 1
|
|
120 6 1 1 1
|
|
121 6 1 1 1
|
|
122 6 1 1 1
|
|
123 6 1 1 1
|
|
124 6 1 1 1
|
|
125 6 1 1 1
|
|
126 6 1 1 1
|
|
127 6 1 1 1
|
|
select pk, a, c, min(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
|
|
max(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
|
|
from t1;
|
|
pk a c min max
|
|
101 0 1.1 1.1 2.1
|
|
102 0 2.1 1.1 2.1
|
|
103 1 3.1 3.1 4.1
|
|
104 1 4.1 3.1 4.1
|
|
105 2 6.1 6.1 7.1
|
|
106 2 7.1 6.1 8.15
|
|
107 2 8.15 5.1 8.15
|
|
108 2 5.1 5.1 8.15
|
|
109 4 9.15 9.15 10.15
|
|
110 4 10.15 9.15 10.15
|
|
111 5 11.15 11.15 12.25
|
|
112 5 12.25 11.15 13.35
|
|
113 5 13.35 12.25 14.5
|
|
114 5 14.5 13.35 15.65
|
|
115 5 15.65 14.5 15.65
|
|
116 6 NULL 10 10
|
|
117 6 10 1.1 10
|
|
118 6 1.1 1.1 10
|
|
119 6 NULL 1.1 1.1
|
|
120 6 NULL NULL NULL
|
|
121 6 NULL 2.2 2.2
|
|
122 6 2.2 2.2 20.1
|
|
123 6 20.1 -10.4 20.1
|
|
124 6 -10.4 -10.4 20.1
|
|
125 6 NULL -10.4 -10.4
|
|
126 6 NULL NULL NULL
|
|
127 6 NULL NULL NULL
|
|
create table t2 (
|
|
pk int primary key,
|
|
a int,
|
|
b int,
|
|
c char(10)
|
|
);
|
|
insert into t2 values
|
|
( 1, 0, 1, 'one'),
|
|
( 2, 0, 2, 'two'),
|
|
( 3, 0, 3, 'three'),
|
|
( 4, 1, 20, 'four'),
|
|
( 5, 1, 10, 'five'),
|
|
( 6, 1, 40, 'six'),
|
|
( 7, 1, 30, 'seven'),
|
|
( 8, 4,300, 'eight'),
|
|
( 9, 4,100, 'nine'),
|
|
(10, 4,200, 'ten'),
|
|
(11, 4,200, 'eleven');
|
|
# First try some invalid argument queries.
|
|
select pk, a, b, c,
|
|
min(c) over (order by pk),
|
|
max(c) over (order by pk),
|
|
min(c) over (partition by a order by pk),
|
|
max(c) over (partition by a order by pk)
|
|
from t2;
|
|
pk a b c min(c) over (order by pk) max(c) over (order by pk) min(c) over (partition by a order by pk) max(c) over (partition by a order by pk)
|
|
1 0 1 one one one one one
|
|
2 0 2 two one two one two
|
|
3 0 3 three one two one two
|
|
4 1 20 four four two four four
|
|
5 1 10 five five two five four
|
|
6 1 40 six five two five six
|
|
7 1 30 seven five two five six
|
|
8 4 300 eight eight two eight eight
|
|
9 4 100 nine eight two eight nine
|
|
10 4 200 ten eight two eight ten
|
|
11 4 200 eleven eight two eight ten
|
|
# Empty frame
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 2 following and 1 following) as min1,
|
|
max(b) over (order by pk rows between 2 following and 1 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 2 following and 1 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 2 following and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 2 following and 1 following) as min1,
|
|
max(b) over (order by pk range between 2 following and 1 following) as max1,
|
|
min(b) over (partition by a order by pk range between 2 following and 1 following) as min2,
|
|
max(b) over (partition by a order by pk range between 2 following and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 preceding and 2 preceding) as min1,
|
|
max(b) over (order by pk rows between 1 preceding and 2 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 preceding and 2 preceding) as min1,
|
|
max(b) over (order by pk range between 1 preceding and 2 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 following and 0 following) as min1,
|
|
max(b) over (order by pk rows between 1 following and 0 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 following and 0 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 following and 0 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 following and 0 following) as min1,
|
|
max(b) over (order by pk range between 1 following and 0 following) as max1,
|
|
min(b) over (partition by a order by pk range between 1 following and 0 following) as min2,
|
|
max(b) over (partition by a order by pk range between 1 following and 0 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 following and 0 preceding) as min1,
|
|
max(b) over (order by pk rows between 1 following and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 following and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 following and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 following and 0 preceding) as min1,
|
|
max(b) over (order by pk range between 1 following and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 1 following and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 1 following and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 0 following and 1 preceding) as min1,
|
|
max(b) over (order by pk rows between 0 following and 1 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 0 following and 1 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 0 following and 1 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 0 following and 1 preceding) as min1,
|
|
max(b) over (order by pk range between 0 following and 1 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 0 following and 1 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 0 following and 1 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four NULL NULL NULL NULL
|
|
5 1 10 five NULL NULL NULL NULL
|
|
6 1 40 six NULL NULL NULL NULL
|
|
7 1 30 seven NULL NULL NULL NULL
|
|
8 4 300 eight NULL NULL NULL NULL
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
# 1 row frame.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between current row and current row) as min1,
|
|
max(b) over (order by pk rows between current row and current row) as max1,
|
|
min(b) over (partition by a order by pk rows between current row and current row) as min2,
|
|
max(b) over (partition by a order by pk rows between current row and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 0 preceding and current row) as min1,
|
|
max(b) over (order by pk rows between 0 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk rows between 0 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk rows between 0 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 0 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk rows between 0 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 preceding and 1 preceding) as min1,
|
|
max(b) over (order by pk rows between 1 preceding and 1 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two 1 1 1 1
|
|
3 0 3 three 2 2 2 2
|
|
4 1 20 four 3 3 NULL NULL
|
|
5 1 10 five 20 20 20 20
|
|
6 1 40 six 10 10 10 10
|
|
7 1 30 seven 40 40 40 40
|
|
8 4 300 eight 30 30 NULL NULL
|
|
9 4 100 nine 300 300 300 300
|
|
10 4 200 ten 100 100 100 100
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 following and 1 following) as min1,
|
|
max(b) over (order by pk rows between 1 following and 1 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 following and 1 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 following and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 2 2 2 2
|
|
2 0 2 two 3 3 3 3
|
|
3 0 3 three 20 20 NULL NULL
|
|
4 1 20 four 10 10 10 10
|
|
5 1 10 five 40 40 40 40
|
|
6 1 40 six 30 30 30 30
|
|
7 1 30 seven 300 300 NULL NULL
|
|
8 4 300 eight 100 100 100 100
|
|
9 4 100 nine 200 200 200 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
# Try a larger offset.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 3 following and 3 following) as min1,
|
|
max(b) over (order by pk rows between 3 following and 3 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 3 following and 3 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 3 following and 3 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 20 20 NULL NULL
|
|
2 0 2 two 10 10 NULL NULL
|
|
3 0 3 three 40 40 NULL NULL
|
|
4 1 20 four 30 30 30 30
|
|
5 1 10 five 300 300 NULL NULL
|
|
6 1 40 six 100 100 NULL NULL
|
|
7 1 30 seven 200 200 NULL NULL
|
|
8 4 300 eight 200 200 200 200
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 3 preceding and 3 preceding) as min1,
|
|
max(b) over (order by pk rows between 3 preceding and 3 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four 1 1 NULL NULL
|
|
5 1 10 five 2 2 NULL NULL
|
|
6 1 40 six 3 3 NULL NULL
|
|
7 1 30 seven 20 20 20 20
|
|
8 4 300 eight 10 10 NULL NULL
|
|
9 4 100 nine 40 40 NULL NULL
|
|
10 4 200 ten 30 30 NULL NULL
|
|
11 4 200 eleven 300 300 300 300
|
|
# 2 row frame.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between current row and 1 following) as min1,
|
|
max(b) over (order by pk rows between current row and 1 following) as max1,
|
|
min(b) over (partition by a order by pk rows between current row and 1 following) as min2,
|
|
max(b) over (partition by a order by pk rows between current row and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 2 3 2 3
|
|
3 0 3 three 3 20 3 3
|
|
4 1 20 four 10 20 10 20
|
|
5 1 10 five 10 40 10 40
|
|
6 1 40 six 30 40 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 0 preceding and 1 following) as min1,
|
|
max(b) over (order by pk rows between 0 preceding and 1 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 0 preceding and 1 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 0 preceding and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 2 3 2 3
|
|
3 0 3 three 3 20 3 3
|
|
4 1 20 four 10 20 10 20
|
|
5 1 10 five 10 40 10 40
|
|
6 1 40 six 30 40 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 preceding and current row) as min1,
|
|
max(b) over (order by pk rows between 1 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 2 3 2 3
|
|
4 1 20 four 3 20 20 20
|
|
5 1 10 five 10 20 10 20
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 30 40 30 40
|
|
8 4 300 eight 30 300 300 300
|
|
9 4 100 nine 100 300 100 300
|
|
10 4 200 ten 100 200 100 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 1 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk rows between 1 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 2 3 2 3
|
|
4 1 20 four 3 20 20 20
|
|
5 1 10 five 10 20 10 20
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 30 40 30 40
|
|
8 4 300 eight 30 300 300 300
|
|
9 4 100 nine 100 300 100 300
|
|
10 4 200 ten 100 200 100 200
|
|
11 4 200 eleven 200 200 200 200
|
|
# Try a larger frame/offset.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between current row and 3 following) as min1,
|
|
max(b) over (order by pk rows between current row and 3 following) as max1,
|
|
min(b) over (partition by a order by pk rows between current row and 3 following) as min2,
|
|
max(b) over (partition by a order by pk rows between current row and 3 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 20 1 3
|
|
2 0 2 two 2 20 2 3
|
|
3 0 3 three 3 40 3 3
|
|
4 1 20 four 10 40 10 40
|
|
5 1 10 five 10 300 10 40
|
|
6 1 40 six 30 300 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 2 preceding and 1 following) as min1,
|
|
max(b) over (order by pk rows between 2 preceding and 1 following) as max1,
|
|
min(b) over (partition by a order by pk rows between 2 preceding and 1 following) as min2,
|
|
max(b) over (partition by a order by pk rows between 2 preceding and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 1 3 1 3
|
|
3 0 3 three 1 20 1 3
|
|
4 1 20 four 2 20 10 20
|
|
5 1 10 five 3 40 10 40
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 10 300 10 40
|
|
8 4 300 eight 30 300 100 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 100 300 100 300
|
|
11 4 200 eleven 100 200 100 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 3 preceding and current row) as min1,
|
|
max(b) over (order by pk rows between 3 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk rows between 3 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk rows between 3 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 1 3 1 3
|
|
4 1 20 four 1 20 20 20
|
|
5 1 10 five 2 20 10 20
|
|
6 1 40 six 3 40 10 40
|
|
7 1 30 seven 10 40 10 40
|
|
8 4 300 eight 10 300 300 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 30 300 100 300
|
|
11 4 200 eleven 100 300 100 300
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk rows between 3 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk rows between 3 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 1 3 1 3
|
|
4 1 20 four 1 20 20 20
|
|
5 1 10 five 2 20 10 20
|
|
6 1 40 six 3 40 10 40
|
|
7 1 30 seven 10 40 10 40
|
|
8 4 300 eight 10 300 300 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 30 300 100 300
|
|
11 4 200 eleven 100 300 100 300
|
|
# Check range frame bounds
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between current row and current row) as min1,
|
|
max(b) over (order by pk range between current row and current row) as max1,
|
|
min(b) over (partition by a order by pk range between current row and current row) as min2,
|
|
max(b) over (partition by a order by pk range between current row and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 0 preceding and current row) as min1,
|
|
max(b) over (order by pk range between 0 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk range between 0 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk range between 0 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 0 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk range between 0 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 2 2 2 2
|
|
3 0 3 three 3 3 3 3
|
|
4 1 20 four 20 20 20 20
|
|
5 1 10 five 10 10 10 10
|
|
6 1 40 six 40 40 40 40
|
|
7 1 30 seven 30 30 30 30
|
|
8 4 300 eight 300 300 300 300
|
|
9 4 100 nine 100 100 100 100
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 preceding and 1 preceding) as min1,
|
|
max(b) over (order by pk range between 1 preceding and 1 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two 1 1 1 1
|
|
3 0 3 three 2 2 2 2
|
|
4 1 20 four 3 3 NULL NULL
|
|
5 1 10 five 20 20 20 20
|
|
6 1 40 six 10 10 10 10
|
|
7 1 30 seven 40 40 40 40
|
|
8 4 300 eight 30 30 NULL NULL
|
|
9 4 100 nine 300 300 300 300
|
|
10 4 200 ten 100 100 100 100
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 following and 1 following) as min1,
|
|
max(b) over (order by pk range between 1 following and 1 following) as max1,
|
|
min(b) over (partition by a order by pk range between 1 following and 1 following) as min2,
|
|
max(b) over (partition by a order by pk range between 1 following and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 2 2 2 2
|
|
2 0 2 two 3 3 3 3
|
|
3 0 3 three 20 20 NULL NULL
|
|
4 1 20 four 10 10 10 10
|
|
5 1 10 five 40 40 40 40
|
|
6 1 40 six 30 30 30 30
|
|
7 1 30 seven 300 300 NULL NULL
|
|
8 4 300 eight 100 100 100 100
|
|
9 4 100 nine 200 200 200 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
# Try a larger offset.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 3 following and 3 following) as min1,
|
|
max(b) over (order by pk range between 3 following and 3 following) as max1,
|
|
min(b) over (partition by a order by pk range between 3 following and 3 following) as min2,
|
|
max(b) over (partition by a order by pk range between 3 following and 3 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 20 20 NULL NULL
|
|
2 0 2 two 10 10 NULL NULL
|
|
3 0 3 three 40 40 NULL NULL
|
|
4 1 20 four 30 30 30 30
|
|
5 1 10 five 300 300 NULL NULL
|
|
6 1 40 six 100 100 NULL NULL
|
|
7 1 30 seven 200 200 NULL NULL
|
|
8 4 300 eight 200 200 200 200
|
|
9 4 100 nine NULL NULL NULL NULL
|
|
10 4 200 ten NULL NULL NULL NULL
|
|
11 4 200 eleven NULL NULL NULL NULL
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 3 preceding and 3 preceding) as min1,
|
|
max(b) over (order by pk range between 3 preceding and 3 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one NULL NULL NULL NULL
|
|
2 0 2 two NULL NULL NULL NULL
|
|
3 0 3 three NULL NULL NULL NULL
|
|
4 1 20 four 1 1 NULL NULL
|
|
5 1 10 five 2 2 NULL NULL
|
|
6 1 40 six 3 3 NULL NULL
|
|
7 1 30 seven 20 20 20 20
|
|
8 4 300 eight 10 10 NULL NULL
|
|
9 4 100 nine 40 40 NULL NULL
|
|
10 4 200 ten 30 30 NULL NULL
|
|
11 4 200 eleven 300 300 300 300
|
|
# 2 row frame.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between current row and 1 following) as min1,
|
|
max(b) over (order by pk range between current row and 1 following) as max1,
|
|
min(b) over (partition by a order by pk range between current row and 1 following) as min2,
|
|
max(b) over (partition by a order by pk range between current row and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 2 3 2 3
|
|
3 0 3 three 3 20 3 3
|
|
4 1 20 four 10 20 10 20
|
|
5 1 10 five 10 40 10 40
|
|
6 1 40 six 30 40 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 0 preceding and 1 following) as min1,
|
|
max(b) over (order by pk range between 0 preceding and 1 following) as max1,
|
|
min(b) over (partition by a order by pk range between 0 preceding and 1 following) as min2,
|
|
max(b) over (partition by a order by pk range between 0 preceding and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 2 3 2 3
|
|
3 0 3 three 3 20 3 3
|
|
4 1 20 four 10 20 10 20
|
|
5 1 10 five 10 40 10 40
|
|
6 1 40 six 30 40 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 preceding and current row) as min1,
|
|
max(b) over (order by pk range between 1 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk range between 1 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk range between 1 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 2 3 2 3
|
|
4 1 20 four 3 20 20 20
|
|
5 1 10 five 10 20 10 20
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 30 40 30 40
|
|
8 4 300 eight 30 300 300 300
|
|
9 4 100 nine 100 300 100 300
|
|
10 4 200 ten 100 200 100 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 1 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk range between 1 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 2 3 2 3
|
|
4 1 20 four 3 20 20 20
|
|
5 1 10 five 10 20 10 20
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 30 40 30 40
|
|
8 4 300 eight 30 300 300 300
|
|
9 4 100 nine 100 300 100 300
|
|
10 4 200 ten 100 200 100 200
|
|
11 4 200 eleven 200 200 200 200
|
|
# Try a larger frame/offset.
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between current row and 3 following) as min1,
|
|
max(b) over (order by pk range between current row and 3 following) as max1,
|
|
min(b) over (partition by a order by pk range between current row and 3 following) as min2,
|
|
max(b) over (partition by a order by pk range between current row and 3 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 20 1 3
|
|
2 0 2 two 2 20 2 3
|
|
3 0 3 three 3 40 3 3
|
|
4 1 20 four 10 40 10 40
|
|
5 1 10 five 10 300 10 40
|
|
6 1 40 six 30 300 30 40
|
|
7 1 30 seven 30 300 30 30
|
|
8 4 300 eight 100 300 100 300
|
|
9 4 100 nine 100 200 100 200
|
|
10 4 200 ten 200 200 200 200
|
|
11 4 200 eleven 200 200 200 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 2 preceding and 1 following) as min1,
|
|
max(b) over (order by pk range between 2 preceding and 1 following) as max1,
|
|
min(b) over (partition by a order by pk range between 2 preceding and 1 following) as min2,
|
|
max(b) over (partition by a order by pk range between 2 preceding and 1 following) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 2 1 2
|
|
2 0 2 two 1 3 1 3
|
|
3 0 3 three 1 20 1 3
|
|
4 1 20 four 2 20 10 20
|
|
5 1 10 five 3 40 10 40
|
|
6 1 40 six 10 40 10 40
|
|
7 1 30 seven 10 300 10 40
|
|
8 4 300 eight 30 300 100 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 100 300 100 300
|
|
11 4 200 eleven 100 200 100 200
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 3 preceding and current row) as min1,
|
|
max(b) over (order by pk range between 3 preceding and current row) as max1,
|
|
min(b) over (partition by a order by pk range between 3 preceding and current row) as min2,
|
|
max(b) over (partition by a order by pk range between 3 preceding and current row) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 1 3 1 3
|
|
4 1 20 four 1 20 20 20
|
|
5 1 10 five 2 20 10 20
|
|
6 1 40 six 3 40 10 40
|
|
7 1 30 seven 10 40 10 40
|
|
8 4 300 eight 10 300 300 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 30 300 100 300
|
|
11 4 200 eleven 100 300 100 300
|
|
select pk, a, b, c,
|
|
min(b) over (order by pk range between 3 preceding and 0 preceding) as min1,
|
|
max(b) over (order by pk range between 3 preceding and 0 preceding) as max1,
|
|
min(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as min2,
|
|
max(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as max2
|
|
from t2;
|
|
pk a b c min1 max1 min2 max2
|
|
1 0 1 one 1 1 1 1
|
|
2 0 2 two 1 2 1 2
|
|
3 0 3 three 1 3 1 3
|
|
4 1 20 four 1 20 20 20
|
|
5 1 10 five 2 20 10 20
|
|
6 1 40 six 3 40 10 40
|
|
7 1 30 seven 10 40 10 40
|
|
8 4 300 eight 10 300 300 300
|
|
9 4 100 nine 30 300 100 300
|
|
10 4 200 ten 30 300 100 300
|
|
11 4 200 eleven 100 300 100 300
|
|
drop table t2;
|
|
drop table t1;
|