diff options
Diffstat (limited to 'mysql-test/main/win_avg.result')
| -rw-r--r-- | mysql-test/main/win_avg.result | 95 |
1 files changed, 95 insertions, 0 deletions
diff --git a/mysql-test/main/win_avg.result b/mysql-test/main/win_avg.result new file mode 100644 index 00000000..7e539d93 --- /dev/null +++ b/mysql-test/main/win_avg.result @@ -0,0 +1,95 @@ +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, avg(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a b avg(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 10 10.0000 +102 0 10 10.0000 +103 1 10 10.0000 +104 1 10 10.0000 +105 2 20 20.0000 +106 2 20 20.0000 +107 2 20 16.6667 +108 2 10 15.0000 +109 4 20 20.0000 +110 4 20 20.0000 +111 5 NULL 1.0000 +112 5 1 1.0000 +113 5 NULL 1.0000 +114 5 NULL NULL +115 5 NULL NULL +116 6 1 1.0000 +117 6 1 1.0000 +118 6 1 1.0000 +119 6 1 1.0000 +120 6 1 1.0000 +121 6 1 1.0000 +122 6 1 1.0000 +123 6 1 1.0000 +124 6 1 1.0000 +125 6 1 1.0000 +126 6 1 1.0000 +127 6 1 1.0000 +select pk, a, c, avg(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +pk a c avg(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +101 0 1.1 1.6 +102 0 2.1 1.6 +103 1 3.1 3.5999999999999996 +104 1 4.1 3.5999999999999996 +105 2 6.1 6.6 +106 2 7.1 7.116666666666667 +107 2 8.15 6.783333333333334 +108 2 5.1 6.625000000000001 +109 4 9.15 9.65 +110 4 10.15 9.65 +111 5 11.15 11.7 +112 5 12.25 12.25 +113 5 13.35 13.366666666666667 +114 5 14.5 14.5 +115 5 15.65 15.075 +116 6 NULL 10 +117 6 10 5.55 +118 6 1.1 5.55 +119 6 NULL 1.0999999999999996 +120 6 NULL NULL +121 6 NULL 2.1999999999999997 +122 6 2.2 11.15 +123 6 20.1 3.966666666666667 +124 6 -10.4 4.85 +125 6 NULL -10.400000000000002 +126 6 NULL NULL +127 6 NULL NULL +drop table t1; |