一、概述
Oracle 行转列函数 PIVOT 是一种非常方便的操作,在处理跨行转换数据的时候非常有效,能够将行数据转为列数据,方便数据的查询和分析。Oracle 行转列函数 PIVOT 主要是通过将数据行转化为数据列,类似于 Excel 的数据透视表功能,通过将聚合函数应用于数据列,实现数据的交叉汇总。
二、PIVOT 的基本语法
SELECT <非聚合列>,
[pivot_clause][
[subquery]
{ FOR <column>
IN <value1>, <value2>, ... <value_n> ]
}
]
FROM <table>
[PIVOT XML]
其中,pivot_clause 包括 PIVOT 操作的聚合列、行列转换所需的列名和列转换对应值的映射。使用 PIVOT XML 可以让结果以 XML 形式返回。
三、PIVOT 的具体应用
a. 按列分组汇总数据
实际运用中有很多这样的场景:需求是按照特定的列进行分组,同时还需要对其他列进行计数、求和这样的聚合计算,所产生的数据结构如下:
| ID | NAME | AGE | GENDER | SCORE |
| 1 | Tom | 18 | M | 90 |
| 2 | Mary | 19 | F | 88 |
| 1 | Tom | 18 | M | 95 |
| 2 | Mary | 19 | F | 92 |
| 3 | Jack | 18 | M | 80 |
可以使用下列 SQL 语句:
SELECT ID, NAME, GENDER,
SUM(SCORE) AS TOTAL_SCORE,
AVG(SCORE) AS AVG_SCORE,
COUNT(*) AS COUNTER
FROM SCORES
GROUP BY ID, NAME, GENDER;
结果如下表:
| ID | NAME | GENDER | TOTAL_SCORE | AVG_SCORE | COUNTER |
| 1 | Tom | M | 185 | 92.5 | 2 |
| 2 | Mary | F | 180 | 90.0 | 2 |
| 3 | Jack | M | 80 | 80.0 | 1 |
b. 行列转换
PIVOT 函数可以将行值转换为列值,这个功能在处理某些业务场景时很有用。例如,上面的分组表格,我们可能还需要将结果按照性别进一步划分,得到类似于下面这个表:
| ID | NAME | MALE_SCORE | FEMALE_SCORE |
| 1 | Tom | 185 | NULL |
| 2 | Mary | NULL | 180 |
| 3 | Jack | 80 | NULL |
可以使用下列 SQL 语句:
SELECT ID, NAME,
MAX(CASE WHEN GENDER = 'M' THEN SCORE ELSE NULL END) AS MALE_SCORE,
MAX(CASE WHEN GENDER = 'F' THEN SCORE ELSE NULL END) AS FEMALE_SCORE
FROM SCORES
GROUP BY ID, NAME;
结果如下表:
| ID | NAME | MALE_SCORE | FEMALE_SCORE |
| 1 | Tom | 185 | NULL |
| 2 | Mary | NULL | 180 |
| 3 | Jack | 80 | NULL |
c. 使用子查询和PIVOT子句完成复杂转换
在实际情况下,PIVOT 有时需要使用子查询来进行更复杂的转换操作,下面来一起看一个 SQL 语句实战:
SELECT *
FROM (
SELECT ID, NAME, GENDER, SCORE
FROM SCORES
)
PIVOT
(
AVG(SCORE) for GENDER IN ('M', 'F')
)
这个 SQL 查询操作完成了一个聚合操作,同时将平均值分别按照性别转换为了不同的列,从而完成了数据的透视表聚合。这个应用主要是针对复杂的、跨度广的数据结构,能够方便的将数据转为透视表形式。
四、PIVOT 的注意事项
a. 请避免混用 SUM 和 COUNT 聚合函数
在使用 PIVOT 的时候,如果混用 SUM 和 COUNT 这样的聚合函数,在生成的结果中会出现错误,因此需要避免使用这样的混用写法。
b. 请注意对缺失记录的处理
在使用 PIVOT 函数的时候,我们还需要注意对于缺失的记录需要进行处理,否则会导致 PIVOT 函数中出现空值 null,这样会影响到后续的计算。我们需要在写 PIVOT 函数之前,针对记录的缺失做好充分的预处理工作。
c. 请根据实际情况进行动态拼接
在实际情况中,针对不同的数据表格,需要使用不同的动态拼接操作,才能够最终得到想要的结果,而不是只使用一种固定的规则。因此,我们需要在实际操作中多进行尝试,积累一定的经验。
五、总结
PIVOT 是一种强大的 SQL 函数,它能够将普通的表格数据快速转换为透视表格形式,方便进行数据的查询和汇总,尤其是针对复杂的数据结构,PIVOT 会发挥出非常重要的作用。在实际工作中,针对具体的数据结构,我们需要对 PIVOT 函数进行不断的尝试和探索,多进行数据的拼接和转换,才能够得到更好的结果。