SPL：复杂静态转置
转置功能常用报表等前端展现，将查询出来的数据转置成指定的显示格式。比如行转列，列转行，以及比较复杂的动态转置等等。在《SPL：静态转置》一文中，我们为大家介绍了静态转置。但是有些时候，虽然转置后的表结构是可以确定的，但是很难用 pivot 函数直接实现。接下来我们一起看一下，对于这些复杂的静态转置，SPL 是如何处理的。
1. 多行转多行
【例 1】 根据打卡记录，整理员工每日的外出数据。在日常考勤信息表中，每卡每天有 7 条数据：
PER_CODE |
IN_OUT |
DATE |
TIME |
TYPE |
1110263 |
1 |
2013-10-11 |
09:17:14 |
In |
1110263 |
6 |
2013-10-11 |
11:37:00 |
Break |
1110263 |
5 |
2013-10-11 |
11:38:21 |
Return |
1110263 |
0 |
2013-10-11 |
11:43:21 |
NULL |
1110263 |
6 |
2013-10-11 |
13:21:30 |
Break |
1110263 |
5 |
2013-10-11 |
14:25:58 |
Return |
1110263 |
2 |
2013-10-11 |
18:28:55 |
Out |
… |
… |
… |
… |
… |
期望得到如下格式结果：
PER_CODE |
DATE |
IN |
OUT |
BREAK |
RETURN |
1110263 |
2013-10-11 |
9:17:14 |
18:28:55 |
11:37:00 |
11:38:21 |
1110263 |
2013-10-11 |
9:17:14 |
18:28:55 |
13:21:30 |
14:25:58 |
… |
… |
… |
… |
… |
… |
虽然转置后的表结构是可以确定的，但是用函数 A.pivot()实现起来会很复杂。这时可以先创建目标数据结构，再填充数据。首先我们按照期望的结构创建一个空表。然后将数据排序，每 7 条记录分为一组，每组的成员取 [1,7,2,3,1,7,5,6]，即要生成的 2 条记录的时间值。最后再按目标结构顺序填入数据即可。
SPL 脚本如下：
A |
|
1 |
=create(PER_CODE,DATE,IN,OUT,BREAK,RETURN) |
2 |
=T("DailyTime.txt").sort(PER_CODE,DATE,TIME) |
3 |
=A2.group((#-1)\7).(~([1,7,2,3,1,7,5,6])) |
4 |
>A1.record(A3.conj([~.PER_CODE,~.DATE]|~.(TIME).m([1,2,3,4])|[~.PER_CODE,~.DATE]|~.(TIME).m([5,6,7,8]))) |
A1：根据目标结构创建空表。
A2：导入日常打卡记录，并按员工号和日期时间排序。
A3：每 7 条记录为一组，每组按指定顺序（[1,7,2,3,1,7,5,6]）返回记录。
A4：将所有的记录数据按目标顺序合并，再添加到 A3 创建的表中。
2. 行转列同时行间计算
【例 2】 根据用户支付明细表，统计各个用户 2014 年每月应付金额的汇总表。部分数据如下：
ID |
CUSTOMERID |
NAME |
UNPAID |
ORDER_DATE |
112101 |
C013 |
CA |
12800 |
2014/02/21 |
112102 |
C013 |
CA |
3500 |
2014/06/15 |
112103 |
C013 |
CA |
2600 |
2015/03/21 |
112104 |
C025 |
BK |
4600 |
2014/06/11 |
112105 |
C002 |
TUN |
23100 |
2014/01/22 |
112106 |
C002 |
TUN |
13800 |
2014/08/03 |
… |
… |
… |
… |
… |
期望得到如下格式结果：
NAME |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
TUN |
23100 |
23100 |
23100 |
23100 |
23100 |
23100 |
23100 |
13800 |
13800 |
13800 |
13800 |
13800 |
CA |
12800 |
12800 |
12800 |
12800 |
3500 |
3500 |
3500 |
3500 |
3500 |
3500 |
3500 |
|
BK |
4600 |
4600 |
4600 |
4600 |
4600 |
4600 |
4600 |
|||||
… |
对于复杂静态转置，我们还是先创建目标数据结构，再填充数据。
SPL 脚本如下：
A |
|
1 |
=create(NAME,${12.concat@c()}) |
2 |
=T("UserPayment.txt").select(year(ORDER_DATE)==2014).group(CUSTOMERID) |
3 |
>A2.((m12=12.(null),~.(m12(month(ORDER_DATE))=UNPAID), m12.(~=ifn(~,~[-1])),A1.record(NAME|m12))) |
A1：根据目标结构创建空表。
A2：导入 2014 年用户支付明细表，并按客户 ID 分组。
A3：循环每组数据，对成员再进行循环，计算每个月的应付金额，最后和客户名称一起添加到 A1 创建的表中。
3. 数据分栏
数据分栏常用于数据展现，将有相似属性的数据分栏列出来，可以互相比较查看。
【例 3】 分栏列出销售部和研发部工资超 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 |
… |
… |
… |
… |
… |
… |
期望结果如下：
SALESNAME |
SALARY |
RDNAME |
SALARY |
Madeline |
15000 |
Ashley |
16000 |
Jacob |
12000 |
Jacob |
16000 |
Andrew |
12000 |
Ryan |
13000 |
… |
… |
… |
… |
SPL 处理数据分栏，与之前的方法类似。还是先构造目标数据结构，再填入数据。
SPL 脚本如下：
A |
|
1 |
=T("Employee.csv").select(SALARY >10000).sort@z(SALARY) |
2 |
=A1.select(DEPT:"Sales") |
3 |
=A1.select(DEPT:"R&D") |
4 |
=create('SALESNAME',SALARY,'RDNAME', SALARY) |
5 |
=A4.paste(A2.(NAME),A2.(SALARY),A3.(NAME),A3.(SALARY)) |
A1：导入员工表，选出工资超过 1 万的记录，并按工资降序排列。
A2：取出销售部数据。
A3：取出研发部数据。
A4：按目标结构创建一个空表。
A5：使用函数 A.paste() 将值粘贴到对应列。
英文版