SQL技巧——行转列/列转行

在做特征工程的时候，会经常会碰到一个场景，比如手上有一张用户表user，记录了用户某款产品每一天各个功能的使用次数，存储方式类似key-value键值结构。具体如下：

用户使用行为统计表user

此时需要对用户进行建模，需要一张用户纬度的特征表，记录用户在每个功能的使用次数、占比的特征，这种情况就需要行转列的操作。而相应的有反操作，列转行。

行转列

• case when <expr> then <result>…else <default> end
• if(expr, true_result, false_result)

## 方法一
select uid
, max(case when fid = 100 then num else 0 end) as fid_100
, max(case when fid = 101 then num else 0 end) as fid_101
, max(case when fid = 103 then num else 0 end) as fid_103
from user group by uid

## 方法二
select uid
, max(if(fid = 100, num, 0)) as fid_100
, max(if(fid = 101, num, 0)) as fid_101
, max(if(fid = 103, num, 0)) as fid_103
from user group by uid

行转列结果表

除了以上根据条件判断来实现行转列的场景，有的场景是要把多行合并，比如有用户的评论流水表comment，此时我们想对同一个用户的所有评论拼接到一起，然后做文本分类。

用户评论流水表comment

• collect_list：把列聚合成为数组，不去重
• collect_set：把列聚合成为数据，去重
• concat_ws(separator, str1, str2, ...)：把多个字符串用分隔符进行拼接

select uid
, concat_ws(" ", collect_list(content)) as content
from comment group by uid

行转列结果表

（思考题：如果要按照时间sendtime来排序，那应该怎么实现呢？至少有两种解决思路）

评论按照sendtime排序的结果表

列转行

有的时候我们拿到像用户玩过的各种游戏的数据表，但是游戏是拼接到一起的，这个时候我们只想关注某一些游戏，需要过滤掉不关注的游戏，需要怎么办呢？这时候只需要对表进行列转行操作，然后再进行筛选即可。

用户玩过的游戏表user_game

• explode：处理数组结构的字段，转换成多行；
• LATERAL VIEW：其实explode是一个UDTF函数（一行输入多行输出），这个时候如果要select除了explode得到的字段以外的多个字段，需要创建虚拟表。

select uid
, game 
from user_game
LATERAL VIEW explode(split(game_list,",")) tmpTable as game
-- split(game_list,",")相对字符串切割，得到数组
-- game 是给 explode(split(game_list,",")) 列起的别名

结果表result

这个时候我们只要用select语句筛选关注的游戏即可。

（思考题：如果数据表中，用户和游戏是n对n的关系，应该怎么处理成1对1的关系呢？）

用户玩过的游戏表user_game