[编辑中] MySQL中高效的交叉表查询

使用基本的SQL查询可以很容易地表达许多基本用例,但是很少有需要您的创意才能在查询中表达它的用例。 假设是我们希望查询完成所有繁重的工作,并避免不必要的服务层逻辑来进行过滤或排序。 Cross Tab的用例属于此类,需要一些开箱即用的查询来获取正确数量的数据。

什么是交叉表?

让我们从一个表开始,该表包含值的基数有限的列,例如Company,Quarter和与前一个Ex相比,其值更具动态性的列:Revenue,Profits,这对于Company和Quarter的每种组合都是不同的。

交叉表视图:假设您要构建一个如图1所示的视图,其中您最多需要Q1和Q2的三个公司,现在如何将其转换为MySQL查询? 一些查询引擎(例如Impala)提供了分析功能来轻松实现此目的,但是截至本文撰写之时,MySQL中尚无此类支持。

上面是一个交叉表的示例,其中您具有针对多个维度(公司和Q1 / Q2)的单独度量(收入,利润)。 如果仅对Q1使用相同的视图,则查询将是一个简单得多的查询1: 从WHERE季度表中选择SELECT公司,收入,利润= Q1;

但是这里的用例是显示最大N(在示例3中,交叉表尺寸Q1和Q2都显示)。 您可能会说我们可以将相同的查询修改为query2: “ SELECT公司,收入,利润,季度FROM表WHERE季度IN(Q1,Q2);” query2的问题在于,您将获得Q1和Q2的所有可能的行(这会使您的内存膨胀
),如果您有限制,则不能保证将返回所有均属于Q1和Q2的行。 您可以通过订购主要尺寸(公司)并将其限制为预期限制X交叉表尺寸的数量来解决此问题。 在上面的示例中,它将是3(companies)* 2(Q1,Q2)= 6。

因此,最终查询将类似于查询3: “选择公司,收入,利润,季度FROM表WHERE季度IN(Q1,Q2)ORDER BY公司限制6;” 该查询将确保返回的结果在每个“交叉表”维度中都包含一行,并且您避免避免消耗过多的内存或实现任何服务层逻辑来过滤不需要的行。