行转列不再复杂:SQL高手都在用的技巧揭秘
大约 3 分钟
背景
在数据分析中,我们常常需要将纵向排列的数据转换为横向展示,以便更直观地进行分析或生成报告。这个过程就是 行转列,又称为 Pivot 操作。在 SQL 中,如何将行数据高效转换为列数据是一项非常实用的技巧,无论是生成动态报表,还是在数据透视分析中应用,都能够大显身手。
在这篇博文中,我将带你从零开始,深入理解 SQL 中行转列的原理,并通过常见的数据库示例逐步展示行转列的多种实现方式。
行转列是什么
行转列操作可以帮助我们将多行数据转换成不同的列,尤其在处理 数据透视 时非常有用。行转列广泛应用于需要汇总、整理数据的场景,如报表生成和业务分析。
假设有一张销售表 sales
,记录了每个季度的销售额:
year | quarter | revenue |
---|---|---|
2023 | Q1 | 5000 |
2023 | Q2 | 7000 |
2023 | Q3 | 6000 |
2023 | Q4 | 8000 |
我们的目标是将不同季度的销售额变成每一年的列展示,比如:
year | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
2023 | 5000 | 7000 | 6000 | 8000 |
常见行转列方法
方法 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;
- 通过
CASE WHEN
判断每一行属于哪个季度,使用SUM
聚合数据,确保同一年内的季度销售额正确归类到各自的列中。 GROUP BY
按年份分组,以获得每年的季度销售额汇总。
方法 2:使用 PIVOT 语句
某些数据库(如 SQL Server 和 Oracle)提供了专门的 PIVOT
语句来实现行转列。
SELECT *
FROM sales
PIVOT (
SUM(revenue)
FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS pivot_table;
PIVOT
语句自动将季度 (quarter
) 转为列,并对销售额 (revenue
) 进行汇总。- 相比于
CASE WHEN
,PIVOT
更加简洁,但并非所有数据库都支持。
总结
其实从我个人而言,更喜欢用case when的方式,这个方式更通用,也方便理解和记忆
系统推荐
- Hadoop 一
- Thread & ExecutorService & ThreadPoolExecutor 总览
- kafka
- MySQL锁、事务、隔离级别
- ShadowsockServerUpdatePort
- Paxos算法
- InnoDB存储引擎
- index方法压力测试记录
- JetBrains-License-Server
- PGSQL的json和 jsonb 读写性能测试
- 前后端常用技术
- 免费 API 每日提供摸鱼日报,自动返回无水印图片,适用于公众号和小程序
- 随机毒鸡汤:你以为你是灰姑娘吗?拜托别做梦了,她可是伯爵的女儿。