有没有办法在MySql中模拟GROUP BY WITH CUBE？ [英] Is there a way to simulate GROUP BY WITH CUBE in MySql?

问题描述

MySql支持GROUP BY WITH ROLLUP，它将返回组中n列的最后一个x的聚合，但不支持GROUP BY WITH CUBE取n列的所有组合并进行聚合。

我可以通过执行GROUP BY WITH ROLLUP查询的联合来模拟此操作，但MySql正在多次实现子查询。我在一个大的子查询中使用了一个组，因此这是不理想的。有没有办法解决这个没有临时表？

解决方案

简短的回答：没有。

长答案：您可以安装一个带有GROUP BY WITH CUBE支持的开源数据仓库，该支持使用Mysql作为存储引擎，如Pentaho。

MySql supports GROUP BY WITH ROLLUP which will return aggregates for the last x of the n columns in the group by but does not support GROUP BY WITH CUBE to take all combinations of the n columns and take aggregates.

I can simulate this by doing unions of GROUP BY WITH ROLLUP queries, but MySql is materializing my subquery multiple times. I am using a group by on a large subquery, so this is suboptimal. Is there a way to solve this without temporary tables?

解决方案

Short answer: No.

Long answer: You may install an open source data warehouse with GROUP BY WITH CUBE support which is using Mysql as a storage engine, such as Pentaho.

这篇关于有没有办法在MySql中模拟GROUP BY WITH CUBE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！