如何将组表达式和多维数据集表达式按子句混在一起？

Question

我想在闪烁or中使用group by和cube，就像select a,b,c,sum(d) from table group by a, cube(b,c)，但是它抛出了java.lang.UnsupportedOperationException，似乎我只能使用其中之一(组表达式或多维数据集表达式)。

这种方法可以在Postgre或Presto中获得成功，并在下面的片段中详细介绍如何在SparkSql文档中使用。

Mixed/Nested Grouping Analytics

A GROUP BY clause can include multiple group_expressions and multiple CUBE|ROLLUP|GROUPING SETSs. GROUPING SETS can also have nested CUBE|ROLLUP|GROUPING SETS clauses, e.g. GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)))). CUBE|ROLLUP is just a syntax sugar for GROUPING SETS, please refer to the sections above for how to translate CUBE|ROLLUP to GROUPING SETS. group_expression can be treated as a single-group GROUPING SETS under this context. For multiple GROUPING SETS in the GROUP BY clause, we generate a single GROUPING SETS by doing a cross-product of the original GROUPING SETSs. For nested GROUPING SETS in the GROUPING SETS clause, we simply take its grouping sets and strip it. For example, GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ()) and GROUP BY warehouse, ROLLUP(product), CUBE(location, size) is equivalent to GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse)).

GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product))) is equivalent to GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Answer 1

我有个不太完美的主意。

select a,b,c,sum(d) from table group by cube(a,b,c) having grouping(a)!=1

它仍然将多维数据集列a，但随后它将删除它。