如何在 SQL 中创建 Pivot 表-编程实验室

**REGION**(RegionID,RDescription)**TERRITORIES**(TerritoryID,TDescription,RegionID@)**CATEGORIES**(CategoryID,CategoryName,Description)**SUPPLIERS**(SupplierID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone)**CUSTOMERS**(CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone)**SHIPPERS**(ShipperID,CompanyName,Phone)**PRODUCTS**(ProductID,ProductName,SupplierID@,CategoryID@,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)**EMPLOYEES**(EmployeeID,LastName,FirstName,Title,BirthDate,HireDate,Address,City,RegionID@,PostalCode,Country,HomePhone,Extension,ReportsTo@)**EMPLOYEETERRITORIES**(EmployeeID@,TerritoryID@)**ORDERS**(OrderID,CustomerID@,EmployeeID@,TerritoryID@,OrderDate,RequiredDate,ShippedDate,ShipVia@,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry)**ORDERDETAILS**(OrderID@,ProductID@,UnitPrice,Quantity,Discount)

如果你不熟悉 SQL*Plus，建议在开始之前查看 Oracle 的SQL*Plus 快速入门。

不再赘述，让我们开始吧！

使用“DECODE”的数据透视表

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/74a9451ab9ff3d7249015797d4488746.png

图片来源：Jean-Philippe Delberghe于Unsplash

最原始的数据透视表方法是利用函数：DECODE()。DECODE()函数类似于 if else 语句。它将输入与每个值进行比较，并产生一个输出。

DECODE(input, value1, return1, value2, return2, …, default)

input/value：“input”将与所有“values”进行比较。
return：如果输入值等于某个值，则“return”是输出结果。
默认（可选）：如果输入 != 所有值，则输出为“默认”。

当我们知道 DECODE()如何工作时，就该制作我们的第一个透视表了。

第一版：不带总计列和总计行的透视表

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/01c28e4ecd251f365a719f825518832c.png

不带总计列和总计行的透视表，来源：我

使用 DECODE()，我们可以为冰淇淋店老板绘制一个透视表的伪代码。当“星期几”与每个工作日匹配时，DECODE()返回当天的收入；如果不匹配，则返回 0。

SELECT ice cream flavor,SUM(DECODE(day of the week,'Monday',revenue,0))AS MONDAY,SUM(DECODE(day of the week,'Tuesday',revenue,0))AS TUESDAY,SUM(DECODE(day of the week,'Wednesday',revenue,0))AS WEDNESDAY,SUM(DECODE(day of the week,'Thursday',revenue,0))AS THURSDAY,SUM(DECODE(day of the week,'Friday',revenue,0))AS FRIDAY,SUM(DECODE(day of the week,'Saturday',revenue,0))AS SATURDAY,SUM(DECODE(day of the week,'Sunday',revenue,0))AS SUNDAY FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday;

第二版：带有总计列和总计行的透视表

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/3a58fe26e6b3c13ffbe6a4ba3bddd4a5.png

带有总计列和总计行的透视表，来源：我

这可以通过在 GROUP BY 语句中使用GROUPING SETS 表达式来实现。GROUPING SETS 表达式定义了多个 GROUP BY 聚合的标准。

GROUPING SETS (属性 1，…，())

属性：单个元素或用于 GROUP BY 的元素列表
()：一个空的组，将成为透视表的总计行

SELECT NVL(ice cream flavor,'TOTAL')"ICE CREAM FLAVOR",SUM(DECODE(day of the week,'Monday',revenue,0))AS MONDAY,SUM(DECODE(day of the week,'Tuesday',revenue,0))AS TUESDAY,SUM(DECODE(day of the week,'Wednesday',revenue,0))AS WEDNESDAY,SUM(DECODE(day of the week,'Thursday',revenue,0))AS THURSDAY,SUM(DECODE(day of the week,'Friday',revenue,0))AS FRIDAY,SUM(DECODE(day of the week,'Saturday',revenue,0))AS SATURDAY,SUM(DECODE(day of the week,'Sunday',revenue,0))AS SUNDAY,SUM(revenue)AS TOTAL FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday GROUP BY GROUPING SETS(ice cream flavor,());

