cube真好用，但是效率不高

〇〇 · 发表于 2021-9-1 10:15

同时有distinct和count(*)
pop=# create table test_cube12_man4 as
pop-# with t as(select c1,c2,c3,count(*)cnt from ren group by c1,c2,c3)
pop-# select c1,c2,count(distinct c3)cnt1,sum(cnt)cnt2 from t group by c1,c2
pop-# union all select c1,null,count(distinct c3),sum(cnt)from t group by c1
pop-# union all select null,c2,count(distinct c3),sum(cnt)from t group by c2
pop-# union all select null,null,count(distinct c3),sum(cnt)from t;
SELECT 12
时间：2542.105 ms (00:02.542)
pop=# select * from test_cube12_man4;
c1 | c2 | cnt1 | cnt2
----+----+------+----------
  1 |  1 | 5 |  3331362
  1 |  2 | 5 |  3332019
  1 |  3 | 5 |  3333445
  2 |  1 | 5 |  3334111
  2 |  2 | 5 |  3334562
  2 |  3 | 5 |  3334501
  1 | | 5 |  9996826
  2 | | 5 | 10003174
|  1 | 5 |  6665473
|  2 | 5 |  6666581
|  3 | 5 |  6667946
| | 5 | 20000000
(12 行记录)

时间：1.313 ms
pop=# create table test_cube12_d as select c1,c2,count(distinct c3)cnt1,count(*)cnt2 from ren group by cube(c1,c2);
SELECT 12
时间：48208.805 ms (00:48.209)
pop=# select * from test_cube12_d;
c1 | c2 | cnt1 | cnt2
----+----+------+----------
  1 |  1 | 5 |  3331362
  1 |  2 | 5 |  3332019
  1 |  3 | 5 |  3333445
  1 | | 5 |  9996826
  2 |  1 | 5 |  3334111
  2 |  2 | 5 |  3334562
  2 |  3 | 5 |  3334501
  2 | | 5 | 10003174
| | 5 | 20000000
|  1 | 5 |  6665473
|  2 | 5 |  6666581
|  3 | 5 |  6667946
(12 行记录)

时间：1.416 ms