SPL:静态转置

   转置功能常用报表等前端展现,将查询出来的数据转置成指定的显示格式。比如行转列,列转行,以及比较复杂的动态转置等等。其中静态转置是指,转置后的表结构事先可以确定,目标结构不会根据数据进行动态的变化。

 

1. 行转列

   行转列顾名思义,这个功能是实现行与列的转换,也就是将行中的值作为列名。行转列的实际应用,基本都要跟随在分组聚合运算之后,通过分组把用于转置的列中每一行数据都处理成不重复的值后,再将各行的值作为列名来展开。行转列的作用,其实就是将某一列的聚合结果,细分为多个更具体的列的聚合结果,以达到更直观的视觉效果。

 

【例 1】 根据学生成绩表,统计每个班的各科最高分,按列显示。部分数据如下:

CLASS

STUDENTID

SUBJECT

SCORE

1

1

English

84

1

1

Math

77

1

1

PE

69

1

2

English

81

1

2

Math

80

   期望得到如下格式结果:

CLASS

MAX_MATH

MAX_ENGLISH

MAX_PE

1

97

96

97

2

97

96

97

 

   在 SPL 中提供了函数 A.pivot() 用于行列转置,默认为行转列。

   SPL脚本如下:


A

1

=T("Scores.csv")

2

=A1.groups(CLASS,SUBJECT;max(SCORE):MAX_SCORE)

3

=A2.pivot(CLASS;SUBJECT,MAX_SCORE;"Math":"MAX_MATH","English":"MAX_ENGLISH","PE":"MAX_PE")

A1:从文件中导入学生成绩表。

A2:分组汇总每班各科的最高分。

A3:使用函数 A.pivot(),将每班各科最高分行转列。

 

2. 列转行

   列转行与行转列相反,每一个要转置的字段都生成新的一行,字段名或对应名称转化为新字段的值,原字段值则转为另一新字段的值。

 

【例 2】根据奥运会奖牌总榜,生成每种奖牌的榜单。部分数据如下:

Game

Nation

Gold

Silver

Copper

30

USA

46

29

29

30

China

38

27

23

30

UK

29

17

19

30

Russia

24

26

32

30

Korea

13

8

7

   期望得到如下格式结果:

GAME

NATION

MEDAL_TYPE

MEDALS

30

USA

GOLD

46

30

USA

SILVER

29

30

USA

COPPER

29

30

China

GOLD

38

30

China

SILVER

27

30

China

COPPER

23

 

   在 SPL 中函数 A.pivot() 选项 @r 时用于列转行。

   SPL脚本如下:


A

1

=T("Olympic.txt")

2

=A1.pivot@r(GAME,NATION;MEDAL_TYPE,MEDALS; GOLD, SILVER, COPPER)

A1:导入奥运会奖牌表。

A2:使用函数 A.pivot@r(),将字段 GOLD、SILVER、COPPER 进行列转行。

 

3. 行列互转

   有些时候,我们需要将行中的值作为列名,并且将列名转换为字段值。

【例 3】 按渠道分类的销售表,按年月进行记录。部分数据如下:

YEAR

MONTH

ONLINE

STORE

2020

1

2440

3746.2

2020

2

1863.4

448.0

2020

3

1813.0

624.8

2020

4

670.8

2464.8

2020

5

3730.0

724.5

   期望查询 2020 年每种渠道每个月的销售额,格式如下:

CATEGORY

1

2

3

ONLINE

2440

1863.4

1813.0

STORE

3746.2

448.0

624.8

 

   按照逻辑顺序,先使用函数 A.pivot@r()列转行,再使用函数 A.pivot() 行转列即可。

   SPL脚本如下:


A

1

=T("MonthSales.csv").select(YEAR:2020)

2

=A1.pivot@r(YEAR,MONTH; CATEGORY, AMOUNT)

3

=A2.pivot(CATEGORY; MONTH, AMOUNT)

A1:导入销售表,并选出 2020 年的记录。

A2:使用函数 A.pivot@r() 列转行,将渠道类型转换为 CATEGORY 的字段值。列转行后结果如下:

YEAR

MONTH

CATEGORY

AMOUNT

2020

1

ONLINE

2440

2020

1

STORE

3746.2

2020

2

ONLINE

1863.4

2020

2

STORE

448.0

2020

3

ONLINE

1813.0

2020

3

STORE

624.8

A3:使用函数 A.pivot() 行转列,将月份字段的值转换为列。

Scores.csv

Olympic.txt

MonthSales.csv