Hive中怎么行转列？怎么列转行？

得得得个桔

已于 2024-07-11 16:48:03 修改

阅读量437

点赞数 5

分类专栏：数据开发文章标签： hive 大数据 sql

于 2024-07-02 17:21:19 首次发布

本文链接：https://blog.youkuaiyun.com/degeju/article/details/140132076

版权

背景引入

首先，行转列列转行是数仓工作中还比较常见的问题，通常与Hive中的collect_list``collect_set``explode等相关知识挂钩。
其次，行转列的sql编写也是面试常考题之一，面试者需要重点关注。
不知道什么是行转列的话，可以看个简陋的例子：

table01:
uid		score						 uid		score_list
01			88			>>>>>		01			88,85,92
01			85			>>>>>		02			76,70
01			92
02			76
02			70

模拟数据

with data as (
  select "Tom" as uname, "传奇" as gamename, "20240203" as `date` union all
  select "Tom" as uname, "英雄联盟" as gamename, "20240203" as `date` union all
  select "Jack" as uname, "穿越火线" as gamename, "20240203" as `date` union all
  select "Jack" as uname, "穿越火线" as gamename, "20240203" as `date` union all
  select "Jack" as uname, "CSGO"