526互联

ORACLE PIVOT函数

发布时间 2023-07-25 12:46:24作者: ROGER_FEDERER

 

语法

 

 

   函数用途： PIVOT函数被用于SELECT子句中，在查询时将行转换成列，并在由行转列时对对应数据进行聚合统计.一般使用PIVOT操作后结果集将比原结果集包含更多的列，以及更少的行。

   计算步骤：     

 

  • 1.PIVOT函数计算位于PIVOT子句起始位置的聚合函数。使用聚合函数必须要定义一个GOURP BY 子句以返回分组统计值，但在PIVOT函数中不显式使用GROUP BY 子句，实际上PIVOT函数使用隐式（默认）的 GROUP BY 子句。默认GROUP BY 使用的分组字段为在PIVOT子句中未使用到的所有字段，以及将要行转列的字段里所有的值。即除了聚合函数里以外的所有字段，以及比如将要行转列的字段（假设NAME字段)里的所有不同值，都是PIVOT函数里默认GROUP BY 使用的分组字段。如果在PIVOT函数中定义了多个聚合函数，那么则必须为除一个以外的所有其他聚合函数重命名（即只允许有一个聚合函数不进行重命名操作)。

  • 2.分组列以及聚合字段在第一步计算这后将以以下方式输出结果集：
       a .PIVOT函数中未出现的所有的隐式分组字段按以下方式计算展示：
       b.结果集中新输出的列与PIVOT函数中的PIVOT_IN_CLAUSE参数相关。PIVOT_IN_CLAUSE参数代表要转成列的行的所有不同值。比如要将NAME 字段里的行值：FEDERER,NADAL,NOVAK三个值转成三个字段（列），那么PIVOT_IN_CLAUSE参数位置即是FEDERER,NADAL,NOVAK值，最终这个参数里的所有值将被转换成列（字段）值。如果PIVOT函数里的XML参数值 被定了XML关键字，结果集将被输出为仅为一个字段的XML字符串。（关于PIVOT函数的含XML关键字的形式,以及与XML关键配合使用的expr,subquery,any参数，可自行参考文档，目前没用过，暂不叙述，后续有用到再补充)。PIVOT_IN_CLAUSE参数中可以为每个要转列并聚合计算的值重命名，不进行重命名时，其行值即为新列名称。注意ORACLE所有表、字段命名长度不能超过30 byte，否则会报ORA_00918错误。
       示例：
       通过安装oralce默认创建的oe.orders进行查询：

     

CREATE TABLE pivot_table AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM oe.orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));
SELECT * FROM pivot_table ORDER BY year;

   EXTRACT函数仅将ORDERDATE 字段转为年份形式。PIVOT函数将子表（三个字段：YEAR,ORDERMODE,ORDERTOTAL)中的ordertotal进行聚合（即aggregatefunction参数），要转为列的是ORDERMODE字段(即PIVOT_FOR_CLAUSE参数）中的'direct','online'两个行值，（即PIVOT_IN_CLAUSE参数）此时分组字段默认为YEAR,以及direct,online.即GROUP BY YEAR,DIRECT,ONLINE(GROUP BY 不支持别名)。

参考文档 ORALCE官网 SQL Language Reference：https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/About-SQL-Functions.html#GUID-D51AB228-518C-4213-8BD4-F919623D105E