数仓工具—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
;

这里我们使用了一个函数叫做 str_to_map，其实就是将输入的字符串转化成一个map,然后传递给explode 函数，然后就会生成一个这样的一个临时表

k1,v1
k2,v2
k3,v3

然后这个临时表配合LATERAL VIEW 完成关联操作。

总结

1. 行转列除了我们使用的汇总函数+if 之外还可以使用统计函数+case when
2. 其实很多人对行转列和列转行搞不清楚，多行变成一行的就是行转列，因为多行变成了多列，一行变成多行的就是列转行，因为多列变成了多行
3. 列转行的时候请使用union all 而不是union，因为这里不需要去重