SQL 透視行轉列：全面指南

教學

SQL

將行轉換為列使分析師能夠將原始數據轉變為結構良好、意義明確的格式，這樣更容易進行解讀。它還有助於聚合和組織數據，以便於報告，改善決策過程，並揭示可能被忽視的趨勢。在金融、零售和醫療保健等領域，快速訪問組織良好的數據對於驅動重要的商業決策是非常有用的。

在本指南中，我將探索SQL樞紐技術的強大世界，並提供實用的範例及特定於數據庫的實施。如果您希望深入學習SQL技能，我推薦參加DataCamp的中級SQL課程，以了解數據聚合和數據分組。如果您是擁有分析師和工程師的商業利益相關者，考慮通過DataCamp的企業解決方案一次性提升大家的技能。

在SQL中，將行轉換為列意味著什麼？

在SQL中，樞紐操作是指將數據從基於行的格式轉換為基於列的格式。這種轉換對於報告和數據分析很有用，可以提供更結構化和緊湊的數據視圖。將行進行樞紐轉換還允許用戶以更清晰地突顯關鍵見解的方式分析和總結數據。

考慮以下示例：我有一個包含每日銷售交易的表，每行記錄日期、產品名稱和銷售金額。

Date	Product	Sales
2024-01-01	Laptop	100
2024-01-01	Mouse	200
2024-01-02	Laptop	150
2024-01-02	Mouse	250

通過對這個表進行樞紐操作，我可以將其重新結構化，將每個產品顯示為一列，在相應列下顯示每個日期的銷售數據。還要注意到進行了聚合。

Date	Laptop	Mouse
2024-01-01	100	200
2024-01-02	150	250

傳統上，樞紐操作需要複雜的SQL查詢和條件聚合。隨著時間的推移，SQL實現已經演變，許多現代數據庫現在包括PIVOT和UNPIVOT運算符，以實現更有效和簡單的轉換。

理解SQL樞紐行列轉換

SQL樞紐操作通過將行值轉換為列而轉換數據。以下是SQL樞紐的基本語法和結構，包括以下部分：

SELECT: SELECT語句引用要在SQL樞紐表中返回的列。
子查詢: 子查詢包含要包含在SQL樞紐表中的數據源或表。
PIVOT: 「PIVOT」運算子包含要應用於樞紐分析表中的聚合和篩選條件。

-- 選擇靜態列和樞紐列
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- 定義用於樞紐的源數據的子查詢
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- 對值列應用的聚合函數，創建新列
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

讓我們通過以下逐步示例來演示如何在SQL中將行樞紐到列。考慮以下的「SalesData」表。

使用 SQL PIVOT 操作符轉換的表格示例。圖片由作者提供。

我想將這些數據進行樞紐分析，以比較每個產品的每日銷售。我將開始選擇將結構化 PIVOT 操作符的子查詢。

-- 定義樞紐分析源數據的子查詢
SELECT Date, Product, Sales
FROM SalesData;

現在，我將使用 PIVOT 操作符將 Product 值轉換為列，並使用 SUM 操作符匯總 Sales。

-- 選擇每個產品的日期和樞紐列
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- 子查詢以獲取日期、產品和銷售列
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- 按產品匯總銷售，將產品值樞紐到列中
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

使用 SQL 將行轉換為列的示例輸出變換。圖片由作者提供。

將數據進行樞紐操作可簡化數據摘要，但這種技術存在潛在問題。以下是 SQL 樞紐的潛在挑戰以及如何應對這些問題。

動態列名：當要樞紐的值（例如產品類型）未知時，硬編碼列名是行不通的。一些數據庫（如 SQL Server）支持使用存儲過程的動態 SQL 來解決此問題，而其他數據庫可能需要在應用程序層處理此問題。
處理 NULL 值：當特定樞紐列沒有數據時，結果可能會包含 NULL。您可以使用 COALESCE 將 NULL 值替換為零或其他佔位符。
跨数据库兼容性:并非所有数据库直接支持PIVOT运算符。如果您的SQL方言不支持，您可以通过CASE语句和条件聚合来实现类似的结果。

SQL将行转列：示例和用例

根據使用的數據庫或其他需求，在SQL中使用不同的方法來樞紐數據。雖然PIVOT運算子通常用於SQL Server，但其他技術，如CASE語句，也允許進行類似的數據庫轉換，而無需直接支持PIVOT。我將介紹SQL中兩種常見的數據樞紐方法，並談談其優缺點。

