diff options
Diffstat (limited to 'mysql-test/r/win_nth_value.result')
| -rw-r--r-- | mysql-test/r/win_nth_value.result | 120 |
1 files changed, 120 insertions, 0 deletions
diff --git a/mysql-test/r/win_nth_value.result b/mysql-test/r/win_nth_value.result new file mode 100644 index 00000000000..abda1a2377f --- /dev/null +++ b/mysql-test/r/win_nth_value.result @@ -0,0 +1,120 @@ +create table t1 ( +pk int primary key, +a int, +b int, +c char(10), +d decimal(10, 3), +e real +); +insert into t1 values +( 1, 0, 1, 'one', 0.1, 0.001), +( 2, 0, 2, 'two', 0.2, 0.002), +( 3, 0, 3, 'three', 0.3, 0.003), +( 4, 1, 2, 'three', 0.4, 0.004), +( 5, 1, 1, 'two', 0.5, 0.005), +( 6, 1, 1, 'one', 0.6, 0.006), +( 7, 2, NULL, 'n_one', 0.5, 0.007), +( 8, 2, 1, 'n_two', NULL, 0.008), +( 9, 2, 2, NULL, 0.7, 0.009), +(10, 2, 0, 'n_four', 0.8, 0.010), +(11, 2, 10, NULL, 0.9, NULL); +select pk, +nth_value(pk, 1) over (order by pk), +nth_value(pk, 2) over (order by pk), +nth_value(pk, 0) over (order by pk), +nth_value(pk, -1) over (order by pk), +nth_value(pk, -2) over (order by pk) +from t1 +order by pk asc; +pk nth_value(pk, 1) over (order by pk) nth_value(pk, 2) over (order by pk) nth_value(pk, 0) over (order by pk) nth_value(pk, -1) over (order by pk) nth_value(pk, -2) over (order by pk) +1 1 NULL NULL NULL NULL +2 1 2 NULL NULL NULL +3 1 2 NULL NULL NULL +4 1 2 NULL NULL NULL +5 1 2 NULL NULL NULL +6 1 2 NULL NULL NULL +7 1 2 NULL NULL NULL +8 1 2 NULL NULL NULL +9 1 2 NULL NULL NULL +10 1 2 NULL NULL NULL +11 1 2 NULL NULL NULL +select pk, +nth_value(pk, pk) over (order by pk), +nth_value(pk / 0.1, pk) over (order by pk) +from t1 +order by pk asc; +pk nth_value(pk, pk) over (order by pk) nth_value(pk / 0.1, pk) over (order by pk) +1 1 10.0000 +2 2 20.0000 +3 3 30.0000 +4 4 40.0000 +5 5 50.0000 +6 6 60.0000 +7 7 70.0000 +8 8 80.0000 +9 9 90.0000 +10 10 100.0000 +11 11 110.0000 +select pk, +a, +nth_value(pk, pk) over (partition by a order by pk), +nth_value(pk, a + 1) over (partition by a order by pk) +from t1 +order by pk asc; +pk a nth_value(pk, pk) over (partition by a order by pk) nth_value(pk, a + 1) over (partition by a order by pk) +1 0 1 1 +2 0 2 1 +3 0 3 1 +4 1 NULL NULL +5 1 NULL 5 +6 1 NULL 5 +7 2 NULL NULL +8 2 NULL NULL +9 2 NULL 9 +10 2 NULL 9 +11 2 NULL 9 +select pk, +a, +nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) +from t1; +pk a nth_value(pk, 1) over (partition by a order by pk ROWS between 1 preceding and 1 following) +1 0 1 +2 0 1 +3 0 2 +4 1 4 +5 1 4 +6 1 5 +7 2 7 +8 2 7 +9 2 8 +10 2 9 +11 2 10 +select pk, +a, +nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following), +nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) +from t1 +order by pk asc; +pk a nth_value(a, 1) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 2) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 3) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 4) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 5) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 6) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 7) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 8) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 9) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 10) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 11) over (order by a RANGE BETWEEN 1 preceding and 1 following) nth_value(a, 12) over (order by a RANGE BETWEEN 1 preceding and 1 following) +1 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +2 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +3 0 0 0 0 1 1 1 NULL NULL NULL NULL NULL NULL +4 1 0 0 0 1 1 1 2 2 2 2 2 NULL +5 1 0 0 0 1 1 1 2 2 2 2 2 NULL +6 1 0 0 0 1 1 1 2 2 2 2 2 NULL +7 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +8 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +9 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +10 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +11 2 1 1 1 2 2 2 2 2 NULL NULL NULL NULL +drop table t1; |