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

  • 0
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值