github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_std.result (about) 1 SELECT STD(null); 2 STD(null) 3 null 4 SELECT STDDEV_POP(null); 5 STDDEV_POP(null) 6 null 7 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)); 8 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"); 9 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"); 10 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"); 11 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"); 12 select std(a) from t1; 13 std(a) 14 30.310889132455355 15 select std(b) from t1; 16 std(b) 17 30.31088913245535 18 select std(c) from t1; 19 std(c) 20 8.227241335952167 21 select std(d) from t1; 22 std(d) 23 14.7542366796795 24 select std(e) from t1; 25 std(e) 26 21.21762239271875 27 select std(f) from t1; 28 std(f) 29 20.512191496766015 30 select std(g) from t1; 31 std(g) 32 180.28380047095192 33 select std(h) from t1; 34 std(h) 35 47.47713870906711 36 select std(i) from t1; 37 invalid argument aggregate function std, bad value [DATE] 38 select std(k) from t1; 39 invalid argument aggregate function std, bad value [DATETIME] 40 select std(l) from t1; 41 invalid argument aggregate function std, bad value [TIMESTAMP] 42 select std(m) from t1; 43 invalid argument aggregate function std, bad value [CHAR] 44 select std(n) from t1; 45 invalid argument aggregate function std, bad value [VARCHAR] 46 select STDDEV_POP(a) from t1; 47 STDDEV_POP(a) 48 30.310889132455355 49 select STDDEV_POP(b) from t1; 50 STDDEV_POP(b) 51 30.31088913245535 52 select STDDEV_POP(c) from t1; 53 STDDEV_POP(c) 54 8.227241335952167 55 select STDDEV_POP(d) from t1; 56 STDDEV_POP(d) 57 14.7542366796795 58 select STDDEV_POP(e) from t1; 59 STDDEV_POP(e) 60 21.21762239271875 61 select STDDEV_POP(f) from t1; 62 STDDEV_POP(f) 63 20.512191496766015 64 select STDDEV_POP(g) from t1; 65 STDDEV_POP(g) 66 180.28380047095192 67 select STDDEV_POP(h) from t1; 68 STDDEV_POP(h) 69 47.47713870906711 70 select STDDEV_POP(i) from t1; 71 invalid argument aggregate function stddev_pop, bad value [DATE] 72 select STDDEV_POP(k) from t1; 73 invalid argument aggregate function stddev_pop, bad value [DATETIME] 74 select STDDEV_POP(l) from t1; 75 invalid argument aggregate function stddev_pop, bad value [TIMESTAMP] 76 select STDDEV_POP(m) from t1; 77 invalid argument aggregate function stddev_pop, bad value [CHAR] 78 select STDDEV_POP(n) from t1; 79 invalid argument aggregate function stddev_pop, bad value [VARCHAR] 80 drop table t1; 81 select STDDEV_POP(99999999999999999.99999); 82 STDDEV_POP(99999999999999999.99999) 83 0.0 84 select STDDEV_POP(999999999999999933193939.99999); 85 STDDEV_POP(999999999999999933193939.99999) 86 0.0 87 select STDDEV_POP(9999999999999999999999999999999999.9999999999999); 88 STDDEV_POP(9999999999999999999999999999999999.9999999999999) 89 0.0 90 select STDDEV_POP(-99999999999999999.99999); 91 STDDEV_POP(-99999999999999999.99999) 92 0.0 93 select STDDEV_POP(-999999999999999933193939.99999); 94 STDDEV_POP(-999999999999999933193939.99999) 95 0.0 96 select STDDEV_POP(-9999999999999999999999999999999999.9999999999999); 97 STDDEV_POP(-9999999999999999999999999999999999.9999999999999) 98 0.0 99 create table t1(a bigint); 100 select STDDEV_POP(a) from t1; 101 STDDEV_POP(a) 102 null 103 insert into t1 values(null),(null),(null),(null); 104 select STDDEV_POP(a) from t1; 105 STDDEV_POP(a) 106 null 107 insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515); 108 select STDDEV_POP(a) from t1; 109 STDDEV_POP(a) 110 5.773458455090072E13 111 drop table t1; 112 create table t1 ( a int not null default 1, big bigint ); 113 insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515); 114 select * from t1; 115 a big 116 1 -1 117 1 1234567890167 118 1 92233720368547 119 1 18446744073709515 120 select distinct STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1; 121 STDDEV_POP(big) max(big) STDDEV_POP(big)-1 122 7.974271234688913E15 18446744073709515 7.974271234688912E15 123 select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1 group by a; 124 STDDEV_POP(big) max(big) STDDEV_POP(big)-1 125 7.974271234688913E15 18446744073709515 7.974271234688912E15 126 insert into t1 (big) values (184467440737615); 127 select * from t1; 128 a big 129 1 -1 130 1 1234567890167 131 1 92233720368547 132 1 18446744073709515 133 1 184467440737615 134 select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1; 135 STDDEV_POP(big) max(big) STDDEV_POP(big)-1 136 7.351219993962748E15 18446744073709515 7.351219993962747E15 137 select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1 group by a; 138 STDDEV_POP(big) max(big) STDDEV_POP(big)-1 139 7.351219993962748E15 18446744073709515 7.351219993962747E15 140 drop table t1; 141 CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL); 142 INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50); 143 select Fld1, STDDEV_POP(Fld2) as q from t1 group by Fld1 having q is not null; 144 fld1 q 145 1 5.0 146 3 0.0 147 select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having STDDEV_POP(Fld2) is not null; 148 Fld1 STDDEV_POP(Fld2) 149 1 5.0 150 3 0.0 151 select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having avg(Fld2) is not null; 152 Fld1 STDDEV_POP(Fld2) 153 1 5.0 154 3 0.0 155 select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having variance(Fld2) is not null; 156 Fld1 STDDEV_POP(Fld2) 157 1 5.0 158 3 0.0 159 drop table t1; 160 SELECT STDDEV_POP(1)<STDDEV_POP(2); 161 STDDEV_POP(1)<STDDEV_POP(2) 162 false 163 CREATE TABLE t1(i INT); 164 INSERT INTO t1 VALUES (NULL),(1); 165 SELECT STDDEV_POP(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ; 166 splus0 plain 167 null null 168 0.0 1 169 DROP TABLE t1;