在編寫大數據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
C

select 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'

閱讀原文：原文鏈接