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 的递归对比
英文版