diff options
Diffstat (limited to 'mysql-test/main/win_min_max.result')
| -rw-r--r-- | mysql-test/main/win_min_max.result | 864 |
1 files changed, 864 insertions, 0 deletions
diff --git a/mysql-test/main/win_min_max.result b/mysql-test/main/win_min_max.result new file mode 100644 index 00000000000..b3a90262bf7 --- /dev/null +++ b/mysql-test/main/win_min_max.result @@ -0,0 +1,864 @@ +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; |