github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_var_pop.test (about) 1 #NULL 2 SELECT var_pop(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 var_pop(a) from t1; 11 select var_pop(b) from t1; 12 select var_pop(c) from t1; 13 select var_pop(d) from t1; 14 select var_pop(e) from t1; 15 select var_pop(f) from t1; 16 select var_pop(g) from t1; 17 18 select var_pop(h) from t1; 19 select var_pop(i) from t1; 20 select var_pop(k) from t1; 21 select var_pop(l) from t1; 22 select var_pop(m) from t1; 23 select var_pop(n) from t1; 24 drop table t1; 25 26 #EXTREME VALUE, DISTINCT 27 select var_pop(99999999999999999.99999); 28 select var_pop(999999999999999933193939.99999); 29 select var_pop(9999999999999999999999999999999999.9999999999999); 30 select var_pop(-99999999999999999.99999); 31 select var_pop(-999999999999999933193939.99999); 32 select var_pop(-9999999999999999999999999999999999.9999999999999); 33 create table t1(a bigint); 34 select var_pop(a) from t1; 35 insert into t1 values(null),(null),(null),(null); 36 select var_pop(a) from t1; 37 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 38 select var_pop(a) from t1; 39 drop table t1; 40 create table t1 ( a int not null default 1, big bigint ); 41 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 42 select * from t1; 43 select distinct var_pop(big),max(big),var_pop(big)-1 from t1; 44 select var_pop(big),max(big),var_pop(big)-1 from t1 group by a; 45 insert into t1 (big) values (184467440737615); 46 select * from t1; 47 select var_pop(big),max(big),var_pop(big)-1 from t1; 48 select var_pop(big),max(big),var_pop(big)-1 from t1 group by a; 49 drop table t1; 50 51 #HAVING, DISTINCT#HAVING,DISTINCT 52 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 53 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 54 select Fld1, var_pop(Fld2) as q from t1 group by Fld1 having q is not null; 55 select Fld1, var_pop(Fld2) from t1 group by Fld1 having var_pop(Fld2) is not null; 56 select Fld1, var_pop(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; 57 select Fld1, var_pop(Fld2) from t1 group by Fld1 having var_pop(Fld2) is not null; 58 drop table t1; 59 60 #比较操作 61 SELECT var_pop(1)<var_pop(2); 62 63 #DISTINCT, 算式操作 64 CREATE TABLE t1(i INT); 65 INSERT INTO t1 VALUES (NULL),(1); 66 SELECT var_pop(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ; 67 DROP TABLE t1; 68 69 drop database if exists test; 70 create database test; 71 use test; 72 73 -- decimal 128 74 drop table if exists decimal01; 75 create table decimal01(col1 decimal(10,1)); 76 insert into decimal01 values(1234124.2134324321); 77 insert into decimal01 values(23413432.32423); 78 insert into decimal01 values(0); 79 insert into decimal01 values(-13421.34234); 80 select var_pop(col1) from decimal01; 81 drop table decimal01; 82 83 drop table if exists decimal02; 84 create table decimal02(col1 decimal(38,0)); 85 insert into decimal02 values(12312312312314565789874569874512456879); 86 insert into decimal02 values(99999999999999999999999999999999999999); 87 insert into decimal02 values(10000000000000000000000000000000000000); 88 insert into decimal02 values(32482438247328742831193201993219039203); 89 insert into decimal02 values(99999999999999999999999999999999999999.73289342); 90 insert into decimal02 values(-298432432433242412313123321231321313); 91 insert into decimal02 values(-34243214324324342321313321321342342343.123143); 92 insert into decimal02 values(-0.12813218391321939921391929391293912); 93 select * from decimal02; 94 select var_pop(col1) from decimal02; 95 drop table decimal02; 96 97 drop table if exists test02; 98 create table test02(col1 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 var_pop(col1) from test02; 106 drop table test02; 107 108 drop table if exists test03; 109 create table test03(a decimal(38,2)); 110 insert into test03 values(9999999999999999999999999999999.1); 111 insert into test03 values(-0.00001); 112 insert into test03 values(NULL); 113 select var_pop(a) from test03; 114 drop table test03; 115 116 -- decimal 64 117 drop table if exists test04; 118 create table test04(a decimal(38,20)); 119 insert into test04 values(123123242399999900.22231134568909999999); 120 insert into test04 values(999999999999999999.11111111111112222222); 121 insert into test04 values(100000000000000000.00000000000000000001); 122 insert into test04 values(-999999999999999999.9); 123 insert into test04 values(-0.00000000000000000001); 124 select * from test04; 125 select var_pop(a) from test04; 126 drop table test04; 127 128 drop table if exists test05; 129 create table test05(a decimal(20,10)); 130 insert into test05 values(-9999999999.192012); 131 insert into test05 values(9999999999.9102938129); 132 insert into test05 values(7382184932.3892039843); 133 insert into test05 values(0.3029302394); 134 select * from test05; 135 select var_pop(a) from test05; 136 drop table test05; 137 138 drop table if exists test06; 139 create table test06(a decimal(20,10)); 140 insert into test06 values(-9999999999.192012); 141 insert into test06 values(9999999999.9102938129); 142 insert into test06 values(NULL); 143 insert into test06 values(0.3029302394); 144 select * from test06; 145 select var_pop(a) from test06; 146 drop table test06; 147 148 drop database test;