SQL 和 SPL 的动态转置对比
【摘要】
转置功能常用报表等前端展现，将查询出来的数据转置成指定的显示格式。比如行转列，列转行，以及比较复杂的动态转置等等。SQL 和 SPL 是大家比较熟悉的程序语言，本文将探讨对于转置问题，这两种语言的解决方案和基本原理。如何简便快捷的处理转置运算，这里为你全程解析，并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的动态转置对比
动态行转列，是指转置时生成的字段不能事先指定，只能根据原字段的取值动态确定。
1. 根据字段值自动生成列
【例 1】 根据员工表，统计各部门在不同地区的平均工资。部分数据如下：
ID |
NAME |
SURNAME |
STATE |
DEPT |
SALARY |
1 |
Rebecca |
Moore |
California |
R&D |
7000 |
2 |
Ashley |
Wilson |
New York |
Finance |
11000 |
3 |
Rachel |
Johnson |
New Mexico |
Sales |
9000 |
4 |
Emily |
Smith |
Texas |
HR |
7000 |
5 |
Ashley |
Smith |
Texas |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
期望得到如下格式结果：
DEPT |
California |
Colorado |
Florida |
… |
Administration |
9333.333
|
… |
||
Finance |
8000 |
5000 |
10000 |
… |
HR |
10000 |
7000 |
… |
|
… |
… |
… |
… |
… |
SQL的解决方案：
在本例中，结果集的字段名称是从 STATE 字段值中提取的。前面介绍过，在 SQL 中不允许将非常量表达式用于 PIVOT/UNPIVOT 值，所以 PIVOT 不能用于动态的行转列。SQL 语句本身也不支持返回动态的结构，无法实现动态行转列。
想要通过数据库实现这个问题，可以使用存储过程来构造动态的 SQL 语句。本文重点是比较 SQL 和 SPL 语句，就不再具体说明了。
SPL的解决方案：
这个例子是行转列，目标字段需要从数据中提取。函数 A.pivot() 支持这种动态转置，不指定目标字段时，它会自动提取目标字段的名称。
A |
|
1 |
=T("Employee.csv") |
2 |
=A1.groups(STATE,DEPT;avg(SALARY):AVG_SALARY) |
3 |
=A2.pivot(DEPT; STATE, AVG_SALARY) |
A1：导入员工表。
A2：分组汇总每个州各部门的平均工资。
A3：使用函数 A.pivot() 行转列，不指定目标字段时，会自动提取目标字段的名称。
2. 通过计算动态生成列名
【例 2】 根据收入明细表，统计每位员工各类收入的情况，类别自动生成。部分数据如下：
NAME |
SOURCE |
INCOME |
David |
Salary |
8000 |
David |
Bonus |
15000 |
Daniel |
Salary |
9000 |
Andrew |
Shares |
26000 |
Andrew |
Sales |
23000 |
… |
… |
… |
期望得到如下格式结果：
NAME |
SOURCE1 |
INCOME1 |
SOURCE2 |
INCOME2 |
… |
Andrew |
Shares |
26000 |
Sales |
23000 |
… |
Daniel |
Salary |
9000 |
… |
||
David |
Salary |
8000 |
Bonus |
15000 |
… |
Robert |
Bonus |
13000 |
… |
||
… |
… |
… |
… |
… |
… |
SQL的解决方案：
在本例中，列名需要根据字段的取值动态计算取得。不确定行转列后，列的数量，甚至连列名也不能完全确定。SQL 语句无法实现这类动态行转列。
SPL的解决方案：
目标字段并不是从某个字段动态取出，而是需要动态计算取得，这时就不能使用函数 A.pivot() 了。我们可以根据收入类型最长的一组，动态生成目标结构，再填充数据。
A |
|
1 |
=T("Income.txt").group(NAME) |
2 |
=A1.max(~.len()) |
3 |
=create(NAME, ${A2.("SOURCE"/~/", INCOME"/~).concat@c()}) |
4 |
>A1.(A3.record(~.NAME | ~.conj([SOURCE, INCOME]))) |
A1：导入收入明细表，并按姓名分组。
A2：计算分组成员的最大数量，即最多的收入分类数。
A3：根据最多的收入分类数动态生成列名，创建空表。
A4：对各组循环，将每组的姓名、收入来源和收入金额填充到 A3 创建的表中。
3. 表间关联的动态行专列
【例 3】 根据订单表、订单明细表和产品表，查询出 2014 年每个客户每日购买产品的汇总表。其中订单表和订单明细表是一对多关系，每个订单有多条明细数据。订单明细表与产品表是多对一关系，订单明细表中的产品 ID 字段指向了产品表的 ID 字段。部分数据如下：
ORDERS:
ID |
CUSTOMERID |
EMPLOYEEID |
ORDER_DATE |
ARRIVAL_DATE |
10248 |
VINET |
5 |
2012/07/04 |
2012/08/01 |
10249 |
TOMSP |
6 |
2012/07/05 |
2012/08/16 |
10250 |
HANAR |
4 |
2012/07/08 |
2012/08/05 |
10251 |
VICTE |
3 |
2012/07/08 |
2012/08/05 |
10252 |
SUPRD |
4 |
2012/07/09 |
2012/08/06 |
… |
… |
… |
… |
… |
ORDER_DETAIL:
ID |
ORDER_NUMBER |
PRODUCTID |
PRICE |
COUNT |
DISCOUNT |
10814 |
1 |
48 |
102.0 |
8 |
0.15 |
10814 |
2 |
48 |
102.0 |
8 |
0.15 |
10814 |
3 |
48 |
306.0 |
24 |
0.15 |
10814 |
4 |
48 |
102.0 |
8 |
0.15 |
10814 |
5 |
48 |
204.0 |
16 |
0.15 |
… |
… |
… |
… |
… |
… |
PRODUCT:
ID |
NAME |
SUPPLIERID |
CATEGORY |
1 |
Apple Juice |
2 |
1 |
2 |
Milk |
1 |
1 |
3 |
Tomato sauce |
1 |
2 |
4 |
Salt |
2 |
2 |
5 |
Sesame oil |
2 |
2 |
… |
… |
… |
… |
期望得到如下格式结果：
ORDER_DATE |
CUSTOMERID |
PRODUCT1 |
COUNT1 |
PRODUCT2 |
COUNT2 |
… |
2014/1/5 |
VICTE |
Corn flakes |
8 |
|||
2014/1/23 |
CONSH |
Chicken |
3 |
Cake |
9 |
|
… |
… |
… |
… |
… |
… |
… |
SQL的解决方案：
与前面的例子类似，SQL 语句无法实现这类动态行转列。
SPL的解决方案：
首先将几个表通过关联关系进行连接，然后再按前面题目的思路解决即可。
A |
|
1 |
=T("Orders.txt").select(year(ORDER_DATE)==2014) |
2 |
=T("Product.txt") |
3 |
=T("OrderDetail.txt").switch(PRODUCTID,A2:ID).group(ID) |
4 |
=join(A1:ORDERS,ID;A3:DETAIL,ID) |
5 |
=create(DATE,CUSTOMERID,${A4.max(DETAIL.len()).("PRODUCT"/~/","/"COUNT"/~).concat@c()}) |
6 |
>A4.run(A5.record([ORDERS.ORDER_DATE,ORDERS.CUSTOMERID]|DETAIL.([PRODUCTID.NAME,COUNT]).conj())) |
A1：导入订单表，并选出 2014 年的记录。
A2：导入产品表。
A3：导入订单明细表，与产品表通过产品 ID 连接，将产品 ID 转换为对应的产品记录。然后按订单 ID 分组。
A4：订单表与订单明细表按照订单 ID 连接。
A5：根据订单明细最多的一组计算目标表结构，并创建空表。
A6：循环订单信息，顺序将数据填充到 A5 创建的表中。
其实本例与前面的动态转置并没有本质上的不同，只是多了一步，将几个表通过关联关系进行连接。然后根据需求创建目标数据结构，再填入数据即可。
总结
通过 SQL 和 SPL 转置对比的三篇文章，我们可以看到，SQL 提供的静态转置功能 PIVOT 和 UNPIVOT 适用范围很受限，而且还只有部分数据库支持。要用 SQL 实现一些比较复杂的静态转置功能，常常会遇到语句过于复杂的问题，而且也缺少一个标准的解决思路。对于动态转置，SQL 语句则是无法实现，只能通过存储过程生成动态 SQL 等较为复杂的方式解决。
而 SPL 所提供的转置功能要更加灵活，适应性也更加广泛，可以满足各种复杂的转置需求。更重要的是，SPL 处理复杂转置问题的思路非常清晰，首先按照需求创建目标数据结构，再将计算出的数据依次填入表中即可。
另外，当查询比较复杂时，SQL 语句的复杂程度会成倍增加。比如经常要用到临时表、嵌套查询等等，使得 SQL 语句的编写和维护难度提升。而 SPL 只要按自然思维去组织计算逻辑，逐行书写出简洁的代码。
esProc 是专业的数据计算引擎，基于有序集合设计，同时提供了完善的集合运算，相当于 Java 和 SQL 优势的结合。在 SPL 的支持下，转置运算会非常容易。
SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比
英文版