SQL 和 SPL 的静态转置对比

【摘要】
    转置功能常用报表等前端展现,将查询出来的数据转置成指定的显示格式。比如行转列,列转行,以及比较复杂的动态转置等等。SQL 和 SPL 是大家比较熟悉的程序语言,本文将探讨对于转置问题,这两种语言的解决方案和基本原理。如何简便快捷的处理转置运算,这里为你全程解析,并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的静态转置对比

 

静态转置是指,转置后的表结构事先可以确定,目标结构不会根据数据进行动态的变化。

 

1. 行转列

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

 

【例 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

 

SQL的解决方案:

在 SQL 中,PIVOT 函数可以用于实现行转列和列转行,但只有部分数据库的较新版本才支持。例如 ORACLE 从 11g 版本才开始支持 PIVOT,而 MYSQL 等数据库一直不支持,需要使用子查询分组汇总,再 LEFT JOIN 等方式实现行转列。在本文中我们以 ORACLE 11g 为例,SQL 语句如下:

 

   SELECT *

   FROM (

      SELECT

         CLASS, SUBJECT, SCORE

      FROM SCORES

   )

   PIVOT (

      MAX(SCORE) FOR SUBJECT

      IN (

         'Math' AS MAX_MATH,

         'English' AS MAX_ENGLISH,

         'PE' AS MAX_PE

      )

   )

 

SPL的解决方案:

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


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(),将每班各科最高分行转列。

 

SPL同样也支持从数据库中读取数据表,比如A1可以改为:


A

1

=connect("db").query("SELECT   * FROM SCORES")

 

 

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

 

 

SQL的解决方案:

    在 SQL 中,可以使用 UNPIVOT 函数进行列转行:

 

   SELECT *

   FROM OLYMPIC

   UNPIVOT (

      MEDALS FOR MEDAL_TYPE IN (

          GOLD,SILVER,COPPER

      )

   )

 

SPL的解决方案:

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


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

期望得到如下格式结果:

CATEGORY

1

2

3

ONLINE

2440

1863.4

1813.0

STORE

3746.2

448.0

624.8

 

 

SQL的解决方案:

    这个问题单独用行转列或者列转行都无法实现,需要进行两次转换。首先列转行,将渠道类型转换为类别列的字段值:

YEAR

MONTH

CATEGORY

AMOUNT

2020

1

ONLINE

2440

2020

1

STORE

3746.2

2020

2

ONLINE

1863.4

2020

2

STORE

448.0

 

然后行转列,将月份字段的值转换为列。SQL 语句如下:

 

   SELECT *

   FROM (

      SELECT *

      FROM MONTH_SALES

      UNPIVOT (

         AMOUNT FOR CATEGORY IN (

            "ONLINE",STORE

         )

      )

      WHERE YEAR=2020

   )

   PIVOT (

      MAX(AMOUNT) FOR MONTH

      IN (

         1 AS "1",2 AS "2",2 AS "3",

         4 AS "4",5 AS "5",6 AS "6",

         7 AS "7",8 AS "8",9 AS "9",

         10 AS "10",11 AS "11",12 AS "12"

      )

   )

 

在 SQL 中不允许将非常量表达式用于 PIVOT/UNPIVOT 值,所以行转列时需要枚举所有的月份。

 

SPL的解决方案:

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


A

1

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

2

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

3

=A2.pivot(CATEGORY; MONTH, AMOUNT)

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

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

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

 

 

    通过上面几个例题我们可以看到,对于简单的行列转置问题,无论是 SQL 还是 SPL 都可以很好的解决。但是实际应用中的转置不全是简单的分组汇总 +PIVOT 这么简单,在后续的文章中,我们将为大家介绍 SQL 和 SPL 是如何解决复杂静态转置和动态转置的。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