github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_aggr_count.test (about) 1 #NULL 2 SELECT COUNT(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, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 7 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 8 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 9 insert into t1 values(1, 1, 2, 4, 5, 5.5, 31.13, 14.314, "2012-03-12", "2012-03-12 10:03:12", "2012-03-12 13:03:12", "abc", "dcf"); 10 select count(a) from t1; 11 select count(b) from t1; 12 select count(c) from t1; 13 select count(d) from t1; 14 select count(e) from t1; 15 select count(f) from t1; 16 select count(g) from t1; 17 select count(h) from t1; 18 select count(i) from t1; 19 select count(k) from t1; 20 select count(l) from t1; 21 select count(m) from t1; 22 select count(n) from t1; 23 drop table t1; 24 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 count(a) from t1; 33 #drop table t1; 34 35 #EXTREME VALUE 36 create table t1(a int); 37 select count(*) from t1; 38 insert into t1 values(null),(null),(null),(null); 39 select count(*) from t1; 40 drop table t1; 41 42 43 #HAVING, 比较运算 44 CREATE TABLE t1 ( 45 bug_id bigint(9) NOT NULL, 46 groupset bigint(20) DEFAULT 0 NOT NULL, 47 assigned_to bigint(9) DEFAULT 0 NOT NULL, 48 bug_file_loc varchar(255) 49 ); 50 CREATE TABLE t2 ( 51 value varchar(255), 52 program varchar(64), 53 initialowner varchar(255) NOT NULL, 54 initialqacontact varchar(255) NOT NULL, 55 description varchar(255) NOT NULL 56 ); 57 INSERT INTO t1 VALUES (1,0,0,'TestComponent'); 58 INSERT INTO t1 VALUES (2,0,0,'BBBBBBBBBBBBB'); 59 INSERT INTO t1 VALUES (3,0,0,'TestComponent2'); 60 INSERT INTO t1 VALUES (4,0,0,'TestComponent2'); 61 INSERT INTO t2 VALUES ('TestComponent','TestProduct','id0001','',''); 62 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - conversion','AAAAA','id0001','',''); 63 INSERT INTO t2 VALUES ('BBBBBBBBBBBBB - generic','AAAAA','id0001','',''); 64 INSERT INTO t2 VALUES ('TestComponent2','TestProduct','id0001','',''); 65 select any_value(value),any_value(description),COUNT(bug_id) from t2 left join t1 on t2.value=t1.bug_file_loc group by value having COUNT(bug_id) IN (0,2); 66 SELECT COUNT(t1.bug_id) >= COUNT(t2.value) from t1 join t2 on t1.bug_file_loc = t2.value; 67 drop table t1; 68 drop table t2; 69 70 create table t1 (grp int, a bigint unsigned, c char(10) not null); 71 insert into t1 values (1,1,"a"); 72 insert into t1 values (2,2,"b"); 73 insert into t1 values (2,3,"c"); 74 insert into t1 values (3,4,"E"); 75 insert into t1 values (3,5,"C"); 76 insert into t1 values (3,6,"D"); 77 select sum(a) from t1 where a > 10; 78 select count(distinct a),count(distinct grp) from t1; 79 insert into t1 values (null,null,''); 80 select count(distinct a),count(distinct grp) from t1; 81 create table t2 (grp int, a bigint unsigned, c char(10)); 82 insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; 83 drop table t1; 84 drop table t2; 85 86 87 create table t1 (a smallint(6) primary key, c char(10), b varchar(255)); 88 INSERT INTO t1 VALUES (1,'1','1'); 89 INSERT INTO t1 VALUES (2,'2','2'); 90 INSERT INTO t1 VALUES (4,'4','4'); 91 select count(*) from t1; 92 select count(*) from t1 where a = 1; 93 select count(*) from t1 where a = 100; 94 select count(*) from t1 where a >= 10; 95 select count(a) from t1 where a = 1; 96 select count(a) from t1 where a = 100; 97 select count(a) from t1 where a >= 10; 98 99 100 101 select count(b) from t1 where b >= 2; 102 select count(b) from t1 where b >= 10; 103 select count(c) from t1 where c = 10; 104 105 drop table t1; 106 107 CREATE TABLE t1 (d DATETIME, i INT); 108 #INSERT INTO t1 VALUES (NOW(), 1); 109 #INSERT INTO t1 SELECT NOW(), 1; 110 INSERT INTO t1 SELECT "2021-10-23 10:01:01", 1; 111 SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i; 112 SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i; 113 DROP TABLE t1; 114 115 116 CREATE TABLE t1 (a int, b int); 117 select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; 118 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 119 insert into t1 values (1,null); 120 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 121 insert into t1 values (1,null); 122 insert into t1 values (2,null); 123 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 124 insert into t1 values (2,1); 125 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 126 insert into t1 values (3,1); 127 select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; 128 drop table t1; 129 130 131 132 #CREATE TABLE t1 (id int(10) unsigned NOT NULL,val enum('one','two','three') NOT NULL default 'one',PRIMARY KEY (id)); 133 #INSERT INTO t1 VALUES 134 #(1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two'); 135 #select val, count(*) from t1 group by val; 136 #drop table t1; 137 138 139 140 141 CREATE TABLE t1 (a INT, b INT); 142 INSERT INTO t1 VALUES (1,1),(1,2),(2,3); 143 SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a; 144 SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a; 145 SELECT AVG(2), BIT_AND(2), BIT_OR(2), BIT_XOR(2), COUNT(*), COUNT(12),COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2); 146 DROP TABLE t1; 147 148 149 CREATE TABLE t1 (col1 decimal(16,12)); 150 INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002); 151 insert into t1 select * from t1; 152 select col1,count(col1),sum(col1),avg(col1) from t1 group by col1; 153 DROP TABLE t1; 154 155 CREATE TABLE t1 (a VARCHAR(400)); 156 INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a "),("B"), ("b"), ("b "), ("b "); 157 SELECT COUNT(DISTINCT a) FROM t1; 158 DROP TABLE t1; 159 160 161 162 create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal); 163 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 164 select i, count(*) from bug22555 group by i; 165 select std(s1/s2) from bug22555 where i=1; 166 select std(s1/s2) from bug22555 where i=2; 167 select std(s1/s2) from bug22555 where i=3; 168 select std(s1/s2) from bug22555 where i=1 group by i; 169 select std(s1/s2) from bug22555 where i=2 group by i; 170 select std(s1/s2) from bug22555 where i=3 group by i; 171 select std(s1/s2) from bug22555 group by i order by i; 172 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 173 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 174 select i, count(*), variance(s1/s2) from bug22555 group by i order by i; 175 select i, count(*), variance(o1/o2) from bug22555 group by i order by i; 176 select i, count(*), variance(e1/e2) from bug22555 group by i order by i; 177 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 178 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 179 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 180 select i, count(*), variance(s1/s2) from bug22555 group by i order by i; 181 select i, count(*), variance(o1/o2) from bug22555 group by i order by i; 182 select i, count(*), variance(e1/e2) from bug22555 group by i order by i; 183 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 184 select i, count(*), std(o1/o2) from bug22555 group by i order by i; 185 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 186 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 187 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 188 insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76); 189 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 190 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i; 191 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 192 select std(s1/s2) from bug22555; 193 select std(o1/o2) from bug22555; 194 select std(e1/e2) from bug22555; 195 select i, count(*), std(s1/s2) from bug22555 group by i order by i; 196 select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i; 197 select i, count(*), std(e1/e2) from bug22555 group by i order by i; 198 select round(std(s1/s2), 17) from bug22555; 199 select std(o1/o2) from bug22555; 200 select round(std(e1/e2), 17) from bug22555; 201 drop table bug22555; 202 203 204 205 206 create table t1 (a decimal(20)); 207 insert into t1 values (12345678901234567890); 208 select count(a) from t1; 209 select count(distinct a) from t1; 210 drop table t1; 211 212 213 CREATE TABLE t1 (a INT, b INT); 214 INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 215 INSERT INTO t1 SELECT a, b+8 FROM t1; 216 INSERT INTO t1 SELECT a, b+16 FROM t1; 217 INSERT INTO t1 SELECT a, b+32 FROM t1; 218 INSERT INTO t1 SELECT a, b+64 FROM t1; 219 INSERT INTO t1 SELECT a, b+128 FROM t1; 220 INSERT INTO t1 SELECT a, b+256 FROM t1; 221 INSERT INTO t1 SELECT a, b+512 FROM t1; 222 INSERT INTO t1 SELECT a, b+1024 FROM t1; 223 INSERT INTO t1 SELECT a, b+2048 FROM t1; 224 INSERT INTO t1 SELECT a, b+4096 FROM t1; 225 INSERT INTO t1 SELECT a, b+8192 FROM t1; 226 INSERT INTO t1 SELECT a, b+16384 FROM t1; 227 INSERT INTO t1 SELECT a, b+32768 FROM t1; 228 #SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 229 #SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50; 230 SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50; 231 SELECT a, SUM( b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50; 232 SELECT a, AVG( b) AS average FROM t1 GROUP BY a HAVING average > 50; 233 SELECT a,COUNT(DISTINCT b) FROM t1 GROUP BY a HAVING COUNT(DISTINCT b) > 50; 234 SELECT DISTINCT a, SUM( b) FROM t1 GROUP BY a HAVING SUM( b) > 50; 235 SELECT DISTINCT a, AVG( b) FROM t1 GROUP BY a HAVING AVG( b) > 50; 236 DROP TABLE t1; 237 238 CREATE TABLE t1 (a INT); 239 INSERT INTO t1 SELECT result FROM generate_series(1,100000) g; 240 SELECT COUNT(*) FROM t1; 241 DELETE FROM t1 WHERE a>50000; 242 SELECT COUNT(*) FROM t1; 243 SELECT COUNT(a) FROM t1; 244 DELETE FROM t1 WHERE a%2=1; 245 SELECT COUNT(*) FROM t1; 246 DROP TABLE t1;