行转列不再复杂:SQL高手都在用的技巧揭秘

Rocky大约 3 分钟

背景

在数据分析中,我们常常需要将纵向排列的数据转换为横向展示,以便更直观地进行分析或生成报告。这个过程就是 行转列,又称为 Pivot 操作。在 SQL 中,如何将行数据高效转换为列数据是一项非常实用的技巧,无论是生成动态报表,还是在数据透视分析中应用,都能够大显身手。

在这篇博文中,我将带你从零开始,深入理解 SQL 中行转列的原理,并通过常见的数据库示例逐步展示行转列的多种实现方式。

行转列是什么

行转列操作可以帮助我们将多行数据转换成不同的列,尤其在处理 数据透视 时非常有用。行转列广泛应用于需要汇总、整理数据的场景,如报表生成和业务分析。

假设有一张销售表 sales,记录了每个季度的销售额:

yearquarterrevenue
2023Q15000
2023Q27000
2023Q36000
2023Q48000

我们的目标是将不同季度的销售额变成每一年的列展示,比如:

yearQ1Q2Q3Q4
20235000700060008000

常见行转列方法

方法 1:使用 CASE WHEN 实现行转列

CASE WHEN 是行转列的经典方式,也是我最喜欢使用的方式,这个方式适用于大多数 SQL 数据库。


SELECT
  year,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY year;
  1. 通过 CASE WHEN 判断每一行属于哪个季度,使用 SUM 聚合数据,确保同一年内的季度销售额正确归类到各自的列中。
  2. GROUP BY 按年份分组,以获得每年的季度销售额汇总。

方法 2:使用 PIVOT 语句

某些数据库(如 SQL Server 和 Oracle)提供了专门的 PIVOT 语句来实现行转列。


SELECT *
FROM sales
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS pivot_table;

  1. PIVOT 语句自动将季度 (quarter) 转为列,并对销售额 (revenue) 进行汇总。
  2. 相比于 CASE WHENPIVOT 更加简洁,但并非所有数据库都支持。

总结

其实从我个人而言,更喜欢用case when的方式,这个方式更通用,也方便理解和记忆


系统推荐









  • 随机毒鸡汤:你以为你是灰姑娘吗?拜托别做梦了,她可是伯爵的女儿。