SQL 實用技巧 - 行列轉換
在編寫大數據 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 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>]
...
)
)
[...]直接看示例
利用 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
)
)列轉行
使用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_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
適用場景：Spark SQL
explode可以將一個數組或者 map 分解成多行，例如
select explode(split('A,B,C', ','))
# 結果
col
A
B
Cselect explode(map('2024-01', 1000, '2024-02', 2000, '2024-03', 3000))
# 結果
key value
2024-01 1000
2024-02 2000
2024-03 3000對於列轉行的需求，可以先創建一個 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_column類似的思路還可以利用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 3000也就是說在 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]: UDTF's are not supported outside the SELECT clause, nor nested in expressions因此這塊需要使用 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 [ , ... ]直接看如何利用 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, sales其中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 1400此時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 1400使用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>] ...)]]
...
)
)
[...]參數說明如下：
也是直接看示例
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)的意思就是
生成一個新列 sales，這一列的值是 month_01, month_02, month_03 這三列的值
生成一個新列 month， 這裏一列的值是 month_01, month_02, month_03 這三列的列名，即'2024-01', '2024-02', '2024-03'
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/bZMsoiDWtj41f4I-qWkdaA