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

     1  select median(null);
     2  
     3  drop table if exists t1;
     4  create table t1 (a int,b int);
     5  insert into t1 values (1,null);
     6  select median(b) from t1;
     7  insert into t1 values (1,1);
     8  select median(b) from t1;
     9  insert into t1 values (1,2);
    10  select median(b) from t1;
    11  select median(b) from t1 group by a order by a;
    12  insert into t1 values (2,1),(2,2),(2,3),(2,4);
    13  select median(b) from t1 group by a order by a;
    14  insert into t1 values (2,null);
    15  select median(b) from t1 group by a order by a;
    16  
    17  drop table if exists t1;
    18  create table t1 (a int,b float,c double);
    19  insert into t1 values (1,null,null);
    20  select median(b),median(c) from t1;
    21  insert into t1 values (1,1.1,1.1);
    22  select median(b),median(c) from t1;
    23  insert into t1 values (1,2.2,2.2);
    24  select median(b),median(c) from t1;
    25  select median(b),median(c) from t1 group by a order by a;
    26  insert into t1 values (2,1.1,1.1),(2,2.2,2.2),(2,3.3,3.3),(2,4.4,4.4);
    27  select median(b),median(c) from t1 group by a order by a;
    28  insert into t1 values (2,null,null);
    29  select median(b),median(c) from t1 group by a order by a;
    30  
    31  drop table if exists t1;
    32  create table t1 (a int,b decimal(10,2),c decimal(34,10));
    33  insert into t1 values (1,null,null);
    34  select median(b),median(c) from t1;
    35  insert into t1 values (1,'1.1','1.1');
    36  select median(b),median(c) from t1;
    37  insert into t1 values (1,'2.2','2.2');
    38  select median(b),median(c) from t1;
    39  select median(b),median(c) from t1 group by a order by a;
    40  insert into t1 values (2,'1.1','1.1'),('2','2.2','2.2'),('2','3.3','3.3'),('2','4.4','4.4');
    41  select median(b),median(c) from t1 group by a order by a;
    42  insert into t1 values (2,null,null);
    43  select median(b),median(c) from t1 group by a order by a;
    44  
    45  select median(distinct a) from t1;
    46  drop table if exists t1;
    47  create table t1 (a int,b varchar(10));
    48  select median(b) from t1;
    49  
    50  -- @suit
    51  -- @case
    52  -- @test function LEFT
    53  -- @label:bvt
    54  
    55  -- median()函数是求中位数的函数。
    56  
    57  SELECT median(1);
    58  SELECT median(-6372.2);
    59  SELECT median(NULL);
    60  SELECT median(ABS(-99));
    61  SELECT median(COS(0) + 2);
    62  
    63  
    64  -- 异常输入
    65  SELECT median(1,2,3);
    66  SELECT median(fekwelwfew);
    67  SELECT median(3hewh32ioj);
    68  SELECT median("ejwjlvd23232r43f");
    69  SELECT median("4");
    70  SELECT median('');
    71  
    72  
    73  
    74  -- @suite
    75  -- @setup
    76  DROP TABLE IF EXISTS median_01;
    77  CREATE TABLE median_01(id int, d1 tinyint, d2 smallint unsigned, d3 bigint);
    78  INSERT INTO median_01 VALUES(1, -128, 65534, 5554584122);
    79  INSERT INTO median_01 VALUES(2, 0, 68, -7855122);
    80  INSERT INTO median_01 VALUES(3, 45, 0, 67432648932);
    81  INSERT INTO median_01 VALUES(4, 45, 5789, 0);
    82  INSERT INTO median_01 VALUES(5, NULL, 3782, NULL);
    83  
    84  -- 异常插入
    85  -- tinyint超出插入范围
    86  INSERT INTO median_01 VALUES(6, -129, 65534, 5554584122);
    87  
    88  -- smallint unsigned超出插入范围
    89  INSERT INTO median_01 VALUES(7, -123, 89555, 5554584122);
    90  
    91  -- bigint超出插入范围
    92  INSERT INTO median_01 VALUES(8, -62, 33, 9223372036854775808);
    93  
    94  -- int超出插入范围
    95  INSERT INTO median_01 VALUES(2147483648, -62, 33, 9223372036854775808);
    96  
    97  
    98  -- median
    99  SELECT median(d2) from median_01;
   100  SELECT median(d1),median(d2),median(d3),median(id) from median_01;
   101  SELECT median(d1) + median(d2) as he, median(d2) * median(d3) as pr from median_01 where id = 2;
   102  SELECT median(id) / 4 from median_01;
   103  SELECT median(d3) FROM median_01 WHERE id BETWEEN 1 AND 4;
   104  SELECT median(d1),median(d2),median(d3) from median_01 GROUP by d1;
   105  SELECT d1, d2 FROM median_01 group by median(d1);
   106  SELECT median(d1) FROM median_01 WHERE id = ABS(-1) + TAN(45);
   107  
   108  -- 数学函数
   109  SELECT ABS(median(d2)), FLOOR(median(id) * 3) from median_01;
   110  SELECT SIN(median(d1)), COS(median(d2)), TAN(median(d2)) FROM median_01;
   111  SELECT TAN(median(d2)), cot(median(d2) * 2), ACOS(median(d1)) FROM median_01;
   112  SELECT ATAN(median(d2)), SINH(median(id)) FROM median_01;
   113  SELECT ROUND(median(id) / 2) from median_01;
   114  SELECT CEIL(median(d1)) FROM median_01 WHERE id = 1;
   115  SELECT power(median(id),3) FROM median_01;
   116  SELECT LOG(median(id)) AS a,LN(median(id)) AS b FROM median_01;
   117  SELECT EXP(median(id)) FROM median_01;
   118  
   119  
   120  
   121  -- @suite
   122  -- @setup
   123  DROP TABLE IF EXISTS median_02;
   124  CREATE TABLE median_02(id int PRIMARY KEY, d1 FLOAT, d2 DOUBLE NOT NULL);
   125  
   126  INSERT INTO median_02 VALUES(1, 645545.11, 65534.5554584122);
   127  INSERT INTO median_02 VALUES(2, NULL, 638239.1);
   128  INSERT INTO median_02 VALUES(3, -32783, -56323298.8327382);
   129  INSERT INTO median_02 VALUES(4, 0, 389283920.1);
   130  INSERT INTO median_02 VALUES(5, 382, 0);
   131  
   132  
   133  -- 异常插入
   134  -- DOUBLE超出插入范围
   135  INSERT INTO median_02 VALUES(6, 0, -1.8976931348623157E+308);
   136  
   137  -- FLOAT超出插入范围
   138  INSERT INTO median_02 VALUES(7, 4.402823466351E+38, 5554584122);
   139  
   140  -- d2为空
   141  INSERT INTO median_02 VALUES(8, -55845.0, NULL);
   142  
   143  SELECT median(d1), median(d2) from median_02;
   144  SELECT median(d2) from median_02 group by d2;
   145  SELECT median(d2) from median_02 WHERE id BETWEEN 2 AND 4;
   146  -- 嵌套查询
   147  SELECT median(d1) from median_02 WHERE id = (SELECT id from median_02 where d2 = 65534.5554584122);
   148  
   149  -- 数学函数
   150  SELECT ACOS(median(d2)) from median_02;
   151  SELECT CEIL(median(d2)), FLOOR(median(d2)) from median_02;
   152  SELECT power(median(d2),2) from median_02;
   153  
   154  
   155  -- @suite
   156  -- @setup
   157  DROP TABLE IF EXISTS median_03;
   158  DROP TABLE IF EXISTS median_04;
   159  
   160  CREATE TABLE median_03(id int, ch smallint NOT NULL, ma bigint unsigned NOT NULL, en FLOAT, ph double,
   161                         PRIMARY KEY(id));
   162  INSERT INTO median_03 VALUES(1, 88, 99999, -99.98, 88.99);
   163  INSERT INTO median_03 VALUES(2, 65, 744515, 0, 78.789);
   164  INSERT INTO median_03 VALUES(3, 76, 21, 893293.1, NULL);
   165  INSERT INTO median_03 VALUES(4, -367, 3298, NULL, 0);
   166  INSERT INTO median_03 VALUES(5, 674, 432, 8767687.0, 0.1);
   167  
   168  CREATE TABLE median_04(id int, name VARCHAR(10), ch smallint, ma bigint, en FLOAT not NULL,
   169                         PRIMARY KEY(id));
   170  INSERT INTO median_04 VALUES(1, 'Alice', 327, 45451, 3232.1);
   171  INSERT INTO median_04 VALUES(2, 'Bob', 3728, -8889, 899);
   172  INSERT INTO median_04 VALUES(3, 'Grace', 0, NULL, 0.1);
   173  INSERT INTO median_04 VALUES(4, 'Vicky', 88, 99, 88888.0);
   174  INSERT INTO median_04 VALUES(5, 'John', 10, 23211, -78);
   175  
   176  
   177  -- @case
   178  -- @join and function test
   179  SELECT median(median_04.ch) from median_03, median_04 where median_03.id = median_04.id;
   180  SELECT median(median_03.ma) AS a, median(median_04.ma) AS b from median_03 join median_04 ON median_03.ph = median_04.en;
   181  SELECT median(median_03.ch),median(median_03.en) from median_03 WHERE id % 2 =1;
   182  SELECT median(median_03.ch) from median_04 WHERE left(name,2) = 'Al';