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 的支持下,转置运算会非常容易。