SPL：动态转置
转置功能常用报表等前端展现，将查询出来的数据转置成指定的显示格式。比如行转列，列转行，以及比较复杂的动态转置等等。动态行转列，是指转置时生成的字段不能事先指定，只能根据原字段的取值动态确定。接下来我们一起看一下，对于动态转置，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 |
… |
|
… |
… |
… |
… |
… |
这个例子是行转列，目标字段需要从数据中提取。函数 A.pivot() 支持这种动态转置，不指定目标字段时，它会自动提取目标字段的名称。
SPL脚本如下：
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 |
… |
||
… |
… |
… |
… |
… |
… |
目标字段并不是从某个字段动态取出，而是需要动态计算取得，这时就不能使用函数 A.pivot() 了。我们可以根据收入类型最长的一组，动态生成目标结构，再填充数据。
SPL脚本如下：
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 |
|
… |
… |
… |
… |
… |
… |
… |
首先将几个表通过关联关系进行连接，然后再按前面题目的思路解决即可。
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 创建的表中。
其实本例与前面的动态转置并没有本质上的不同，只是多了一步，将几个表通过关联关系进行连接。然后根据需求创建目标数据结构，再填入数据即可。
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
英文版