SQL實(shí)用技巧-行列轉(zhuǎn)換
在編寫大數(shù)據(jù)SQL的時(shí)候，有時(shí)需要進(jìn)行行列的轉(zhuǎn)化。
什么是行列轉(zhuǎn)化？如下圖，不同商品在不同月份的銷量數(shù)據(jù)，有時(shí)候我們希望數(shù)據(jù)和左側(cè)一樣的排列，但原始數(shù)據(jù)卻像右側(cè)一樣排列，此時(shí)我們需要把右側(cè)的列排列轉(zhuǎn)換成左側(cè)的行排列，反之亦然。
行轉(zhuǎn)列與列轉(zhuǎn)行
下面以上面這個(gè)例子為大家介紹一些行列轉(zhuǎn)換的方式。
行轉(zhuǎn)列
使用CASE WHEN
適用場(chǎng)景：MySQL、Hive、Spark SQL。
把行轉(zhuǎn)換成列最簡(jiǎn)單的方式就是使用CASE WHEN。
case month when '2024-01' then sales end的意思是當(dāng)month的值為'2024-01'時(shí)取sales的值，其他情況取NULL，因此可以計(jì)算出不同月份的銷量。
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 product使用PIVOT
適用場(chǎng)景：Spark SQL。
PIVOT關(guān)鍵字對(duì)于指定的每一組行值，都會(huì)生成對(duì)應(yīng)的列。PIVOT關(guān)鍵字是FROM子句的一部分，可以和JOIN等其他關(guān)鍵字一同使用。
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>]
...
)
)
[...]參數(shù) | 是否必選 | 說(shuō)明 |
aggregate function | 是 | 聚合函數(shù) |
alias | 否 | 聚合函數(shù)的別名，別名和最終PIVOT處理過(guò)后生成的列名相關(guān) |
column | 是 | 指定轉(zhuǎn)換為列的行值在源表中的列名稱 |
value | 是 | 指定轉(zhuǎn)換為列的行值 |
new column | 否 | 轉(zhuǎn)換后新的列名稱 |
直接看示例。
利用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
)
)列轉(zhuǎn)行
使用UNION ALL
適用場(chǎng)景：MySQL、Hive、Spark SQL。
UNION ALL相當(dāng)于取每一個(gè)列的值，然后并聯(lián)在一起，注意'2024-01' as month中的2024-01是字符串。
使用UNION ALL的好處就是，無(wú)論是mysql、hive還是spark都支持，以不變應(yīng)萬(wàn)變。
缺點(diǎn)就是當(dāng)要關(guān)聯(lián)列比較多時(shí)比較麻煩，如果要查詢?nèi)甑臄?shù)據(jù)，則需要UNION ALL 12次，如果是天數(shù)據(jù)則要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
)僅使用EXPLODE
適用場(chǎng)景：Spark SQL。
explode可以將一個(gè)數(shù)組或者map分解成多行，例如：
select explode(split('A,B,C', ','))
# 結(jié)果
col
A
B
Cselect explode(map('2024-01', 1000, '2024-02', 2000, '2024-03', 3000))
# 結(jié)果
key value
2024-01 1000
2024-02 2000
2024-03 3000對(duì)于列轉(zhuǎn)行的需求，可以先創(chuàng)建一個(gè)map之后再利用explode拆分成多行。
注意下面SQL中，explode函數(shù)返回值有兩個(gè)，因此設(shè)置列別名時(shí)需要用as (month, sales)。
select product
,explode(
map('2024-01', month_01,
'2024-02', month_02,
'2024-03', month_03)
) as (month, sales)
from sales_column類似的思路還可以利用concat+trans_array等操作。
hive中的UDTF
上面的方式僅適用于Spark。
當(dāng)使用UDTF函數(shù)（explode就是一個(gè)UDTF函數(shù)）的時(shí)候，Hive只允許對(duì)拆分字段進(jìn)行訪問(wèn)。
select explode(map('2024-01', 1000, '2024-02', 2000, '2024-03', 3000))
# 結(jié)果
key value
2024-01 1000
2024-02 2000
2024-03 3000也就是說(shuō)在Hive中，上面SQL是沒(méi)問(wèn)題的，下面的SQL就會(huì)報(bào)錯(cuò)了
hive> select product
> ,explode(map('2024-01', month_01, '2024-02', month_02, '2024-03', month_03))
> from sales_column
SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions因此這塊需要使用LATERAL VIEW功能來(lái)進(jìn)行處理。LATERAL VIEW將explode生成的結(jié)果當(dāng)做一個(gè)視圖來(lái)處理。
使用Lateral View
適用場(chǎng)景：Hive、Spark SQL。
lateral view為側(cè)視圖，意義是為了配合UDTF來(lái)使用，把某一行數(shù)據(jù)拆分成多行數(shù)據(jù)。
Hive中不加lateral view的UDTF只能提取單個(gè)字段拆分。加上lateral view就可以將拆分的單個(gè)字段數(shù)據(jù)與原始表數(shù)據(jù)關(guān)聯(lián)上。
LATERAL VIEW [ OUTER ] generator_function ( expression [ , ... ] ) [ table_alias ] AS column_alias [ , ... ]參數(shù) | 是否必選 | 說(shuō)明 |
generator_function | 是 | 將一行數(shù)據(jù)拆成多行數(shù)據(jù)的UDTF (EXPLODE, INLINE等) |
table_alias | 否 | UDTF結(jié)果的別名 |
columnAlias | 是 | 拆分后得到的列的別名 |
直接看如何利用lateral view實(shí)現(xiàn)列轉(zhuǎn)行。
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, sales其中explode(map('2024-01', month_01, '2024-02', month_02, '2024-03', month_03))把map分解成多行。
lateral view同時(shí)指定了這個(gè)側(cè)視的表名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
# 模擬結(jié)果，lateral view不能單獨(dú)使用
month sales
2024-01 1000
2024-02 1100
2024-03 1200
2024-01 1100
2024-02 1000
2024-03 1400此時(shí)select product, t_view.month, t_view.sales就能達(dá)成UDTF拆分的單個(gè)字段數(shù)據(jù)與原始表數(shù)據(jù)關(guān)聯(lián)的效果了。
select product, t_view.month, t_view.sales
from sales_column
# 結(jié)果
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 1400使用UNPIVOT
適用場(chǎng)景：Spark 3.4+。
UNPIVOT關(guān)鍵字對(duì)于指定的每一組列，都會(huì)生成對(duì)應(yīng)的行。其中UNPIVOT關(guān)鍵字是FROM子句的一部分，可以和JOIN關(guān)鍵字等其他關(guān)鍵字一同使用。
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>] ...)]]
...
)
)
[...]參數(shù)說(shuō)明如下：
參數(shù) | 是否必選 | 說(shuō)明 |
new column of value | 是 | 轉(zhuǎn)換后新生成的列名稱，該列的值由指定轉(zhuǎn)換為行的列的值填充。 |
new column of name | 是 | 轉(zhuǎn)換后新生成的列名稱，該列的值由指定轉(zhuǎn)換為行的列名稱填充。 |
column | 是 | 指定轉(zhuǎn)換為行的列名稱，列的名稱用來(lái)填充new column of name；列的值用來(lái)填充new column of value。 |
column value | 否 | 指定轉(zhuǎn)換為行的列的別名 |
也是直接看示例：
select *
from sales_column
UNPIVOT (
sales for month in (month_01 as '2024-01', month_02 as '2024-02', month_03 as '2024-03')
)sales for month in (month_01, month_02, month_03)的意思就是生成一個(gè)新列sales，這一列的值是month_01, month_02, month_03這三列的值。
生成一個(gè)新列month， 這里一列的值是month_01, month_02, month_03這三列的列名，即'2024-01', '2024-02', '2024-03'。
