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 是如何解决复杂静态转置和动态转置的。
SQL 与 SPL 对比系列：
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比
英文版