github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/distinct/distinct.sql (about)

     1  
     2  -- test keyword distinct
     3  drop table if exists t1;
     4  
     5  create table t1(
     6  a int,
     7  b varchar(10)
     8  );
     9  
    10  insert into t1 values (111, 'a'),(110, 'a'),(100, 'a'),(000, 'b'),(001, 'b'),(011,'b');
    11  
    12  select distinct b from t1;
    13  select distinct b, a from t1;
    14  
    15  select count(distinct a) from t1;
    16  select sum(distinct a) from t1;
    17  select avg(distinct a) from t1;
    18  select min(distinct a) from t1;
    19  select max(distinct a) from t1;
    20  
    21  drop table t1;
    22  
    23  -- test values is NULL or empty
    24  drop table if exists t2;
    25  create table t2(a int, b varchar(10));
    26  
    27  insert into t2 values (1, 'a');
    28  insert into t2 values (2, NULL);
    29  insert into t2 values (NULL, 'b');
    30  insert into t2 values (NULL, '');
    31  insert into t2 values (3, '');
    32  insert into t2 values (NULL, NULL);
    33  
    34  select distinct a from t2;
    35  select distinct b from t2;
    36  select distinct a, b from t2;
    37  
    38  drop table t2;
    39  
    40  
    41  drop table if exists t3;
    42  create table t3 (i int, j int);
    43  insert into t3 values (1,1), (1,2), (2,3), (2,4);
    44  select i, count(distinct j) from t3 group by i;
    45  
    46  -- @bvt:issue#4797
    47  select i+0.0 as i2, count(distinct j) from t3 group by i2;
    48  -- @bvt:issue
    49  
    50  select i+0.0 as i2, count(distinct j) from t3 group by i;
    51  
    52  drop table t3;
    53  
    54  
    55  drop table if exists t4;
    56  CREATE TABLE t4 (a INT, b INT);
    57  INSERT INTO t4 VALUES (1,1),(1,2),(2,3);
    58  
    59  -- echo error
    60  SELECT (SELECT COUNT(DISTINCT t4.b)) FROM t4 GROUP BY t4.a;
    61  
    62  SELECT (SELECT COUNT(DISTINCT 12)) FROM t4 GROUP BY t4.a;
    63  
    64  drop table t4;
    65  
    66  
    67  drop table if exists t5;
    68  create table t5 (ff double);
    69  
    70  insert into t5 values (2.2);
    71  select cast(sum(distinct ff) as decimal(5,2)) from t5;
    72  select cast(sum(distinct ff) as signed) from t5;
    73  select cast(variance(ff) as decimal(10,3)) from t5;
    74  select cast(min(ff) as decimal(5,2)) from t5;
    75  
    76  drop table t5;
    77  
    78  
    79  drop table if exists t6;
    80  create table t6 (df decimal(5,1));
    81  
    82  insert into t6 values(1.1);
    83  insert into t6 values(2.2);
    84  select cast(sum(distinct df) as signed) from t6;
    85  select cast(min(df) as signed) from t6;
    86  select 1e8 * sum(distinct df) from t6;
    87  select 1e8 * min(df) from t6;
    88  
    89  drop table t6;
    90  
    91  
    92  -- test space key
    93  drop table if exists t7;
    94  CREATE TABLE t7 (a VARCHAR(400));
    95  
    96  INSERT INTO t7 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
    97                            ("B"), ("b"), ("b "), ("b   ");
    98  
    99  select * from t7;
   100  SELECT COUNT(DISTINCT a) FROM t7;
   101  
   102  DROP TABLE t7;
   103  
   104  
   105  drop table if exists t8;
   106  CREATE TABLE t8 (a INT, b INT);
   107  
   108  INSERT INTO t8 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
   109  INSERT INTO t8 SELECT a, b+8       FROM t8;
   110  INSERT INTO t8 SELECT a, b+16      FROM t8;
   111  INSERT INTO t8 SELECT a, b+32      FROM t8;
   112  INSERT INTO t8 SELECT a, b+64      FROM t8;
   113  INSERT INTO t8 SELECT a, b+128     FROM t8;
   114  INSERT INTO t8 SELECT a, b+256     FROM t8;
   115  INSERT INTO t8 SELECT a, b+512     FROM t8;
   116  INSERT INTO t8 SELECT a, b+1024    FROM t8;
   117  INSERT INTO t8 SELECT a, b+2048    FROM t8;
   118  INSERT INTO t8 SELECT a, b+4096    FROM t8;
   119  INSERT INTO t8 SELECT a, b+8192    FROM t8;
   120  INSERT INTO t8 SELECT a, b+16384   FROM t8;
   121  INSERT INTO t8 SELECT a, b+32768   FROM t8;
   122  
   123  -- echo error mag
   124  SELECT a,COUNT(DISTINCT b) AS cnt FROM t8 GROUP BY a HAVING cnt > 50;
   125  SELECT a,SUM(DISTINCT b) AS sumation FROM t8 GROUP BY a HAVING sumation > 50;
   126  SELECT a,AVG(DISTINCT b) AS average FROM t8 GROUP BY a HAVING average > 50;
   127  
   128  DROP TABLE t8;
   129  
   130  drop table if exists t9;
   131  CREATE TABLE t9 (a INT);
   132  INSERT INTO t9 values (),(),();
   133  
   134  select distinct * from t9;
   135  
   136  drop table t9;
   137  
   138  
   139  drop table if exists t10;
   140  CREATE TABLE t10 (col_int_nokey int(11));
   141  
   142  INSERT INTO t10 VALUES (7),(8),(NULL);
   143  SELECT AVG(DISTINCT col_int_nokey) FROM t10;
   144  SELECT AVG(DISTINCT outr.col_int_nokey) FROM t10 AS outr LEFT JOIN t10 AS outr2 ON
   145  outr.col_int_nokey = outr2.col_int_nokey;
   146  
   147  DROP TABLE t10;
   148  
   149  
   150  drop table if exists t11;
   151  CREATE TABLE t11(c1 CHAR(30));
   152  INSERT INTO t11 VALUES('111'),('222');
   153  
   154  SELECT DISTINCT substr(c1, 1, 2147483647) FROM t11;
   155  SELECT DISTINCT substr(c1, 1, 2147483648) FROM t11;
   156  SELECT DISTINCT substr(c1, -1, 2147483648) FROM t11;
   157  SELECT DISTINCT substr(c1, -2147483647, 2147483648) FROM t11;
   158  SELECT DISTINCT substr(c1, 9223372036854775807, 23) FROM t11;
   159  
   160  DROP TABLE t11;
   161  
   162  
   163  drop table if exists t12;
   164  drop view if exists v1;
   165  create table t12(pk int primary key);
   166  create view v1 as select pk from t12 where pk < 20;
   167  
   168  insert into t12 values (1), (2), (3), (4);
   169  select distinct pk from v1;
   170  
   171  insert into t12 values (5), (6), (7);
   172  select distinct pk from v1;
   173  
   174  drop view v1;
   175  drop table t12;
   176  
   177  
   178  SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2);
   179  
   180  select count(*);
   181  
   182  select COUNT(12), COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2);
   183  
   184  drop table if exists t13;
   185  CREATE TABLE t13(product VARCHAR(32),country_id INTEGER NOT NULL,year INTEGER,profit INTEGER);
   186  INSERT INTO t13 VALUES ( 'Computer', 2,2000, 1200),
   187  ( 'TV', 1, 1999, 150),
   188  ( 'Calculator', 1, 1999,50),
   189  ( 'Computer', 1, 1999,1500),
   190  ( 'Computer', 1, 2000,1500),
   191  ( 'TV', 1, 2000, 150),
   192  ( 'TV', 2, 2000, 100),
   193  ( 'TV', 2, 2000, 100),
   194  ( 'Calculator', 1, 2000,75),
   195  ( 'Calculator', 2, 2000,75),
   196  ( 'TV', 1, 1999, 100),
   197  ( 'Computer', 1, 1999,1200),
   198  ( 'Computer', 2, 2000,1500),
   199  ( 'Calculator', 2, 2000,75),
   200  ( 'Phone', 3, 2003,10);
   201  
   202  SELECT product, country_id, COUNT(*), COUNT(distinct year) FROM t13 GROUP BY product, country_id order by product;
   203  
   204  drop table t13;