Generate list of distinct dimensions in a table-CodePudding

I have a table with 3 dimensions- A, B, and C. I essentially want values of all possible combinations for these dimensions and populate all measures(M) as 0 when a combination isn't present.

Suppose I have the table-

If I do this I get -


select a,b,c from sum(m) fact group by a,b,c

But I would like all possible combinations, -

Currently, I a doing a cross join like below, but is there some faster way to do this (as my table has about ~1M records)? -

select * from (
select distinct f1.a, f2.b, f3.c 
from fact f1 
cross join fact f2 
cross join fact f3 ) all
left join 
( select a,b,c from sum(m) fact group by a,b,c) s
on all.a=s.a and all.b=s.b and all.c=s.c

CodePudding user response：

If this is Oracle Database, then this is exactly what cube is for.

select a, b, c, sum(m) 
from my_table 
group by cube(a,b,c)

CodePudding user response：

MySQL:

GROUP BY a,b,c will produce 1 row per combination that exists in the table.

If you want all possible combinations, you need to build 1 (or 3) more tables to list all the possible values, then do a LEFT JOIN from them. You may also want COALESCE(col, 0) to turn NULLs into zeros.