HQL之常用的行列转换应用总结

目录

0 工作场景

1 案例实现

1.1 多行转多列

1.1.1 需求

1.1.2 实现原理

1.1.3 实现

1.2 多行转单列

1.1.1 需求

1.2.2实现原理

1.1.3 实现

 1.3 多列转多行

 1.3.1 需求

1.3.2 实现原理

1.3.3 实现

1.4 单列转多行

1.4.1 需求

1.4.2 实现原理

1.4.3 实现

2 小结

---

0 工作场景

实际工作场景中经常需要实现对于Hive中的表进行行列转换操作，例如当前ADS层的数据表，我们统计得到每个小时不同维度下的UV、PV、IP的个数，而现在为了构建可视化报表，得到每个小时的UV、PV的线图，观察访问趋势，我们需要构建如下的表结构：

趋势图如下：

1 案例实现

1.1 多行转多列

1.1.1 需求

• 原始数据表

• 目标结果表

1.1.2 实现原理

多行转多列主要利用case when 语句进行实现。

• case when判断
• 功能

用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于Java中的switch case 功能

• 语法

（1）语法一

CASE WHEN 条件1 THEN VALUE1 WHEN 条件2 THEN VALUE2 …… WHEN 条件N THEN VALUEN ELSE 默认值 END

（2）语法二

CASE 列 WHEN V1 THEN VALUE1 WHEN V2 THEN VALUE2 …… WHEN VN THEN VALUEN ELSE 默认值 END

（3）测试

• 语法一：当id < 2显示a，当id = 2 显示b ,其他的显示c

select   id,   case   when id < 2 then 'a'   when id = 2 then 'b'   else 'c'   end as caseName from tb_url;

• 语法二：当id =1 显示a，当id = 2 显示b ,其他的显示c

select   id,   case id   when 1 then 'a'   when 2 then 'b'   else 'c'   end as caseName from tb_url;

1.1.3 实现

• 创建原始数据表，加载数据

--切换数据库 use db_function; --建表 create table row2col1(    col1 string,    col2 string,    col3 int ) row format delimited fields terminated by '\t'; --加载数据到表中 load data local inpath '/export/data/r2c1.txt' into table row2col1;

• SQL实现转换

select   col1 as col1,   max(case col2 when 'c' then col3 else 0 end) as c,   max(case col2 when 'd' then col3 else 0 end) as d,   max(case col2 when 'e' then col3 else 0 end) as e from   row2col1 group by   col1;

1.2 多行转单列

1.1.1 需求

• 原始数据表

• 目标数据表

1.2.2实现原理

利用Hive中的concat或caoncat_ws与collect_list或collect_set进行实现

• concat 

1. 功能：用于实现字符串拼接，不可指定分隔符
2. 语法

concat(element1,element2,element3……)

•   3.测试

select concat("it","cast","And","heima"); +-----------------+ | itcastAndheima  | +-----------------+

• 4 特点：如果任意一个元素为null，结果就为null

select concat("it","cast","And",null); +-------+ | NULL  | +-------+

• concat_ws

1. 功能：用于实现字符串拼接，可以指定分隔符
2. 语法

concat_ws(SplitChar，element1，element2……)

•     3.测试

select concat_ws("-","itcast","And","heima"); +-------------------+ | itcast-And-heima  | +-------------------+

•      4 特点：任意一个元素不为null，结果就不为null

select concat_ws("-","itcast","And",null); +-------------+ | itcast-And  | +-------------+

• collect_list

1. 功能：用于将一列中的多行合并为一行，不进行去重
2. 语法

collect_list（colName）

• 3.测试

select collect_list(col1) from row2col1; +----------------------------+ | ["a","a","a","b","b","b"]  | +----------------------------+

• 4. 特点

    collect_list不会去重，会忽略null值

• collect_set

1. 功能：用于将一列中的多行合并为一行，并进行去重
2. 语法

collect_set（colName）

• 3.测试

select collect_set(col1) from row2col1; +------------+ | ["b","a"]  | +------------+

•    4 .特点

 会去重，会忽略NULL值

1.1.3 实现

1. 创建原始数据表，加载数据

--切换数据库 use db_function; --建表 create table row2col2(    col1 string,    col2 string,    col3 int )row format delimited fields terminated by '\t'; --加载数据到表中 load data local inpath '/export/data/r2c2.txt' into table row2col2;

• 2.SQL实现转换

select   col1,   col2,   concat_ws(',', collect_list(cast(col3 as string))) as col3 from   row2col2 group by   col1, col2;

 1.3 多列转多行

 1.3.1 需求

• 原始数据表

• 目标结果表

1.3.2 实现原理

   union

1. 功能：将多个select语句结果合并为一个，且结果去重且排序
2. 语法

select_statement UNION [DISTINCT] select_statement UNION [DISTINCT] select_statement ...

• 3.测试

select 'b','a','c' union select 'a','b','c' union  select 'a','b','c';

•  4 特点

    对数据进行去重,重复的数据不进行合并，相当于union distinct ,效率较低

union all

1. 功能：将多个select语句结果合并为一个，且结果不去重不排序
2. 语法

select_statement UNION ALL select_statement UNION ALL select_statement ...

•     3.测试

select 'b','a','c' union all select 'a','b','c' union all  select 'a','b','c';

• 4. 特点

      对数据不进行去重，效率要高

1.3.3 实现

1. 创建原始数据表，加载数据

--切换数据库 use db_function; --创建表 create table col2row1 (   col1 string,   col2 int,   col3 int,   col4 int ) row format delimited fields terminated by '\t'; --加载数据 load data local inpath '/export/data/c2r1.txt'  into table col2row1;

1. SQL实现转换

select col1, 'c' as col2, col2 as col3 from col2row1 UNION ALL select col1, 'd' as col2, col3 as col3 from col2row1 UNION ALL select col1, 'e' as col2, col4 as col3 from col2row1;

1.4 单列转多行

1.4.1 需求

• 原始数据表

• 目标结果表

1.4.2 实现原理

利用UDTF函数lateral view + explode函数

• explode

1. 功能：用于将一个集合或者数组中的每个元素展开，将每个元素变成一行（类似于flatmap()函数）
2. 语法

explode( Map | Array)

• 3.测试

select explode(split("a,b,c,d",","));

•     4.特点

   UDTF函数，将一变多，类似于spark算子flatmap()，一般结合split()函数使用

1.4.3 实现

• 1.创建原始数据表，加载数据

--切换数据库 use db_function; --创建表 create table col2row2(    col1 string,    col2 string,    col3 string )row format delimited fields terminated by '\t'; --加载数据 load data local inpath '/export/data/c2r2.txt' into table col2row2;

• 2.SQL实现转换

select   col1,   col2,   lv.col3 as col3 from   col2row2     lateral view   explode(split(col3, ',')) lv as col3;

2 小结

行列转换是数据开发者的基本技能，也是面试重点，在数据开发中处处可见，本文对常见的几种情况进行了总结，包括了多行转多列，多行转单列，多列转多行，单列转多行的几种情况，并给出了解决思路和方法，读者可根据本文给出的思路灵活应对业务中遇到的问题。