數倉工具—Hive文法之行列轉換(18)

行列轉換

今天我們看一個比較常見的計算場景，行列互換，又時候有稱之為行列互轉

行專列 把多行資料轉換成多列資料

列轉行 把多列資料轉成多行資料

說起來還是有點抽象的，我們還是用一個例子說明一下這兩個概念到底是怎麼回事，解釋清楚之後我們再舉幾個例子

這是一個學生的成績表，這裡我們就隻有一個學生的資訊

學生ID	科目(subject)	分數(score)
1	math	140
1	chinese	145
1	english	143

行專列

下面就是我們行轉列侯的結果

學生ID	math	chinese	english
1	140	145	143

列轉行

下面就死我們能把上面 行專列 後的資料進行列轉行 的結果

學生ID	subject	score
1	math	140
1	chinese	145
1	english	143

行轉列

正常的行轉列

這裡我們使用 with 進行資料準備工作，select 是我們的核心部分

with data as(
select 1 as id,'math' as subject,140 as score
union all
select 1 as id,'chinese' as subject,145 as score
union all
select 1 as id,'english' as subject,143 as score
)
select
  id,
  max(if(subject='math',score,0)) as math,
  max(if(subject='chinese',score,0)) as chinese,
  max(if(subject='english',score,0)) as english
from
  data
group by
  id
;      

彙總資料的行轉列

上面的例子其實作實中并不常見，我們看一下現實中的需求是怎麼樣的

這是一個真實的需求，我們簡化一下，我們就看07.05～07.11 的打卡人數，一般情況下我們統計人數，直接按照時間進行group by 就好了

打卡時間	打卡人數
20210705	100
20210706	200

我們往往計算出來的結果是像上面這樣的，是以我們需要進行行轉列之後才能滿足我們的需求。

with data as(
  select 20210706 as time,1 as id
  union all
  select 20210706 as time,2 as id
  union all
  select 20210706 as time,3 as id
  union all
  select 20210707 as time,1 as id
  union all
  select 20210707 as time,2 as id
  union all
  select 20210707 as time,3 as id
)
select
  time,
  count(distinct id) as peo_cnt
from
  data
group by
  time
;      

這裡我們先彙總計算，計算出每天的人數

time    peo_cnt
+-----+--------+
20210706   3
20210707   3      

然後我們再對上面計算出來的資料進行行轉列

select
  max(if(time=20210706,peo_cnt,0)) as 0706cnt,
  max(if(time=20210707,peo_cnt,0)) as 0707cnt
from(
  select
    time,
    count(distinct id) as peo_cnt
  from
    data
  group by
    time
)group by
  1      

這樣我們就計算出來了，需要注意的是我們這裡的​

​group by 1​

​

0706cnt 0707cnt
+--------+--------+
3 3      

第一次優化

上面我們使用了一個​

​count(distinct id)​

​​ 其實這個文法是非常不高效的，因為隻會适應一個​

​mapper​

​,是以我們就這個寫法進行優化一下

select
  max(if(time=20210706,peo_cnt,0)) as 0706cnt,
  max(if(time=20210707,peo_cnt,0)) as 0707cnt
from(
  select
    time,
    count( id) as peo_cnt
  from
  (
    -- 使用group by 進行去重
    select
      time,id
    from
      data
    group by 
      time,id
    ) 
  group by
    time
)group by
  1      

第二次優化

我們發現就一個行轉列，還這麼麻煩，我們能不能在計算的時候就完成行轉列的過程，而不是後面再通過group by 來完成這個過程

select
count(if(time=20210706,id,null)) as 0706cnt,
count(if(time=20210707,id,null)) as 0707cnt
from
(
  -- 使用group by 進行去重
  select
    time,id
  from
    data
  group by
    time,id
)
;      

這裡我們使用if 配合count 來完成行轉列的過程,當然你可以使用count_if 函數來替代​

​count if​

​的組合

select
count_if(time=20210706) as 0706cnt,
count_if(time=20210707) as 0707cnt
from
(
  -- 使用group by 進行去重
  select
    time,id
  from
    data
  group by
    time,id
)
;      

GROUP BY + COLLECT_LIST/COLLECT_SET

其實這個組合容易被忽略，但是這也是行轉列的一種場景，這裡我們就不多介紹了，因為我們前面有文站單獨寫了COLLECT_LIST和COLLECT_SET 的用法和使用場景，請參考​​Hive文法之collect_set 和 collect_list​​

列轉行

union all 實作

因為列轉行很簡單，我們就直接使用union all 就可以了，是以我們這裡隻給一個例子

id  math  chinese english
+---+-----+--------+--------+
1 140 145 143      

這就是我們的資料，我們現在将它展開為多行

select
  id  學生ID,'math' as subject,math as score
from
  data2
union all
select
  id 學生ID,'chinese' as subject, chinese as score
from
  data2
union all
select
  id 學生ID, 'english' as subject,english as score
from
  data2
;
      

計算結果

學生id  subject score
+-----+--------+------+
1 math  140
1 chinese 145
1 english 143      

注意，這裡請使用union all 而不是union，因為這裡不需要去重

lateral view 實作

上面的實作我們發現了一個問題，那就是存在大量的union all，如果我們的合并字段很多的話，就需要維護大量的SQL ,是以這裡我們看一下lateral view 的實作

，關于lateral view 我們也有單獨的文站介紹，請參考​​Hive文法之explode 和 lateral view​​

select
  id,subject,score
from
  data2
LATERAL VIEW  explode(
    str_to_map(
      concat(
        'math=',cast(math as string),
        '#chinese=',cast(chinese as string),
        '#english=',cast(english as string)
      )
      ,'#','='
    )
) tmpTable as subject,score
;      

k1,v1
k2,v2
k3,v3      

總結

1. 行轉列除了我們使用的彙總函數+if之外還可以使用統計函數+case when
2. 其實很多人對行轉列和列轉行搞不清楚，多行變成一行的就是行轉列，因為多行變成了多列，一行變成多行的就是列轉行，因為多列變成了多行
3. 列轉行的時候請使用union all 而不是union，因為這裡不需要去重