Converting ROLLUP to CUBE

user_651174 0
Viewed 59

Let's say we have access to GROUP BY ROLLUP but not GROUP BY CUBE (for example, in mysql or even postgres if we imagine CUBE doesn't exist in an older version).

The following shows the difference between ROLLUP and CUBE:

ROLLUP (a, b, c)
(a, b, c)
(a, b)
(a)
()

CUBE (a, b, c)
(a, b, c)
(a, b)
(a, c)
(a)
(b, c)
(b)
(c)
()

If CUBE is not an option, what would be the proper way to map the ROLLUP function to be CUBE? It seems it would be:

ROLLUP (a,b,c) UNION ALL
ROLLUP (a,  c) UNION ALL
ROLLUP (  b,c) UNION ALL
ROLLUP (    c)

Is that a correct understanding? And if so, would it be possible to use a recursive CTE or something else to emulate the CUBE function with rollup for N variables? If so, how could this be done?

postgresql mysql sql
1 Answers

You could use a procedure to generate your sqls

You could try a procedure as below pseudo

   Create procedure Rollup(a, b) 
     As
     Begin 
     For i in [a, b, c]    -- or whatever array you may have
          For j! =i
                 Rollup(i, j) 
                 End loop;
      End loop;
    END
user_104974830
Related