注意：NVL()将由()创建的空值行替换为“TOTAL”。如果你不熟悉NVL()，它只是一个用来替换空值的函数。

计算总计列的另一种方法是将从周一到周日的所有收入加起来：

SUM(DECODE(day of the week,'Monday',revenue,0))+SUM(DECODE(day of the week,'Tuesday',revenue,0))+SUM(DECODE(day of the week,'Wednesday',revenue,0))+SUM(DECODE(day of the week,'Thursday',revenue,0))+SUM(DECODE(day of the week,'Friday',revenue,0))+SUM(DECODE(day of the week,'Saturday',revenue,0))+SUM(DECODE(day of the week,'Sunday',revenue,0))AS TOTAL

第三版：带有总计列和总计行及其他总计的透视表

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/b51145e9666531e26b0b454773076572.png

带有总计列和总计行及其他总计的透视表，来源：我

假设冰淇淋店老板想要在你提供的透视表中再加一列：每种口味冰淇淋的购买总数。没问题！你可以用相同的概念再添加一个“TOTAL”列！

SELECT NVL(ice cream flavor,'TOTAL')"ICE CREAM FLAVOR",SUM(DECODE(day of the week,'Monday',revenue,0))AS MONDAY,SUM(DECODE(day of the week,'Tuesday',revenue,0))AS TUESDAY,SUM(DECODE(day of the week,'Wednesday',revenue,0))AS WEDNESDAY,SUM(DECODE(day of the week,'Thursday',revenue,0))AS THURSDAY,SUM(DECODE(day of the week,'Friday',revenue,0))AS FRIDAY,SUM(DECODE(day of the week,'Saturday',revenue,0))AS SATURDAY,SUM(DECODE(day of the week,'Sunday',revenue,0))AS SUNDAY,SUM(revenue)AS TOTAL,SUM(purchase ID)"OTHER TOTAL"FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday GROUP BY GROUPING SETS(ice cream flavor,());

现在你已经知道如何使用 DECODE()做透视表了，接下来让我们尝试三个关于 Northwind 数据集的练习吧！

Q1. 假设我们想找出每个原籍国的员工在各个区域的服务情况。

为了拆解这个问题，首先，我们可以查询 REGION 表中的所有不同区域，并检查员工来自哪些国家。

SELECT DISTINCT REGIONID||' '||RDescription AS REGION FROM REGION ORDER BY1;

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/3627116e1d54ed0917364c2a13a43fe5.png

SELECT DISTINCT Country FROM EMPLOYEES ORDER BY1;

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/80132a8d40c683c0db3caebce861de5a.png

我们将需要为这个问题制作一个 2 * 4 的透视表。

接下来，我们可以使用 DECODE()来制作透视表。下面概述了一个示例答案和输出：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/2961edeea876bc9ccf18d701f9e086b6.png

SELECT NVL(Country,'TOTAL')AS COUNTRY,SUM(DECODE(LOWER(REGIONID||' '||RDescription),'1 eastern',1,0))"1 EASTERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'2 western',1,0))"2 WESTERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'3 northern',1,0))"3 NORTHERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'4 southern',1,0))"4 SOUTHERN",SUM(EmployeeID)AS TOTAL FROM EMPLOYEES JOIN REGION USING(REGIONID)GROUP BY GROUPING SETS(Country,());

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/1c753723386af8721f3b7d2ea9cb6ec6.png

--Q1 SELECT Country,SUM(DECODE(LOWER(REGIONID||' '||RDescription),'1 eastern',1,0))"1 EASTERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'2 western',1,0))"2 WESTERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'3 northern',1,0))"3 NORTHERN",SUM(DECODE(LOWER(REGIONID||' '||RDescription),'4 southern',1,0))"4 SOUTHERN",SUM()AS TOTAL FROM EMPLOYEES JOIN REGION USING(REGIONID)GROUP BY Country;

Q2. 对于 2010 年中的每个月，显示每个员工处理的订单收入。此外，四舍五入到最接近的美元，并显示总收入和订单总数。

