您的位置:

Oracle 行转列函数 PIVOT

一、概述

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 函数进行不断的尝试和探索,多进行数据的拼接和转换,才能够得到更好的结果。