|
MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 1000 |
|
+-------------------+
|
1 row in set (0.28 sec)
|
|
MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 2120 |
|
+-------------------+
|
1 row in set (0.29 sec)
|
1000 <> 2120
MariaDB [dw]>
|
MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 630 |
|
+-------------------+
|
1 row in set (0.23 sec)
|
|
MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 976 |
|
+-------------------+
|
1 row in set (0.11 sec)
|
630 <> 976
MariaDB [dw]>
|
MariaDB [dw]> select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 0 |
|
+-------------------+
|
1 row in set (0.15 sec)
|
|
MariaDB [dw]> select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
|
+-------------------+
|
| count(distinct s) |
|
+-------------------+
|
| 1 |
|
+-------------------+
|
1 row in set (0.08 sec)
|
0 <> 1
Test case :
drop table if exists t;
|
create table t(a int,b int, c int , d int);
|
insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
|
insert into t(a,b,c,d) values(1, rand()*1000, rand()*1000, rand()*1000);
|
replace into t(a,b,c,d) select 1, rand()*1000, rand()*1000, rand()*1000 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15, t t16, t t17;
|
|
select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
|
select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
|
|
select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
|
select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
|
|
select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
|
select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
|
|