HQL之行转列与列转行

行转列与列转行的概述

行转列函数和列转行函数本质上是聚合和拆分的两种类型的函数，我们将这些整理起来放在一起，一类成为行转列，一类称为列转行。

行转列

什么是行转列

把一行中不同列的数据放在同一列当中 (concat,concat_ws)
或者 把多行中不同行的数据转为一列

行转列函数

CONCAT

CONCAT(string a string b…) 返回输入字符串拼接后的结果，可以输入多个字符串。

CONCAT_WS

CONCAT_WS(separator,str1,str2,…) 第一个参数separator作为其他列的分隔符，其他参数必须为字符串或者字符串数组 分隔符为NULL 如果后面的参数存在 null 或者空字符串 则跳过这些参数

把同一行，不同列的合并为一个值

COLLECT_SET（a）

对一个基本数据类型字段进行去重汇总，产生array类型的字段

COLLECT_LIST（a）

对一个基本数据类型字段进行不去重汇总，产生array类型的字段

例子

数据

手机主人	品牌	颜色
小明	三星	红色
小红	苹果	红色
小蓝	三星	蓝色
小紫	三星	红色
小磊	苹果	红色
小强	三星	蓝色

需求

把手机品牌和颜色相同的人放到一起

实现

创建数据 phone.txt

建表并导入数据

create table person_info
(
    name  string,
    brand string,
    color string
)
    row format delimited fields terminated by " ";
load data local inpath "/opt/module/hive/datas/phone.txt" 
into table person_info;

查询

select
       t1.phoneAndColor,
       concat_ws(",",collect_set(t1.name)) names
from
(
    select
           name,
           concat_ws('&', brand, color) as phoneAndColor
    from person_info
)t1
group by t1.phoneAndColor

结果

列转行

什么是列转行

把统一行中多个不同的数据拿出来放在多行中，叫做列转行。

列转行函数

lateral view

lateral view： 侧视生成虚拟表放explode或者split之后的函数，然后在这里进行聚合。

explode

explode(col): hive中，用来将数据结构array和map进行结构的切分，切分成多行。

split

split(str,separator) 将字符串按照后面的分隔符切割，转换成字符array

例子

需求

将分组数据展开为名字 奖项

创建数据

建表并导入数据

create table reward_info(
    name string,
    reward string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/reward.txt" into table reward_info;

按照需求查询数据

select
    name,
    aa
from reward_info
lateral view
explode(split(reward,",")) a as aa;

用split把字符串切开返回一个array，然后用explode把这个array给炸开成多行。

查询结果