如何在 Oracle 中对结果进行 PIVOT?
问题:
您想在 Oracle 中对结果进行 PIVOT。
解决方案
PIVOT 子句是 Oracle Database 11g 的新增功能，它允许您将查询输出中的行翻转为列，同时还允许您对数据运行聚合函数。
PIVOT 对于查看大量数据中的总体趋势特别有用。
我们将使用销售数据来演示其用法。
示例
SELECT * FROM sales;
输出
12008 12 1998-02-28 6 37 552 1898.88 12008 18 1998-02-28 6 37 463 1592.72 12008 20 1998-02-28 6 37 2430 8359.2 12008 25 1998-03-31 6 36 3387 12091.59 12008 26 1998-01-31 6 37 2381 8190.64
Syntax for PIVOT.
示例
SELECT * FROM ( inner_query ) PIVOT ( aggregate_function FOR pivot_column IN (list_of_values) ) ORDER BY…;
我们将使用 PIVOT 查看几个月内各类产品的销售趋势。以下是相同的示例。
示例
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04) = 1998 ) PIVOT ( SUM(amount_sold) FOR prod_id IN (12008 , 12010 , 12011 ) ) ORDER BY month_val;
输出
01 145714.96 1039519.04 1719280.2 02 149309.76 1067082.24 1758706.44 03 1072859.97 8045916.84 13191209.28 04 154280.56 1106052.23 1826847.36 05 155322.88 1119728.52 1848467.64 06 1166464.25 8477308.28 13882822.54 07 157737.76 1145882.33 1891410.4 08 167073.92 1214760.69 2010620.5 09 1274290.22 8932017.99 15038327.33 10 160988.56 1180869.5 1956298.5 11 159736.4 1189188 1964635.2 12 1227572.64 8795186.2 14774142.24
我们还可以向 PIVOT 添加多个表。以下是示例。
示例
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04) = 1998 ) PIVOT ( SUM(amount_sold) FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));
此外，我们还可以在 PIVOT 查询中使用多个聚合函数。
示例
SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04)= 1998 ) PIVOT ( SUM(amount_sold),AVG(amount_sold) AS avg_amount FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));
上述问题陈述的表结构:
示例
CREATE TABLE "SALES" ( "PROD_ID" NUMBER NOT NULL ENABLE, "CUST_ID" NUMBER NOT NULL ENABLE, "TIME_ID" VARCHAR2(20) NOT NULL ENABLE, "CHANNEL_ID" NUMBER NOT NULL ENABLE, "PROMO_ID" NUMBER NOT NULL ENABLE, "QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE, "AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE ) ;