--Q2 COLUMN EMPLOYEE FORMAT A18 SELECT NVL(EmployeeID||' '||FirstName||' '||LastName,'TOTAL')AS EMPLOYEE,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),1,(UnitPrice*Quantity-Discount),0)),'$990')AS JAN,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),2,(UnitPrice*Quantity-Discount),0)),'$990')AS FEB,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),3,(UnitPrice*Quantity-Discount),0)),'$990')AS MAR,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),4,(UnitPrice*Quantity-Discount),0)),'$990')AS APR,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),5,(UnitPrice*Quantity-Discount),0)),'$990')AS MAY,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),6,(UnitPrice*Quantity-Discount),0)),'$990')AS JUN,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),7,(UnitPrice*Quantity-Discount),0)),'$99,990')AS JUL,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),8,(UnitPrice*Quantity-Discount),0)),'$99,990')AS AUG,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),9,(UnitPrice*Quantity-Discount),0)),'$99,990')AS SEP,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),10,(UnitPrice*Quantity-Discount),0)),'$99,990')AS OCT,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),11,(UnitPrice*Quantity-Discount),0)),'$99,990')AS NOV,TO_CHAR(SUM(DECODE(EXTRACT(MONTH FROM OrderDate),12,(UnitPrice*Quantity-Discount),0)),'$99,990')AS DEC,TO_CHAR(SUM((UnitPrice*Quantity-Discount)),'$999,990')AS TOTAL FROM ORDERS JOIN ORDERDETAILS USING(OrderID)JOIN EMPLOYEES USING(EmployeeID)WHERE EXTRACT(YEAR FROM OrderDate)=2010GROUP BY GROUPING SETS(EmployeeID||' '||FirstName||' '||LastName,())ORDER BY1;

注意：请注意，FORMAT 命令和 TO_CHAR()函数是用于格式化目的。如果你想了解更多信息，请查看 Oracle 网站上的格式模型和格式化 SQL*Plus 报告部分。

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/20c3ea352b3155d73bf77da29769b3dc.png

使用“PIVOT”的透视表

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/93e298803a661c1d579b990d24846251.png

图片由Noah Windler拍摄，来源于Unsplash

现在你已经知道如何使用 DECODE()创建透视表，我们可以继续介绍 Oracle 在 11g 版本中引入的 PIVOT()子句。

SELECT *
FROM (查询)
PIVOT (aggr FOR 列 IN (value1, value2, …)
);

aggr: 函数，如 SUM、COUNT、MIN、MAX 或 AVG
value: 用于列的值列表，这些值将转换为交叉表查询结果中的标题。

让我们回到冰淇淋店的例子。以下是如何使用 PIVOT()子句来实现：

第一版：没有总计列和行的透视表

SELECT*FROM(SELECT day of the week,ice cream flavor,revenue FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday)PIVOT(SUM(revenue)FOR day of the week IN('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'));

第二版：带有总计列和行的透视表

如果你想在透视表中添加一个总计列，使用 NVL()函数是一个很好的方法。

SELECT*FROM(SELECT NVL(ice cream flavor,'TOTAL')AS ice cream flavor,NVL(day of the week,-1)AS DOW,SUM(revenue)AS REV FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday GROUP BY CUBE(ice cream flavor,day of the week))PIVOT(SUM(REV)FOR DOW IN('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday',-1AS TOTAL));

第三版：带有总计列和行以及其他总计的透视表

当其他总计出现时，只有一种方法可以解决问题，那就是使用 JOIN()子句。

SELECT ice cream flavor,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,TOTAL,OTHER TOTAL FROM(SELECT NVL(ice cream flavor,'TOTAL')AS ice cream flavor,NVL(day of the week,-1)AS DOW,SUM(revenue)AS REV FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday GROUP BY CUBE(ice cream flavor,day of the week))PIVOT(SUM(REV)FOR DOW IN('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday',-1AS TOTAL))JOIN(SELECT NVL(ice cream flavor,'TOTAL')AS ice cream flavor,SUM(purchase ID)"OTHER TOTAL"FROM ice cream shop dataset WHERE date between last Mondayandlast Sunday GROUP BY ROLLUP(ice cream flavor))USING(ice cream flavor);