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 的递归对比
英文版