SQL实用技巧-行列转换_SQL

在编写大数据SQL的时候，有时需要进行行列的转化。
什么是行列转化？如下图，不同商品在不同月份的销量数据，有时候我们希望数据和左侧一样的排列，但原始数据却像右侧一样排列，此时我们需要把右侧的列排列转换成左侧的行排列，反之亦然。

文章插图
行转列与列转行
下面以上面这个例子为大家介绍一些行列转换的方式。
行转列使用CASE WHEN适用场景：MySQL、Hive、Spark SQL 。
把行转换成列最简单的方式就是使用CASE WHEN 。
case month when '2024-01' then sales end的意思是当month的值为'2024-01'时取sales的值，其他情况取NULL，因此可以计算出不同月份的销量。

selectproduct,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_03fromsales_rowgroup by product

使用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>]...)) [...]

参数
是否必选
说明
aggregate function
是
聚合函数
alias
否
聚合函数的别名，别名和最终PIVOT处理过后生成的列名相关
column
是
指定转换为列的行值在源表中的列名称
value
是
指定转换为列的行值
new column
否
转换后新的列名称
直接看示例。
利用PIVOT把month列按值聚合出了三列month_01 ， month_02 ， month_03 。
select*fromsales_row PIVOT (MAX(sales) for month in('2024-01' as month_01,'2024-02' as month_02,'2024-03' as month_03))列转行使用UNION ALL适用场景：MySQL、Hive、Spark SQL 。
UNION ALL相当于取每一个列的值，然后并联在一起，注意'2024-01' as month中的2024-01是字符串。
使用UNION ALL的好处就是，无论是mysql、hive还是spark都支持，以不变应万变。
缺点就是当要关联列比较多时比较麻烦，如果要查询全年的数据，则需要UNION ALL 12次，如果是天数据则要UNION ALL 365次。

select*from(select product, '2024-01' as month, month_01 from sales_columnunion allselect product, '2024-02' as month, month_02 from sales_columnunion allselect product, '2024-03' as month, month_03 from sales_column)

仅使用EXPLODE适用场景：Spark SQL 。
explode可以将一个数组或者map分解成多行，例如：
select explode(split('A,B,C', ','))# 结果colABC

select explode(map('2024-01', 1000, '2024-02', 2000, '2024-03', 3000))# 结果keyvalue2024-01 10002024-02 20002024-03 3000

对于列转行的需求，可以先创建一个map之后再利用explode拆分成多行。
注意下面SQL中，explode函数返回值有两个，因此设置列别名时需要用as (month, sales) 。

selectproduct,explode(map('2024-01', month_01,'2024-02', month_02,'2024-03', month_03)) as (month, sales)fromsales_column

类似的思路还可以利用concat+trans_array等操作。
hive中的UDTF上面的方式仅适用于Spark 。
当使用UDTF函数（explode就是一个UDTF函数）的时候，Hive只允许对拆分字段进行访问。

select explode(map('2024-01', 1000, '2024-02', 2000, '2024-03', 3000))# 结果keyvalue2024-01 10002024-02 20002024-03 3000

也就是说在Hive中，上面SQL是没问题的，下面的SQL就会报错了

hive> selectproduct>,explode(map('2024-01', month_01, '2024-02', month_02, '2024-03', month_03))>fromsales_columnSemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

因此这块需要使用LATERAL VIEW功能来进行处理。LATERAL VIEW将explode生成的结果当做一个视图来处理。
使用Lateral View适用场景：Hive、Spark SQL 。