searchusermenu
  • 发布文章
  • 消息中心
点赞
收藏
评论
分享
原创

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

2023-09-26 11:58:51
8
0

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

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

如下面表1,是用SQL查出来的二维数据(节选)

年份

季度

省份

城市

销售员

销售额

2019

第一季度

广东

广州

张三

3541

2019

第一季度

广东

广州

李四

6841

2019

第一季度

广东

深圳

王屋

2571

2019

第一季度

广东

深圳

李柳

2548

2019

第一季度

广东

深圳

洪七

2547

2019

第一季度

广东

珠海

刘巴

2546

2019

第一季度

广东

珠海

唐诗

2987

2019

第一季度

广东

湛江

宋词

5845

                                  表1

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

年份

2019

2020

汇总

季度

一季度

二季度

三季度

四季度

一季度

二季度

三季度

四季度

-

省份

城市

销售额

销售额

销售额

销售额

销售额

销售额

销售额

销售额

销售额

广东

广州

10086

45278

4527

13444

13475

78653

45453

7916

218832

深圳

26545

75278

72872

15165

1377

45353

53843

8466

298899

珠海

16387

75275

4525

16540

1537

7353

86345

5454

213416

湛江

4527

4522

7245

5444

1733

3537

42345

8479

77832

湖北

武汉

72375

78272

82542

54984

17335

3538

45345

5981

360372

襄阳

27824

7272

7252

5445

1234

34538

3453

8694

95712

宜昌

52452

4356

42631

65484

8325

41353

5659

5749

226009

汇总

-

212215

292272

221594

176506

45016

214325

282443

50739

1491072

                                                        表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个二维数组合并成一个大的二维数组,就可以得到交叉表的数据结构了。

0条评论
0 / 1000
白龙马
14文章数
0粉丝数
白龙马
14 文章 | 0 粉丝
原创

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

2023-09-26 11:58:51
8
0

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

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

如下面表1,是用SQL查出来的二维数据(节选)

年份

季度

省份

城市

销售员

销售额

2019

第一季度

广东

广州

张三

3541

2019

第一季度

广东

广州

李四

6841

2019

第一季度

广东

深圳

王屋

2571

2019

第一季度

广东

深圳

李柳

2548

2019

第一季度

广东

深圳

洪七

2547

2019

第一季度

广东

珠海

刘巴

2546

2019

第一季度

广东

珠海

唐诗

2987

2019

第一季度

广东

湛江

宋词

5845

                                  表1

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

年份

2019

2020

汇总

季度

一季度

二季度

三季度

四季度

一季度

二季度

三季度

四季度

-

省份

城市

销售额

销售额

销售额

销售额

销售额

销售额

销售额

销售额

销售额

广东

广州

10086

45278

4527

13444

13475

78653

45453

7916

218832

深圳

26545

75278

72872

15165

1377

45353

53843

8466

298899

珠海

16387

75275

4525

16540

1537

7353

86345

5454

213416

湛江

4527

4522

7245

5444

1733

3537

42345

8479

77832

湖北

武汉

72375

78272

82542

54984

17335

3538

45345

5981

360372

襄阳

27824

7272

7252

5445

1234

34538

3453

8694

95712

宜昌

52452

4356

42631

65484

8325

41353

5659

5749

226009

汇总

-

212215

292272

221594

176506

45016

214325

282443

50739

1491072

                                                        表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个二维数组合并成一个大的二维数组,就可以得到交叉表的数据结构了。

文章来自个人专栏
Java
14 文章 | 1 订阅
0条评论
0 / 1000
请输入你的评论
0
0