github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_std.test (about)

     1  #NULL
     2  
     3  SELECT STD(null);
     4  SELECT STDDEV_POP(null);
     5  
     6  
     7  
     8  #DATATYPE
     9  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));
    10  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");
    11  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");
    12  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");
    13  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");
    14  select std(a) from t1;
    15  select std(b) from t1;
    16  select std(c) from t1;
    17  select std(d) from t1;
    18  select std(e) from t1;
    19  select std(f) from t1;
    20  select std(g) from t1;
    21  
    22  select std(h) from t1;
    23  select std(i) from t1;
    24  select std(k) from t1;
    25  select std(l) from t1;
    26  select std(m) from t1;
    27  select std(n) from t1;
    28  
    29  select STDDEV_POP(a) from t1;
    30  select STDDEV_POP(b) from t1;
    31  select STDDEV_POP(c) from t1;
    32  select STDDEV_POP(d) from t1;
    33  select STDDEV_POP(e) from t1;
    34  select STDDEV_POP(f) from t1;
    35  select STDDEV_POP(g) from t1;
    36  
    37  select STDDEV_POP(h) from t1;
    38  select STDDEV_POP(i) from t1;
    39  select STDDEV_POP(k) from t1;
    40  select STDDEV_POP(l) from t1;
    41  select STDDEV_POP(m) from t1;
    42  select STDDEV_POP(n) from t1;
    43  drop table t1;
    44  
    45  
    46  #0.5暂不支持time类型
    47  #create table t1(a time)
    48  #insert into t1 values("10:03:12");
    49  #insert into t1 values("10:03:12");
    50  #insert into t1 values("10:03:12");
    51  #insert into t1 values("10:03:12");
    52  #select STDDEV_POP(a) from t1;
    53  #drop table t1;
    54  
    55  #EXTREME VALUE, DISTINCT
    56  select STDDEV_POP(99999999999999999.99999);
    57  select STDDEV_POP(999999999999999933193939.99999);
    58  select STDDEV_POP(9999999999999999999999999999999999.9999999999999);
    59  select STDDEV_POP(-99999999999999999.99999);
    60  select STDDEV_POP(-999999999999999933193939.99999);
    61  select STDDEV_POP(-9999999999999999999999999999999999.9999999999999);
    62  create table t1(a bigint);
    63  select STDDEV_POP(a) from t1;
    64  insert into t1 values(null),(null),(null),(null);
    65  select STDDEV_POP(a) from t1;
    66  insert into t1 values(12417249128419),(124124125124151),(5124125151415),(124125152651515);
    67  select STDDEV_POP(a) from t1;
    68  drop table t1;
    69  create table t1 ( a int not null default 1, big bigint );
    70  insert into t1 (big) values (-1),(1234567890167),(92233720368547),(18446744073709515);
    71  select * from t1;
    72  select distinct STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1;
    73  select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1 group by a;
    74  insert into t1 (big) values (184467440737615);
    75  select * from t1;
    76  select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1;
    77  select STDDEV_POP(big),max(big),STDDEV_POP(big)-1 from t1 group by a;
    78  drop table t1;
    79  
    80  #HAVING, DISTINCT#HAVING,DISTINCT
    81  CREATE TABLE t1 (Fld1 int(11) default NULL,Fld2 int(11) default NULL);
    82  INSERT INTO t1 VALUES (1,10),(1,20),(2,NULL),(2,NULL),(3,50);
    83  select Fld1, STDDEV_POP(Fld2) as q from t1 group by Fld1 having q is not null;
    84  select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having STDDEV_POP(Fld2) is not null;
    85  select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having avg(Fld2) is not null;
    86  select Fld1, STDDEV_POP(Fld2) from t1 group by Fld1 having variance(Fld2) is not null;
    87  drop table t1;
    88  
    89  #比较操作
    90  SELECT STDDEV_POP(1)<STDDEV_POP(2);
    91  
    92  #DISTINCT, 算式操作
    93  CREATE TABLE t1(i INT);
    94  INSERT INTO t1 VALUES (NULL),(1);
    95  SELECT STDDEV_POP(i)+0 as splus0, i+0 as plain FROM t1 GROUP BY i ;
    96  DROP TABLE t1;