github.com/matrixorigin/matrixone@v0.7.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,19), 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