本文分享在Azure Databricks中如何实现行转列和列转行。

一，行转列

在分组中，把每个分组中的某一列的数据连接在一起：

• collect_list：把一个分组中的列合成为数组，数据不去重，格式是['a','a','b']
• collect_set：把一个分组中的列合成为集合，数据去重，格式是['a','b']

用于连接文本数组的函数，通过sep把数组中的item分割开，连接成一个字符串：

concat_ws(sep, [str | array(str)]+)

举个例子，把每个用户的game，通过逗号连接起来：

select uid
 ,concat_ws(",",collect_list(game)) as game_list 
from user_game

二，列转行

Explode(expr) 用于处理array和map结构的数据，把一行的列值转换成多行，该函数产生一个虚拟表，包含一行或多行数据，也就是说，Explode(expr)函数把array类型expr中的元素分成多行，或者将map类型的expr中的元素分成多行和多列。

举个例子，把game_list中的每个item转换为一行数据：

with cte_game as
(
  select uid
    ,collect_list(game) as game_list 
  from user_game
  group by uid
)
select uid
  ,explode(game_list) as game    
from cte_game

三，Lateral View子句

Lateral View子句用于连接表值函数（UDTF），比如explode、split 。Lateral View通过UDTF函数把数据拆分成多行，再把多行结果组合成一个虚拟表。

该子句主要解决的问题是：在select使用UDTF做查询的过程中，该查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。

LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

使用LATERAL VIEW + explode 函数进行查询，语句如下：

select movie,category_name 
from movie_info
LATERAL VIEW explode(category) tmpTable as category_name;
-- category_name 是给 explode(category) 列起的别名

 

 

参考文档：

掌握这个SQL技巧超越80%的人——行转列/列转行

SQL reference for Databricks Runtime 7.x