使用PIVOT運算子

PIVOT運算子在SQL Server中提供了一種將行樞紐為列的直觀方法，通過指定聚合函數並定義要樞紐的列。

考慮以下名為sales_data的表。

要使用PIVOT運算子轉換的示例訂單表。作者提供圖片。

我將使用 PIVOT 操作符來聚合數據，以便每年的總 sales_revenue 顯示在列中。

-- 使用 PIVOT 按年份聚合銷售收入
SELECT *
FROM (
    -- 從源表中選擇相關的列
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- 聚合每年的銷售收入
    SUM(sales_revenue)
    -- 為每年創建列
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

使用 SQL PIVOT 的示例輸出轉換。圖片由作者提供。

使用 PIVOT 操作符具有以下優勢和限制：

優點: 當欄位正確索引時，這種方法效率高。它還具有簡單、更易讀的語法。
限制: 不是所有的資料庫都支援 PIVOT 運算子。它需要事先指定欄位，而動態樞紐需要額外的複雜度。

使用 CASE 語句的手動樞紐分析

您還可以使用 CASE 語句在不支援 PIVOT 操作符的資料庫中手動樞紐分析數據，例如 MySQL 和 PostgreSQL。此方法通過評估每一行並根據特定標準有條件地將值分配給新列來使用條件聚合。

例如，我們可以在相同的 sales_data 表中使用 CASE 語句手動樞紐分析數據。

-- 使用 CASE 語句按年份聚合銷售收入
SELECT 
    -- 計算每年的總銷售收入
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

使用SQL CASE语句进行输出转换的示例。作者提供的图片。

使用CASE语句进行转换具有以下优点和限制：

优点：该方法适用于所有SQL数据库，灵活地生成新列，即使产品名称未知或经常更改。
限制：如果要進行許多列的樞紐轉換，查詢可能變得複雜且冗長。由於存在多個條件檢查，此方法的執行速度略慢於 PIVOT 運算子。

將行轉換為列時的性能考量

在 SQL 中將行轉換為列可能會對性能產生影響，特別是在處理大型數據集時。以下是一些提示和最佳做法，可幫助您撰寫高效的樞紐查詢、優化其性能，並避免常見問題。

最佳做法

以下是優化查詢和提升性能的最佳實踐。

索引策略：適當的索引對於優化樞紐查詢至關重要，使SQL能夠更快地檢索和處理數據。始終對在WHERE子句中經常使用的列或進行分組的列進行索引，以減少掃描時間。
避免嵌套樞紐分析: 在一個查詢中堆疊多個樞紐操作可能會難以閱讀且執行速度較慢。通過將查詢拆分為幾個部分或使用臨時表來簡化。
限制樞紐中的列和行: 只需要樞紐列來進行分析，因為樞紐多個列可能會消耗資源並創建大型表格。

避免常見問題

以下是您在樞紐查詢中可能遇到的常見錯誤以及如何避免它們。

不必要的全表掃描：樞紐查詢可能觸發全表掃描，特別是如果沒有相應的索引。通過為關鍵列建立索引並在應用樞紐之前過濾數據來避免全表掃描。
頻繁樞紐運算使用動態 SQL：使用動態 SQL 可能會因查詢重編譯而降低性能。為了避免這個問題，緩存或限制動態樞紐到特定場景，並在可能的情況下考慮在應用層處理動態列。
在大型數據集上進行聚合而不進行預篩選：在大型數據集上使用聚合函數如SUM或COUNT可能會降低數據庫的效能。請在對整個數據集進行樞紐轉換之前，先使用WHERE子句對數據進行篩選。
樞紐列中的NULL值：進行樞紐操作時，當特定列沒有數據時通常會產生NULL值。這可能會減慢查詢速度並使結果更難解釋。為避免此問題，可以使用COALESCE等函數將NULL值替換為默認值。
僅使用示例數據進行測試: 由於內存和處理需求的增加，樞紐查詢在大數據集上可能表現不同。為了準確評估性能影響，請始終在真實或代表性數據樣本上測試樞紐查詢。

嘗試我們的SQL Server 開發人員職業軌道，涵蓋從交易和錯誤處理到改善查詢性能的所有內容。

特定於數據庫的實現

樞紐操作在各種數據庫（如 SQL Server、MySQL 和 Oracle）中有顯著差異。每個數據庫都具有特定的語法和限制。我將介紹在不同數據庫中進行數據樞紐操作的示例及其主要特點。

SQL Server

SQL Server 提供內建的 PIVOT 運算子，用於將行旋轉為列時非常直觀。 PIVOT 運算子易於使用，並與 SQL Server 強大的聚合函數集成。 SQL 中旋轉的關鍵功能包括以下內容：

直接支持 PIVOT 和 UNPIVOT： SQL Server 的 PIVOT 運算子允許快速將行轉換為列。 UNPIVOT 運算子也可以反轉此過程。
聚合選項： PIVOT 運算子允許各種聚合函數，例如 SUM、COUNT 和 AVG。

SQL Server 中 PIVOT 運算子的限制是它要求要轉置的列值必須事先知道，這使得它在動態變化的數據中不夠靈活。

在下面的示例中，PIVOT 操作符將 Product 值轉換為列並使用 SUM 操作符對 Sales 進行聚合。

-- 選擇日期和每個產品的樞紐列
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- 子查詢以提取日期、產品和銷售列
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- 按產品聚合銷售，將產品值樞紐到列
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

我建議參加DataCamp的 Introduction to SQL Server 課程，以掌握 SQL Server 用於數據分析的基礎知識。

MySQL

MySQL 不支持 PIVOT 操作符。但是，您可以使用 CASE 语句手动将行樞紐到列，並结合其他聚合函数如 SUM、AVG 和 COUNT。儘管這種方法很靈活，但如果需要樞紐的列很多，它可能變得很複雜。

以下查詢通過條件聚合每個產品的銷售數據，使用 CASE 陳述式實現與 SQL Server PIVOT 範例相同的輸出。

-- 選擇日期和每個產品的旋轉列
SELECT 
    Date,
    -- 使用 CASE 創建筆記本電腦和鼠標銷售的列
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle 支持 PIVOT 運算符，允許將行直接轉換為列。與 SQL Server 一樣，您需要明確指定轉換的列。

在以下查詢中，PIVOT 運算符將 ProductName 值轉換為列，並使用 SUM 運算符對 SalesAmount 進行聚合。

SELECT *
FROM (
    -- 選擇源數據
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- 按產品聚合銷售數據，創建旋轉列
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

在Oracle中使用SQL PIVOT操作符进行示例输出转换。图片由作者提供。

SQL中将行转换为列的高级技术

将行转换为列的高级技术在处理复杂数据时非常有用。动态技术和同时处理多列使您能够在静态旋转受限的情况下转换数据。让我们详细探讨这两种方法。

动态旋转

动态旋转允许您创建自动适应数据变化的旋转查询。当您的列频繁更改（例如产品名称或类别），并且希望查询自动包括新条目而无需手动更新时，此技术特别有用。

假設我們有一個 SalesData 表格，並且可以創建一個動態的透視表，當新產品被添加時會自動調整。在下面的查詢中，@columns 動態生成透視列的列表，而 sp_executesql 執行生成的 SQL。

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- 步驟 1：生成一個獨特產品的列表以進行透視
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- 步驟 2：構建動態 SQL 查詢
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- 步驟 3：執行動態 SQL
EXEC sp_executesql @sql;

處理多個列

在需要同時透視多個列的情況下，您將使用 PIVOT 操作符和額外的聚合技術，在同一查詢中創建多個列。

在下面的示例中，我已經透視了 Sales 和 Quantity 列，並以 Product 為依據。

按日期对笔记本电脑和鼠标的销售额和数量进行透视
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        销售额的透视
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        数量的透视
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

使用 SQL PIVOT 运算符来转换多列的示例输出。作者提供的图片。

将多列进行透视允许生成更详细的报告，通过为每个项目透视多个属性，从而实现更丰富的洞察。然而，语法可能会很复杂，特别是存在许多列时。可能需要硬编码，除非与动态透视技术相结合，从而增加进一步的复杂性。

结论

将行转换为列是值得学习的 SQL 技术。我曾看到 SQL 透视技术被用于创建队列保留表，您可以跟踪用户的保留情况。我还看到 SQL 透视技术被用于分析调查数据，其中每一行代表一个受访者，而每个问题可以被透视为其自己的列。

我們的SQL報告課程是您想要進一步了解如何總結和準備數據以便於展示和/或儀表板構建的絕佳選擇。我們的SQL助理數據分析師和SQL助理數據工程師職業路徑也是一個非常好的選擇，它們能為任何履歷增添很多亮點，因此請立即報名。

Source:
https://www.datacamp.com/tutorial/sql-pivot-rows-to-columns