如何将二维数据转换成交叉表数据

在前面的文章中讲述过，自助报表的主要原理就是，根据用户拖拽产生的配置数据，生成一条或多条查询SQL，然后将在数据库中执行查询SQL得出的二维数据转换成对应图表的数据结构，交由前端渲染。

在众多的图表数据结构中，最复杂的便是交叉表数据结构。

如下面表1，是用SQL查出来的二维数据（节选）

                                  表1

需要将上面的数据转换成下面表2的交叉表

                                                        表2

第一眼看过去可能无从下手，但细心观察之后，不难发现交叉表跟我们在数学课堂学过的直角坐标系是有相似之处的

于是尝试将交叉表按上图划分为四个象限，然后每个象限独自生成一个二维数据，接着将四个二维数据合并成一个大的二维数组，最后就是标记需要合并的单元格，交由前端渲染了

如上图中，第一象限由列维度的值组成，第二象限最简单，由列维度和行维度的名称组成，是固定值，第三象限和第一象限类似，由行维度的值组成，第四象限由指标统计值组成。

我们先给表1每个字段标上字段名，依次为：年份（year）、季度（quarter）、省份（province）、城市（city）、销售员（celler）、销售额（amount）

不难看出，表1的数据可以用下面SQL查询得到

select `year`,`quarter`,`province`, `city`,`celler`,`amount` from sale_data

变成交叉表后：

1）行维度为省份（province）、城市（city），

2）列维度为年份（year）、季度（quarter），

3）统计指标为销售额（amount），汇总方式为求和（sum函数）

销售员（celler）字段没用上，在求和汇总计算中去掉了

第四象限中大部分数据可以用下面SQL查询得到

select sum(`amount`),`province`, `city`,`year`,`quarter` from sale_data group by `province`, `city`,`year`,`quarter`

汇总列和汇总行数据需要用额外两条SQL查询得到

select sum(`amount`),`province`, `city` from sale_data group by `province`, `city`;
select sum(`amount`),`year`,`quarter` from sale_data group by `year`,`quarter`;

最右下角的汇总单元格，还需要一条SQL查询得到

select sum(`amount`) from sale_data;

将四个SQL的查询结果逐条转换成DataCell，结构如下

@Data
public class DataCell {
    // 指标汇总统计值，第四象限
    private Object data;
    // 列维度值的列表，第一象限
    private List<String> tops;
    // 行维度值的列表，第三象限
    private List<String> lefts;
}

这样我们就能得到一个 DataCell的列表，然后就可以分别构造每个象限的二维数组了。

1）将所有tops加到一个列表中，得到一个 List<List<String>> 对象，命名为topList，对最外层List排序之后就可以得到第一象限的二维数组了；

2）第二象限的二维数组全是常量

3）将所有lefts加到一个列表中，得到一个 List<List<String>> 对象，命名为leftList，对最外层List排序之后，再纵横翻转下，就可以得到第三象限的二维数组了；

4）第四个象限，从[0][0]开始，从DataCell列表找到 tops == topList.get(0) 且 lefts == leftList.get(0) 的DataCell，将data值填充到[0][0]位置，如此类推，就可以得到第四象限的二维数组了

接着将4个二维数组合并成一个大的二维数组，就可以得到交叉表的数据结构了。