Alternative to GROUP BY CUBE in snowflake

user_6216704 0
Viewed 485

I'm trying to do a GROUP BY CUBE() with 8 different elements in Snowflake but based on the documentation, "Snowflake allows up to 7 elements (equivalent to 128 grouping sets) in each cube".

This is a limitation for one of the queries I need to run. The basic form of the query looks like

SELECT A, B, C, D, E, F, G, H
FROM (
    SELECT '1' AS A, '2' AS B, '3' AS C, '4' AS D, '5' AS E, '6' AS F, '7' AS G, 8 AS H
  ) SUB
GROUP BY CUBE(A,B,C,D,E,F,G,H);

If column H is removed, it works just fine. But when I add H to the query, I get

SQL compilation error: Number of grouping sets (256) exceeds maximum number of grouping sets allowed (128)

What would be the way to go to get the same results of a GROUP BY CUBE with 8 elements? I did some research and it looks like GROUP BY GROUPING SETS could work but there's still the 128 grouping sets limit in the same query block.

snowflake-cloud-data-platform sql
2 Answers

You can produce the 2^8 combinations by the union of two operations: One cube with GROUP BY CUBE(A,B,C,D,E,F,G), H -- and one without H, and make it null.


with data as (
  SELECT '1' AS A, '2' AS B, '3' AS C, '4' AS D, '5' AS E, '6' AS F, '7' AS G, 8 AS H
)

SELECT A, B, C, D, E, F, G, H
FROM data
GROUP BY CUBE(A, B, C, D, E, F, G), H
union all
SELECT A, B, C, D, E, F, G, null
FROM data
GROUP BY CUBE(A, B, C, D, E, F, G)

You can prove this with less combinations:

SELECT A, B, C
FROM data
GROUP BY CUBE(A, B), C
union all
SELECT A, B, null
FROM data
GROUP BY CUBE(A, B)

You can see that it gives equivalent results to GROUP BY CUBE(A,B,C).

user_1324380

A brute force method is union all:

select null as a, b, c, d, e, f, g, h
from t
group by cube (b, c, d, e, f, g, h)
union all
select a, null as b, c, d, e, f, g, h
from t
group by cube (a, c, d, e, f, g, h)
union all
. . .

This is feasible for 8 columns. It gets cumbersome if you have any more.

If you are just use cube to generate rows, there might be simpler ways to accomplish the same thing.

user_11440350
Related