github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_avg.test (about) 1 #null 2 SELECT avg(null); 3 4 #DATATYPE 5 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 6 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 7 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 8 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 9 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 10 select avg(a) from t1; 11 select avg(b) from t1; 12 select avg(c) from t1; 13 select avg(d) from t1; 14 select avg(e) from t1; 15 select avg(f) from t1; 16 select avg(g) from t1; 17 select avg(h) from t1; 18 19 select avg(a) from t1 limit 1; 20 select avg(b) from t1 limit 1; 21 select avg(c) from t1 limit 1; 22 select avg(d) from t1 limit 1; 23 select avg(e) from t1 limit 1; 24 select avg(f) from t1 limit 1; 25 select avg(g) from t1 limit 1; 26 select avg(h) from t1 limit 1; 27 28 drop table t1; 29 30 #0.5暂不支持time类型 31 #create table t1(a time) 32 #insert into t1 values("10:03:12"); 33 #insert into t1 values("10:03:12"); 34 #insert into t1 values("10:03:12"); 35 #insert into t1 values("10:03:12"); 36 #select avg(a) from t1; 37 #drop table t1; 38 39 40 #EXTREME VALUE 41 create table t1(a bigint); 42 select avg(a) from t1; 43 insert into t1 values(null),(null),(null),(null); 44 select avg(a) from t1; 45 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 46 select avg(a) from t1 order by avg(a); 47 drop table t1; 48 49 50 #HAVING, 比较运算 51 CREATE TABLE t1 ( 52 bug_id bigint(9) NOT NULL, 53 groupset bigint(20) DEFAULT 0 NOT NULL, 54 assigned_to bigint(9) DEFAULT 0 NOT NULL, 55 bug_file_loc varchar(255) 56 ); 57 CREATE TABLE t2 ( 58 value varchar(255), 59 program varchar(64), 60 initialowner varchar(255) NOT NULL, 61 initialqacontact varchar(255) NOT NULL, 62 description varchar(255) NOT NULL, 63 id int not null 64 ); 65 INSERT INTO t1 VALUES (1,0,0,'TestComponent'); 66 INSERT INTO t1 VALUES (2,0,0,'BBBBBBBBBBBBB'); 67 INSERT INTO t1 VALUES (3,0,0,'TestComponent2'); 68 INSERT INTO t1 VALUES (4,0,0,'TestComponent2'); 69 INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','','',2); 70 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','','',2); 71 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','','',3); 72 INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','','',3); 73 select any_value(value),any_value(description),avg(bug_id) from t2 left join t1 on t2.value=t1.bug_file_loc group by value having avg(bug_id)>0; 74 SELECT avg(t1.bug_id) >= avg(t2.id) from t1 join t2 on t1.bug_file_loc = t2.value; 75 drop table t1; 76 drop table t2; 77 78 #IF 79 create table t1 (a int); 80 insert t1 values (1),(2); 81 select if(1>2,any_value(a),avg(a)) from t1; 82 drop table t1; 83 84 #CASE WHEN 85 CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER); 86 INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1); 87 SELECT CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END FROM t1; 88 SELECT CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1; 89 SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; 90 DROP TABLE t1; 91 92 93 #HAVING, IN 94 CREATE TABLE t1(a varchar(255), b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); 95 INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); 96 INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); 97 #SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); 98 #SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); 99 #SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); 100 #SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); 101 #SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); 102 SELECT DISTINCT SUM( a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); 103 SELECT DISTINCT SUM( b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); 104 SELECT DISTINCT SUM( c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); 105 SELECT DISTINCT SUM( d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); 106 SELECT DISTINCT SUM( e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); 107 DROP TABLE t1; 108 109 #子查询 110 CREATE TABLE t1 ( 111 a INT, 112 b INT, 113 c INT 114 ); 115 INSERT INTO t1 VALUES 116 ( 1, 1, 1 ), 117 ( 1, 2, 2 ), 118 ( 1, 3, 3 ), 119 ( 1, 4, 6 ), 120 ( 1, 5, 5 ), 121 ( 1, 9, 13 ), 122 ( 2, 1, 6 ), 123 ( 2, 2, 7 ), 124 ( 2, 3, 8 ); 125 SELECT a, AVG(t1.b), 126 (SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c, 127 (SELECT t12.c FROM t1 t12 WHERE t12.a = t1.a AND t12.b = AVG(t1.b)) AS t12c 128 FROM t1 GROUP BY a; 129 DROP TABLE t1; 130 131 132 CREATE TABLE t1 (id int(11),value1 float(10,2)); 133 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 134 CREATE TABLE t2 (id int(11),name char(20)); 135 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 136 select id, avg(value1), std(value1), variance(value1) from t1 group by id; 137 select any_value(name), avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id; 138 drop table t1; 139 drop table t2; 140 141 create table t1 (id int not null); 142 create table t2 (id int not null,rating int null); 143 insert into t1 values(1),(2),(3); 144 insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL); 145 select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id; 146 drop table t1; 147 drop table t2; 148 149 150 CREATE TABLE t1 (a int, b int); 151 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; 152 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 153 insert into t1 values (1,null); 154 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 155 insert into t1 values (1,null); 156 insert into t1 values (2,null); 157 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 158 insert into t1 values (2,1); 159 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 160 insert into t1 values (3,1); 161 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 162 drop table t1; 163 164 create table t1 (a int); 165 select avg(2) from t1; 166 drop table t1; 167 168 169 170 CREATE TABLE t1 (a INT, b INT); 171 INSERT INTO t1 VALUES (1,1),(1,2),(2,3); 172 SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; 173 SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; 174 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12), COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2); 175 DROP TABLE t1; 176 177 178 179 CREATE TABLE t1 (col1 decimal(16,12)); 180 INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002); 181 insert into t1 select * from t1; 182 select col1,count(col1),sum(col1),avg(col1) from t1 group by col1; 183 DROP TABLE t1; 184 185 186 187 CREATE TABLE t1 (a INT, b INT); 188 INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 189 INSERT INTO t1 SELECT a, b+8 FROM t1; 190 INSERT INTO t1 SELECT a, b+16 FROM t1; 191 INSERT INTO t1 SELECT a, b+32 FROM t1; 192 INSERT INTO t1 SELECT a, b+64 FROM t1; 193 INSERT INTO t1 SELECT a, b+128 FROM t1; 194 INSERT INTO t1 SELECT a, b+256 FROM t1; 195 INSERT INTO t1 SELECT a, b+512 FROM t1; 196 INSERT INTO t1 SELECT a, b+1024 FROM t1; 197 INSERT INTO t1 SELECT a, b+2048 FROM t1; 198 INSERT INTO t1 SELECT a, b+4096 FROM t1; 199 INSERT INTO t1 SELECT a, b+8192 FROM t1; 200 INSERT INTO t1 SELECT a, b+16384 FROM t1; 201 202 INSERT INTO t1 SELECT a, b+32768 FROM t1; 203 SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; 204 #SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 205 #SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; 206 SELECT DISTINCT a, SUM( b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 207 SELECT DISTINCT a, AVG( b) AS average FROM t1 GROUP BY a HAVING average > 50; 208 SELECT a,COUNT(DISTINCT b) FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 50; 209 SELECT DISTINCT a, SUM( b) FROM t1 GROUP BY a HAVING SUM( b) > 50; 210 SELECT DISTINCT a, AVG( b) FROM t1 GROUP BY a HAVING AVG( b) > 50; 211 212 DROP TABLE t1; 213 214 215 CREATE TABLE t1(a DOUBLE); 216 INSERT INTO t1 VALUES (10), (20); 217 SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1; 218 DROP TABLE t1; 219 220 221 create table t1 (grp int, a bigint unsigned, c char(10) not null); 222 insert into t1 values (1,1,"a"); 223 insert into t1 values (2,2,"b"); 224 insert into t1 values (2,3,"c"); 225 insert into t1 values (3,4,"E"); 226 insert into t1 values (3,5,"C"); 227 insert into t1 values (3,6,"D"); 228 -- @bvt:issue#3344 229 select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; 230 -- @bvt:issue 231 select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; 232 select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; 233 create table t2 (grp int, a bigint unsigned, c char(10)); 234 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; 235 select * from t2; 236 drop table t1; 237 drop table t2; 238 239 240 #GROUP BY ERROR 241 CREATE TABLE t1(a DOUBLE); 242 INSERT INTO t1 VALUES (10), (20); 243 SELECT AVG(min(a)) FROM t1 group by a; 244 DROP TABLE t1; 245 246 247 #group by error 248 CREATE TABLE t1 (id int(11),value1 float(10,2)); 249 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); 250 CREATE TABLE t2 (id int(11),name char(20)); 251 INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); 252 select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id; 253 drop table t1; 254 drop table t2; 255 256 create table t1 (grp int, a bigint unsigned, c char(10) not null); 257 insert into t1 values (1,1,"a"); 258 insert into t1 values (2,2,"b"); 259 insert into t1 values (2,3,"c"); 260 insert into t1 values (3,4,"E"); 261 insert into t1 values (3,5,"C"); 262 insert into t1 values (3,6,"D"); 263 select avg(distinct a),avg(distinct grp) from t1; 264 insert into t1 values (null,null,''); 265 select avg(distinct a),avg(distinct grp) from t1; 266 drop table t1; 267 268 create table d1 (i1 int); 269 insert into d1 select * from generate_series(0, 999) g; 270 select avg(distinct i1) from d1; 271 drop table d1;