diff options
Diffstat (limited to 'mysql-test/main/win_lead_lag.result')
| -rw-r--r-- | mysql-test/main/win_lead_lag.result | 229 |
1 files changed, 229 insertions, 0 deletions
diff --git a/mysql-test/main/win_lead_lag.result b/mysql-test/main/win_lead_lag.result new file mode 100644 index 00000000000..5ead58fa11a --- /dev/null +++ b/mysql-test/main/win_lead_lag.result @@ -0,0 +1,229 @@ +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, +lead(pk) over (order by pk), +lead(pk, 1) over (order by pk), +lead(pk, 2) over (order by pk), +lead(pk, 0) over (order by pk), +lead(pk, -1) over (order by pk), +lead(pk, -2) over (order by pk) +from t1 +order by pk asc; +pk lead(pk) over (order by pk) lead(pk, 1) over (order by pk) lead(pk, 2) over (order by pk) lead(pk, 0) over (order by pk) lead(pk, -1) over (order by pk) lead(pk, -2) over (order by pk) +1 2 2 3 1 NULL NULL +2 3 3 4 2 1 NULL +3 4 4 5 3 2 1 +4 5 5 6 4 3 2 +5 6 6 7 5 4 3 +6 7 7 8 6 5 4 +7 8 8 9 7 6 5 +8 9 9 10 8 7 6 +9 10 10 11 9 8 7 +10 11 11 NULL 10 9 8 +11 NULL NULL NULL 11 10 9 +select pk, +lag(pk) over (order by pk), +lag(pk, 1) over (order by pk), +lag(pk, 2) over (order by pk), +lag(pk, 0) over (order by pk), +lag(pk, -1) over (order by pk), +lag(pk, -2) over (order by pk) +from t1 +order by pk asc; +pk lag(pk) over (order by pk) lag(pk, 1) over (order by pk) lag(pk, 2) over (order by pk) lag(pk, 0) over (order by pk) lag(pk, -1) over (order by pk) lag(pk, -2) over (order by pk) +1 NULL NULL NULL 1 2 3 +2 1 1 NULL 2 3 4 +3 2 2 1 3 4 5 +4 3 3 2 4 5 6 +5 4 4 3 5 6 7 +6 5 5 4 6 7 8 +7 6 6 5 7 8 9 +8 7 7 6 8 9 10 +9 8 8 7 9 10 11 +10 9 9 8 10 11 NULL +11 10 10 9 11 NULL NULL +select pk, pk - 2, +lag(pk, pk - 2) over (order by pk), +lead(pk, pk - 2) over (order by pk) +from t1 +order by pk asc; +pk pk - 2 lag(pk, pk - 2) over (order by pk) lead(pk, pk - 2) over (order by pk) +1 -1 2 NULL +2 0 2 2 +3 1 2 4 +4 2 2 6 +5 3 2 8 +6 4 2 10 +7 5 2 NULL +8 6 2 NULL +9 7 2 NULL +10 8 2 NULL +11 9 2 NULL +select pk, pk - 2, +lag(pk, pk + 2) over (order by pk), +lead(pk, pk + 2) over (order by pk) +from t1 +order by pk asc; +pk pk - 2 lag(pk, pk + 2) over (order by pk) lead(pk, pk + 2) over (order by pk) +1 -1 NULL 4 +2 0 NULL 6 +3 1 NULL 8 +4 2 NULL 10 +5 3 NULL NULL +6 4 NULL NULL +7 5 NULL NULL +8 6 NULL NULL +9 7 NULL NULL +10 8 NULL NULL +11 9 NULL NULL +select pk, a, +lead(pk) over (partition by a order by pk), +lead(pk, 1) over (partition by a order by pk), +lead(pk, 2) over (partition by a order by pk), +lead(pk, 0) over (partition by a order by pk), +lead(pk, -1) over (partition by a order by pk), +lead(pk, -2) over (partition by a order by pk) +from t1 +order by pk asc; +pk a lead(pk) over (partition by a order by pk) lead(pk, 1) over (partition by a order by pk) lead(pk, 2) over (partition by a order by pk) lead(pk, 0) over (partition by a order by pk) lead(pk, -1) over (partition by a order by pk) lead(pk, -2) over (partition by a order by pk) +1 0 2 2 3 1 NULL NULL +2 0 3 3 NULL 2 1 NULL +3 0 NULL NULL NULL 3 2 1 +4 1 5 5 6 4 NULL NULL +5 1 6 6 NULL 5 4 NULL +6 1 NULL NULL NULL 6 5 4 +7 2 8 8 9 7 NULL NULL +8 2 9 9 10 8 7 NULL +9 2 10 10 11 9 8 7 +10 2 11 11 NULL 10 9 8 +11 2 NULL NULL NULL 11 10 9 +select pk, a, +lag(pk) over (partition by a order by pk), +lag(pk, 1) over (partition by a order by pk), +lag(pk, 2) over (partition by a order by pk), +lag(pk, 0) over (partition by a order by pk), +lag(pk, -1) over (partition by a order by pk), +lag(pk, -2) over (partition by a order by pk) +from t1 +order by pk asc; +pk a lag(pk) over (partition by a order by pk) lag(pk, 1) over (partition by a order by pk) lag(pk, 2) over (partition by a order by pk) lag(pk, 0) over (partition by a order by pk) lag(pk, -1) over (partition by a order by pk) lag(pk, -2) over (partition by a order by pk) +1 0 NULL NULL NULL 1 2 3 +2 0 1 1 NULL 2 3 NULL +3 0 2 2 1 3 NULL NULL +4 1 NULL NULL NULL 4 5 6 +5 1 4 4 NULL 5 6 NULL +6 1 5 5 4 6 NULL NULL +7 2 NULL NULL NULL 7 8 9 +8 2 7 7 NULL 8 9 10 +9 2 8 8 7 9 10 11 +10 2 9 9 8 10 11 NULL +11 2 10 10 9 11 NULL NULL +select pk, a, pk - 2, +lag(pk, pk - 2) over (partition by a order by pk), +lead(pk, pk - 2) over (partition by a order by pk), +lag(pk, a - 2) over (partition by a order by pk), +lead(pk, a - 2) over (partition by a order by pk) +from t1 +order by pk asc; +pk a pk - 2 lag(pk, pk - 2) over (partition by a order by pk) lead(pk, pk - 2) over (partition by a order by pk) lag(pk, a - 2) over (partition by a order by pk) lead(pk, a - 2) over (partition by a order by pk) +1 0 -1 2 NULL 3 NULL +2 0 0 2 2 NULL NULL +3 0 1 2 NULL NULL 1 +4 1 2 NULL 6 5 NULL +5 1 3 NULL NULL 6 4 +6 1 4 NULL NULL NULL 5 +7 2 5 NULL NULL 7 7 +8 2 6 NULL NULL 8 8 +9 2 7 NULL NULL 9 9 +10 2 8 NULL NULL 10 10 +11 2 9 NULL NULL 11 11 +select pk, a, pk - 2, +lag(pk, pk + 2) over (partition by a order by pk), +lead(pk, pk + 2) over (partition by a order by pk), +lag(pk, a + 2) over (partition by a order by pk), +lead(pk, a + 2) over (partition by a order by pk) +from t1 +order by pk asc; +pk a pk - 2 lag(pk, pk + 2) over (partition by a order by pk) lead(pk, pk + 2) over (partition by a order by pk) lag(pk, a + 2) over (partition by a order by pk) lead(pk, a + 2) over (partition by a order by pk) +1 0 -1 NULL NULL NULL 3 +2 0 0 NULL NULL NULL NULL +3 0 1 NULL NULL 1 NULL +4 1 2 NULL NULL NULL NULL +5 1 3 NULL NULL NULL NULL +6 1 4 NULL NULL NULL NULL +7 2 5 NULL NULL NULL 11 +8 2 6 NULL NULL NULL NULL +9 2 7 NULL NULL NULL NULL +10 2 8 NULL NULL NULL NULL +11 2 9 NULL NULL 7 NULL +select pk, a, b, c, d, e, +lag(a) over (partition by a order by pk), +lag(b) over (partition by a order by pk), +lag(c) over (partition by a order by pk), +lag(d) over (partition by a order by pk), +lag(e) over (partition by a order by pk) +from t1 +order by pk asc; +pk a b c d e lag(a) over (partition by a order by pk) lag(b) over (partition by a order by pk) lag(c) over (partition by a order by pk) lag(d) over (partition by a order by pk) lag(e) over (partition by a order by pk) +1 0 1 one 0.100 0.001 NULL NULL NULL NULL NULL +2 0 2 two 0.200 0.002 0 1 one 0.100 0.001 +3 0 3 three 0.300 0.003 0 2 two 0.200 0.002 +4 1 2 three 0.400 0.004 NULL NULL NULL NULL NULL +5 1 1 two 0.500 0.005 1 2 three 0.400 0.004 +6 1 1 one 0.600 0.006 1 1 two 0.500 0.005 +7 2 NULL n_one 0.500 0.007 NULL NULL NULL NULL NULL +8 2 1 n_two NULL 0.008 2 NULL n_one 0.500 0.007 +9 2 2 NULL 0.700 0.009 2 1 n_two NULL 0.008 +10 2 0 n_four 0.800 0.01 2 2 NULL 0.700 0.009 +11 2 10 NULL 0.900 NULL 2 0 n_four 0.800 0.01 +select pk, a, b, a+b, +lag(a + b) over (partition by a order by pk) +from t1 +order by pk asc; +pk a b a+b lag(a + b) over (partition by a order by pk) +1 0 1 1 NULL +2 0 2 2 1 +3 0 3 3 2 +4 1 2 3 NULL +5 1 1 2 3 +6 1 1 2 2 +7 2 NULL NULL NULL +8 2 1 3 NULL +9 2 2 4 3 +10 2 0 2 4 +11 2 10 12 2 +select pk, a, b, a+b, +lag(a + b) over (partition by a order by pk) + pk +from t1 +order by pk asc; +pk a b a+b lag(a + b) over (partition by a order by pk) + pk +1 0 1 1 NULL +2 0 2 2 3 +3 0 3 3 5 +4 1 2 3 NULL +5 1 1 2 8 +6 1 1 2 8 +7 2 NULL NULL NULL +8 2 1 3 NULL +9 2 2 4 12 +10 2 0 2 14 +11 2 10 12 13 +drop table t1; |