SQL 将行旋转为列：全面指南

在SQL中将行数据透视为列数据是什么意思？

SQL中的数据透视指的是将数据从基于行的格式转换为基于列的格式。这种转换对报告和数据分析很有用，可以提供更结构化和紧凑的数据视图。将行数据透视为列还允许用户以突出关键见解的方式分析和总结数据。

考虑以下示例：我有一个包含每日销售交易的表，每行记录日期、产品名称和销售金额。

通过对这个表进行数据透视，我可以重新构造它，将每个产品显示为一列，每个日期的销售数据显示在相应的列下。还要注意聚合的发生。

传统上，透视操作需要复杂的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中使用不同的方法来透视数据。虽然在SQL Server中通常使用PIVOT运算符，但其他技术，如CASE语句，也可以实现类似的数据库转换，而无需直接支持PIVOT。我将介绍SQL中两种常见的透视数据方法，并讨论其优缺点。

使用PIVOT运算符

在SQL Server中，PIVOT运算符提供了一种直接的方法，通过指定聚合函数和定义要透视的列来将行透视为列。

考虑以下名为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语句手动进行数据旋转

您还可以在不支持PIVOT运算符的数据库（如MySQL和PostgreSQL）中使用CASE语句手动旋转数据。该方法通过评估每一行并根据特定条件有条件地为新列分配值来使用条件聚合。

例如，我们可以使用CASE语句在相同的sales_data表中手动旋转数据。

-- 使用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值。这可能会降低查询速度并使结果更难解释。为避免此问题，使用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。

PIVOT 运算符在 SQL Server 中的限制是需要提前知道要进行数据透视的列值，这使其对动态变化的数据不够灵活。

在下面的示例中，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的《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运算符和额外的聚合技术在同一查询中创建多列。

在下面的示例中，我已经按Product透视了Sales和Quantity列。

 - 按日期对笔记本电脑和鼠标的销售额和数量进行透视
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数据工程师助理职业发展路径也是一个很好的主意，它们可以极大地丰富任何简历，所以立即报名吧。