- 当前位置：首页 >探索 >SQL实用技巧-行列转换
SQL实用技巧-行列转换
发布时间：2025-11-05 09:42:58 来源：创站工坊 作者：应用开发
在编写大数据SQL的用技时候，有时需要进行行列的巧行转化。
什么是列转行列转化？如下图，不同商品在不同月份的用技销量数据，有时候我们希望数据和左侧一样的巧行排列，但原始数据却像右侧一样排列，列转此时我们需要把右侧的用技列排列转换成左侧的行排列，反之亦然。巧行
行转列与列转行
下面以上面这个例子为大家介绍一些行列转换的列转方式。
行转列
使用CASE WHEN
适用场景：MySQL、用技Hive、巧行Spark SQL。列转
把行转换成列最简单的用技方式就是使用CASE WHEN。
case month when 2024-01 then sales end的巧行意思是当month的值为2024-01时取sales的值，其他情况取NULL，列转因此可以计算出不同月份的销量。
复制select product ,max(case month when 2024-01 then sales end) as month_01 ,max(case month when 2024-02 then sales end) as month_02 ,max(case month when 2024-03 then sales end) as month_03 from sales_row group by product1.2.3.4.5.6.使用PIVOT
适用场景：Spark SQL。
PIVOT关键字对于指定的每一组行值，服务器托管都会生成对应的列。PIVOT关键字是FROM子句的一部分，可以和JOIN等其他关键字一同使用。
复制SELECT ... FROM ... PIVOT ( <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ... FOR (<column> [, <column>] ...) IN ( (<value> [, <value>] ...) AS <new column> [, (<value> [, <value>] ...) AS <new column>] ... ) ) [...]1.2.3.4.5.6.7.8.9.10.11.12.参数
是否必选
说明
aggregate function
是
聚合函数
alias
否
聚合函数的别名，别名和最终PIVOT处理过后生成的列名相关
column
是
指定转换为列的行值在源表中的列名称
value
是
指定转换为列的行值
new column
否
转换后新的列名称
直接看示例。
利用PIVOT把month列按值聚合出了三列month_01，month_02，month_03。
复制select * from sales_row PIVOT ( MAX(sales) for month in( 2024-01 as month_01, 2024-02 as month_02, 2024-03 as month_03 ) )1.2.3.4.5.6.7.8.9.列转行
使用UNION ALL
适用场景：MySQL、Hive、Spark SQL。
UNION ALL相当于取每一个列的值，然后并联在一起，注意2024-01 as month中的2024-01是字符串。
使用UNION ALL的好处就是，b2b信息网无论是mysql、hive还是spark都支持，以不变应万变。
缺点就是当要关联列比较多时比较麻烦，如果要查询全年的数据，则需要UNION ALL 12次，如果是天数据则要UNION ALL 365次。
复制select * from ( select product, 2024-01 as month, month_01 from sales_column union all select product, 2024-02 as month, month_02 from sales_column union all select product, 2024-03 as month, month_03 from sales_column )1.2.3.4.5.6.7.8.仅使用EXPLODE
适用场景：Spark SQL。
explode可以将一个数组或者map分解成多行，例如：
复制select explode(split(A,B,C, ,)) # 结果 col A B C1.2.3.4.5.6.7. 复制select explode(map(2024-01, 1000, 2024-02, 2000, 2024-03, 3000)) # 结果 key value 2024-01 1000 2024-02 2000 2024-03 30001.2.3.4.5.6.7.对于列转行的需求，可以先创建一个map之后再利用explode拆分成多行。
注意下面SQL中，explode函数返回值有两个，因此设置列别名时需要用as (month, sales)。
复制select product ,explode( map(2024-01, month_01, 2024-02, month_02, 2024-03, month_03) ) as (month, sales) from sales_column1.2.3.4.5.6.7.类似的思路还可以利用concat+trans_array等操作。
hive中的UDTF
上面的方式仅适用于Spark。
当使用UDTF函数（explode就是一个UDTF函数）的时候，Hive只允许对拆分字段进行访问。
复制select explode(map(2024-01, 1000, 2024-02, 2000, 2024-03, 3000)) # 结果 key value 2024-01 1000 2024-02 2000 2024-03 30001.2.3.4.5.6.7.也就是说在Hive中，上面SQL是没问题的高防服务器，下面的SQL就会报错了
复制hive> select product > ,explode(map(2024-01, month_01, 2024-02, month_02, 2024-03, month_03)) > from sales_column SemanticException [Error 10081]: UDTFs are not supported outside the SELECT clause, nor nested in expressions1.2.3.4.5.因此这块需要使用LATERAL VIEW功能来进行处理。LATERAL VIEW将explode生成的结果当做一个视图来处理。
使用Lateral View
适用场景：Hive、Spark SQL。
lateral view为侧视图，意义是为了配合UDTF来使用，把某一行数据拆分成多行数据。
Hive中不加lateral view的UDTF只能提取单个字段拆分。加上lateral view就可以将拆分的单个字段数据与原始表数据关联上。
复制LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]1.参数
是否必选
说明
generator_function
是
将一行数据拆成多行数据的UDTF (EXPLODE, INLINE等)
table_alias
否
UDTF结果的别名
columnAlias
是
拆分后得到的列的别名
直接看如何利用lateral view实现列转行。
复制select product, t_view.month, t_view.sales from sales_column lateral view explode( map(2024-01, month_01, 2024-02, month_02, 2024-03, month_03) ) t_view as month, sales1.2.3.4.5.其中explode(map(2024-01, month_01, 2024-02, month_02, 2024-03, month_03))把map分解成多行。
lateral view同时指定了这个侧视的表名t_view和两列的列名month 、sales。
复制lateral view explode( map(2024-01, month_01, 2024-02, month_02, 2024-03, month_03) ) t_view as month, sales # 模拟结果，lateral view不能单独使用 month sales 2024-01 1000 2024-02 1100 2024-03 1200 2024-01 1100 2024-02 1000 2024-03 14001.2.3.4.5.6.7.8.9.10.11.12.此时select product, t_view.month, t_view.sales就能达成UDTF拆分的单个字段数据与原始表数据关联的效果了。
复制select product, t_view.month, t_view.sales from sales_column # 结果 product month sales A 2024-01 1000 A 2024-02 1100 A 2024-03 1200 B 2024-01 1100 B 2024-02 1000 B 2024-03 14001.2.3.4.5.6.7.8.9.10.11.使用UNPIVOT
适用场景：Spark 3.4+。
UNPIVOT关键字对于指定的每一组列，都会生成对应的行。其中UNPIVOT关键字是FROM子句的一部分，可以和JOIN关键字等其他关键字一同使用。
复制SELECT ... FROM ... UNPIVOT ( <new column of value> [, <new column of value>] ... FOR (<new column of name> [, <new column of name>] ...) IN ( (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)] [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]] ... ) ) [...]1.2.3.4.5.6.7.8.9.10.11.12.参数说明如下：
参数
是否必选
说明
new column of value
是
转换后新生成的列名称，该列的值由指定转换为行的列的值填充。
new column of name
是
转换后新生成的列名称，该列的值由指定转换为行的列名称填充。
column
是
指定转换为行的列名称，列的名称用来填充new column of name；列的值用来填充new column of value。
column value
否
指定转换为行的列的别名
也是直接看示例：
复制select * from sales_column UNPIVOT ( sales for month in (month_01 as 2024-01, month_02 as 2024-02, month_03 as 2024-03) )1.2.3.4.5.sales for month in (month_01, month_02, month_03)的意思就是生成一个新列sales，这一列的值是month_01, month_02, month_03这三列的值。
生成一个新列month， 这里一列的值是month_01, month_02, month_03这三列的列名，即2024-01, 2024-02, 2024-03。
- 如何在戴尔电脑上安装Win7系统（戴尔电脑安装Win7系统教程及注意事项）
- MapReduce源码解析--环形缓冲区
- 成就完美 CTO 的“六脉神剑”
- Java帝国之宫廷内斗
- 香港销售索尼手机（索尼手机在香港市场占有率提高，质量成为用户选择的关键）
- JavaScript 运算符规则与隐式类型转换详解
- 前端性能优化以及解决方案
- jQuery === 面条式代码？
- 其实在使用VPS的时候很多也用过面板来管理和维护网站，使用过KLOXO以及Virtualmin/Webmin面板，相比较而言还是KLOXO耗费的资源比较小。昨天晚上的时候，在一个VPS群里看到有朋友提到安装Froxlor面板更加节省资源。今天我先不把这几个面板做对比，先自己安装一下。国人站长比较常用的是centos以及安装的LNMP环境，老左也和大家一起学习一下，不知道成功与否，边做边记录。对于Apache服务器，有众多控制面板可供选择，例如最流行的cPanel、Direct Admin和Plesk，免费的也有webmin和kloxo，可是对于高性能的Nginx服务器却暂时没有发现有一个比较好的解决方案。Froxlor是一个网页版本的服务器后台控制面板，支持Apache， Lighttpd和Nginx等多服务器。界面清爽，而且操作后台的面板还支持中文,需要快速配置请使用froxlor一键安装包安装。安装 Froxlor本安装教程安装系统环境为 Ubuntu 15.04。你必须安装如 MySQL、Apache、或Python包等所有先决条件。添加 froxlor 库、更新及安装命令：复制代码代码如下:sudo /etc/init.d/apache2 restart 打开浏览器并输入：http://IP地址/froxlor重要提示：到了这一步，出现Web服务器的404错误，我只需要移动froxlor目录，froxlor包默认安装在/var/WWW移动到/var/www/html，然后打开浏览器继续，点击安装按钮。验证所有要求是否满足与否，然后点击继续。提供所需的Mysql，FQDN，管理等凭证，点击下一步。确保所有的服务都是功能性的，点击下一步。登录的用户名是：admin，密码安装时指定由你。仪表板将类似于下面。OK，安装完毕，现在你可以看看里面的具体功能了。
- 2017年开发者生态报告：Java最火，Go最有前途
- 电脑BIOS时间错误的原因及解决方法（解决电脑BIOS时间错误的关键措施及建议）
- JavaScript运算符规则与隐式类型转换详解
- ARKit & OpenGL ES - ARKit 原理及实现
- 写的代码又被喷？8招让你的代码更Pythonic
- 技嘉GM-M6800鼠标的性能和使用体验（一款值得推荐的高性价比鼠标）
- 一个由正则表达式引发的血案
- 阿里5位嘉宾3个分享：HBaseCon Asia 2017干货满满
- XML的传奇人生
- 想知道在Linux中你正在使用的网卡是什么吗？ 在Linux中很容易就找出网卡的生产商。打开一个终端并输入下面的额命令：复制代码代码如下:sudo lshw -C network假如上面的命令不能在sudo下使用，那就别用 sudo 的特权模式。它的输出看上去有点奇怪但是很有用。复制代码代码如下: *-network description: Wireless interface product: BCM4360 802.11ac Wireless Network Adapter vendor: Broadcom Corporation physical id: 0 bus info: pci@0000:03:00.0 logical name: wlan0 version: 03 serial: 9c:f3:87:c1:5d:6a width: 64 bits clock: 33MHz capabilities: busmaster caplist ethernet physical wireless configuration: broadcast=yes driver=wl0 driverversion=6.30.223.248 (r487574) ip=192.168.1.23 latency=0 multicast=yes wireless=IEEE 802.11abg resources: irq:18 memory:b0600000-b0607fff memory:b0400000-b05fffff如你所见，我Macbook Air上的无线网卡是BCM4360，这是一款在Ubuntu下面很容易出现无法检测无线网络问题的网卡。lshw 命令实际上是用来列出硬件的，因此命令的名字是lshw。带上网络的选项后，就会只过滤出网络硬件了。了解网卡的其他方法另外你还可以使用lspci命令来显示PCI总线上的信息。你应该使用普通用户来运行这个命令。只需要在命令行下输入：复制代码代码如下: lspci命令的输出看上去想这样：复制代码代码如下: 00:00.0 Host bridge: Intel Corporation Haswell-ULT DRAM Controller (rev 09) 00:02.0 VGA compatible controller: Intel Corporation Haswell-ULT Integrated Graphics Controller (rev 09) 00:03.0 Audio device: Intel Corporation Haswell-ULT HD Audio Controller (rev 09) 00:14.0 USB controller: Intel Corporation 8 Series USB xHCI HC (rev 04) 00:16.0 Communication controller: Intel Corporation 8 Series HECI #0 (rev 04) 00:1b.0 Audio device: Intel Corporation 8 Series HD Audio Controller (rev 04) 00:1c.0 PCI bridge: Intel Corporation 8 Series PCI Express Root Port 1 (rev e4) 00:1c.1 PCI bridge: Intel Corporation 8 Series PCI Express Root Port 2 (rev e4) 00:1c.2 PCI bridge: Intel Corporation 8 Series PCI Express Root Port 3 (rev e4) 00:1c.4 PCI bridge: Intel Corporation 8 Series PCI Express Root Port 5 (rev e4) 00:1c.5 PCI bridge: Intel Corporation 8 Series PCI Express Root Port 6 (rev e4) 00:1f.0 ISA bridge: Intel Corporation 8 Series LPC Controller (rev 04) 00:1f.3 SMBus: Intel Corporation 8 Series SMBus Controller (rev 04) 02:00.0 Multimedia controller: Broadcom Corporation Device 1570 03:00.0 Network controller: Broadcom Corporation BCM4360 802.11ac Wireless Network Adapter (rev 03) 04:00.0 SATA controller: Marvell Technology Group Ltd. 88SS9183 PCIe SSD Controller (rev 14)这些命令会同时列出有线和无线的网卡。你应该注意到上面的输出中显示我的系统中没有有线网卡。因为我使用的是Macbook Air，它没有以太网端口
- Kaldi现在提供TensorFlow集成
随便看看
- Copyright © 2025 Powered by SQL实用技巧-行列转换,创站工坊 滇ICP备2023006006号-44sitemap