github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_aggr_sum.test (about)

     1  #ORDER BY
     2  drop table if exists t;
     3  CREATE TABLE t (i1 INT,
     4  d1 DOUBLE,
     5  e2 DECIMAL(5,2));
     6  INSERT INTO t VALUES ( 6,    6.0,  10.0/3),
     7  ( null, 9.0,  10.0/3),
     8  ( 1,    null, 10.0/3),
     9  ( 2,    2.0,  null  );
    10  SELECT coalesce(e2,i1) nullif_c,
    11  IF(e2 IS NULL,i1,e2) if_c,
    12  SUM(d1) FROM t
    13  GROUP BY e2,i1 ORDER BY nullif_c, SUM(d1);
    14  DROP TABLE t;
    15  
    16  #子查询
    17  CREATE TABLE t1(a INT);
    18  CREATE TABLE t2(a INT);
    19  INSERT INTO t1 VALUES (1),(2),(3),(4);
    20  INSERT INTO t2 VALUES (1),(2);
    21  SELECT t1.a, t2.a FROM t1 JOIN t2
    22  ON t1.a+t2.a = (SELECT COUNT(*) FROM t1);
    23  SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt
    24  FROM t2;
    25  drop table t1;
    26  drop table t2;
    27  
    28  #HAVING
    29  CREATE TABLE t1 (name char(1) default NULL, val int default NULL);
    30  INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
    31  SELECT s.name, AVG(s.val) AS med FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
    32  drop table t1;
    33  
    34  #DISTINCT
    35  create table t1 (a integer, b integer);
    36  insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
    37  select sum(b) from t1 group by a;
    38  select sum(b) from (select a,b from t1) y group by a;
    39  drop table t1;
    40  
    41  #WITH
    42  CREATE TABLE t1
    43  (a INTEGER,
    44  b INTEGER,
    45  c INTEGER
    46  );
    47  INSERT INTO t1 VALUES
    48  (1, 1, 10), (1, 2, 20), (1, 3, 30), (2, 1, 40), (2, 2, 50), (2, 3, 60);
    49  CREATE TABLE t2
    50  (a INTEGER,
    51  d INTEGER,
    52  e INTEGER
    53  );
    54  INSERT INTO t2 VALUES
    55  (1, 6, 60), (2, 6, 60), (3, 6, 60);
    56  WITH
    57  cte AS
    58  (SELECT SUM(c) AS c, SUM(b) AS b, a
    59  FROM t1
    60  GROUP BY a)
    61  SELECT t2.a, (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b)
    62  FROM t2 LEFT JOIN cte AS cte1 ON t2.a = cte1.a
    63  LEFT JOIN t2 AS tx ON tx.e = cte1.c;
    64  drop table t1;
    65  drop table t2;
    66  
    67  #IF
    68  create table t1 (num  double(12,2));
    69  insert into t1 values (144.54);
    70  select sum(if(num is null,0.00,num)) from t1;
    71  drop table t1;
    72  
    73  #CASE WHEN
    74  CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
    75  INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1);
    76  SELECT CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END FROM t1;
    77  SELECT CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1;
    78  SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
    79  DROP TABLE t1;
    80  
    81  #DATA TYPE
    82  CREATE TABLE t1(a varchar(255), b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL);
    83  INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1);
    84  INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2);
    85  #SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a);
    86  #SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b);
    87  #SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c);
    88  #SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d);
    89  #SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e);
    90  SELECT  SUM(  a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a);
    91  SELECT  SUM(  b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b);
    92  SELECT  SUM(  c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c);
    93  
    94  SELECT  SUM(  d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d);
    95  
    96  SELECT  SUM(  e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e);
    97  DROP TABLE t1;
    98  
    99  #NULL
   100  SELECT SUM(NULL);
   101  
   102  #比较操作
   103  select sum(1)>sum(0);
   104  
   105  #EXTRME VALUE
   106  create table t1(a bigint, b float, c double, d double, e varchar(1000));
   107  #insert into t1 values(21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000));
   108  #insert into t1 values(21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000));
   109  insert into t1 select 21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000);
   110  insert into t1 select 21474836471, 12412490231412.124124124124, 124141231249124124.1241241243124123, 12421512141241241241241241849912840129402.1241124124241241, space(1000);
   111  select sum(a), sum(b),sum(c),sum(d),sum(e) from t1;
   112  select sum(a), sum(b),sum(c),sum(d) from t1;
   113  drop table t1;
   114  
   115  
   116  
   117  create table t1 (num float(5,2),user char(20));
   118  insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa');
   119  insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa');
   120  insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa');
   121  select sum(num) from t1;
   122  select sum(num) from t1 group by user;
   123  drop table t1;
   124  
   125  
   126  create table t2 (ff double);
   127  insert into t2 values (2.2);
   128  select cast(sum( ff) as decimal(5,2)) from t2;
   129  select cast(sum( ff) as signed) from t2;
   130  select cast(variance(ff) as decimal(10,3)) from t2;
   131  select cast(min(ff) as decimal(5,2)) from t2;
   132  create table t1 (df decimal(5,1));
   133  insert into t1 values(1.1);
   134  insert into t1 values(2.2);
   135  
   136  select cast(sum( df) as signed) from t1;
   137  select cast(min(df) as signed) from t1;
   138  select 1e8 * sum( df) from t1;
   139  select 1e8 * min(df) from t1;
   140  
   141  create table t3 (ifl int);
   142  insert into t3 values(1), (2);
   143  select cast(min(ifl) as decimal(5,2)) from t3;
   144  drop table t1;
   145  drop table t2;
   146  drop table t3;
   147  
   148  create table t1 (grp int, a bigint unsigned, c char(10) not null);
   149  insert into t1 values (1,1,"a");
   150  insert into t1 values (2,2,"b");
   151  insert into t1 values (2,3,"c");
   152  insert into t1 values (3,4,"E");
   153  insert into t1 values (3,5,"C");
   154  insert into t1 values (3,6,"D");
   155  select sum(distinct a),sum(distinct grp) from t1;
   156  insert into t1 values (null,null,'');
   157  select sum(distinct a),sum(distinct grp) from t1;
   158  drop table t1;