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() 将值粘贴到对应列。

UserPayment.txt

DailyTime.txt

Employee.csv