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 创建的表中。

   其实本例与前面的动态转置并没有本质上的不同,只是多了一步,将几个表通过关联关系进行连接。然后根据需求创建目标数据结构,再填入数据即可。


Employee.csv

Income.txt

OrderDetail.txt

Orders.txt

Product.txt