github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_variance.test (about) 1 #NULL 2 SELECT variance(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,10), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 6 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 7 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 8 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 9 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 10 select variance(a) from t1; 11 select variance(b) from t1; 12 select variance(c) from t1; 13 select variance(d) from t1; 14 select variance(e) from t1; 15 select variance(f) from t1; 16 select variance(g) from t1; 17 18 select variance(h) from t1; 19 select variance(i) from t1; 20 select variance(k) from t1; 21 select variance(l) from t1; 22 select variance(m) from t1; 23 select variance(n) from t1; 24 drop table t1; 25 26 #0.5暂不支持time类型 27 #create table t1(a time) 28 #insert into t1 values("10:03:12"); 29 #insert into t1 values("10:03:12"); 30 #insert into t1 values("10:03:12"); 31 #insert into t1 values("10:03:12"); 32 #select variance(a) from t1; 33 #drop table t1; 34 35 #EXTREME VALUE, DISTINCT 36 select variance(99999999999999999.99999); 37 select variance(999999999999999933193939.99999); 38 select variance(9999999999999999999999999999999999.9999999999999); 39 select variance(-99999999999999999.99999); 40 select variance(-999999999999999933193939.99999); 41 select variance(-9999999999999999999999999999999999.9999999999999); 42 create table t1(a bigint); 43 select variance(a) from t1; 44 insert into t1 values(null),(null),(null),(null); 45 select variance(a) from t1; 46 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 47 select variance(a) from t1; 48 drop table t1; 49 create table t1 ( a int not null default 1, big bigint ); 50 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 51 select * from t1; 52 select distinct variance(big),max(big),variance(big)-1 from t1; 53 select variance(big),max(big),variance(big)-1 from t1 group by a; 54 insert into t1 (big) values (184467440737615); 55 select * from t1; 56 select variance(big),max(big),variance(big)-1 from t1; 57 select variance(big),max(big),variance(big)-1 from t1 group by a; 58 drop table t1; 59 60 #HAVING, DISTINCT#HAVING,DISTINCT 61 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 62 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 63 select Fld1, variance(Fld2) as q from t1 group by Fld1 having q is not null; 64 select Fld1, variance(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; 65 select Fld1, variance(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; 66 select Fld1, variance(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; 67 drop table t1; 68 69 #比较操作 70 SELECT variance(1)<variance(2); 71 72 #DISTINCT, 算式操作 73 CREATE TABLE t1(i INT); 74 INSERT INTO t1 VALUES (NULL),(1); 75 SELECT variance(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ; 76 DROP TABLE t1; 77 78 drop database if exists test; 79 create database test; 80 use test; 81 82 -- decimal 128 83 drop table if exists test01; 84 create table test01(a decimal(38,0)); 85 insert into test01 values(12312312312314565789874569874512456879); 86 insert into test01 values(99999999999999999999999999999999999999); 87 insert into test01 values(10000000000000000000000000000000000000); 88 insert into test01 values(32482438247328742831193201993219039203); 89 insert into test01 values(99999999999999999999999999999999999999.73289342); 90 insert into test01 values(-298432432433242412313123321231321313); 91 insert into test01 values(-34243214324324342321313321321342342343.123143); 92 insert into test01 values(-0.12813218391321939921391929391293912); 93 select * from test01; 94 select variance(a) from test01; 95 drop table test01; 96 97 drop table if exists test02; 98 create table test02(a decimal(37)); 99 insert into test02 values(9999999999999999999999999999999999999.123141); 100 insert into test02 values(1122312131421321313131314356569876544.9); 101 insert into test02 values(-2121313214325334213213257654323234324.90); 102 insert into test02 values(-123.2314123412321); 103 insert into test02 values(9932129321093029302930290439029432423.5); 104 select * from test02; 105 select variance(a) from test02; 106 drop table test02; 107 108 -- with null 109 drop table if exists test03; 110 create table test03(a decimal(38,2)); 111 insert into test03 values(9999999999999999999999999999999.1); 112 insert into test03 values(-0.00001); 113 insert into test03 values(NULL); 114 select variance(a) from test03; 115 drop table test03; 116 117 -- decimal 64 118 drop table if exists test04; 119 create table test04(a decimal(38,20)); 120 insert into test04 values(123123242399999900.22231134568909999999); 121 insert into test04 values(999999999999999999.11111111111112222222); 122 insert into test04 values(100000000000000000.00000000000000000001); 123 insert into test04 values(-999999999999999999.9); 124 insert into test04 values(-0.00000000000000000001); 125 select * from test04; 126 select variance(a) from test04; 127 drop table test04; 128 129 drop table if exists test05; 130 create table test05(a decimal(20,10)); 131 insert into test05 values(-9999999999.192012); 132 insert into test05 values(9999999999.9102938129); 133 insert into test05 values(7382184932.3892039843); 134 insert into test05 values(0.3029302394); 135 select * from test05; 136 select variance(a) from test05; 137 drop table test05; 138 139 drop table if exists test06; 140 create table test06(a decimal(20,10)); 141 insert into test06 values(-9999999999.192012); 142 insert into test06 values(9999999999.9102938129); 143 insert into test06 values(NULL); 144 insert into test06 values(0.3029302394); 145 select * from test06; 146 select variance(a) from test06; 147 drop table test06; 148 149 drop database test;