HiveSQL实战积累_行转列与列转行

发表于 | 分类于

HiveSQL实战积累_行转列与列转行

1.步骤拆分

无论是行转列还是列转行，都不是一步完成的，一般都需要先转化成合并到单个单元格的状态。比如行转列，需要先将一行中多列数据合并到同一列中，然后再进行竖向展开。同理列转行，需要先讲一列中多行数据合并到同一个行中，然后再进行横向展开。

1.1 多列合并到一个单元格

concat(字符串1/字段1,字符串2/字段2...)和concat_ws(分隔符,字符串1/字段1,字符串2/字段2...)，可以将多个字段拼接到一起组成一个字符串。

1.2 多行合并到一个单元格

collect_list(字段1)可以将字段1的所有合并到一起组成一个array类型的数据，存放在一个单元格中，该函数的参数只能是基本数据类型。

collect_set(字段1)可以将字段1的所以数据进行去重再合并到一起组成一个array类型的数据。

collect_list(字段1)和collect_set(字段1)通常与group by组合使用，用于将拥有不同属性值的行数据分别合并在一起。

如果还需要进一步将array类型的数据转化为方便使用的字符串类型，则再配合concat()函数或concat_ws()函数即可，如concat_ws(',',collect_set(字段1))。

1.3 一个单元格拆分为多列

split(字符串1/字段1,分隔符)可以将一个字符串类型数据单元格中的字符串拆分，转化为一个array类型数据单元格。

split(字符串1/字段1,分隔符)[index]可以从array中取出索引为index的基础类型数据，注意hive中split函数的index与java中一样是从0开始的，但是presto中split函数的index是从1开始的。

如果需要真正将一个单元格中的数据拆分成多列，最好该数据是约定好格式顺序。比如一个字段score就是语文=92,数学=99,英语=96，将其转化为多行可以使用如下语句：

1
2
3
4
select 
	split(split('语文=92,数学=99,英语=96',',')[0],'=')[1] as chinese,
	split(split('语文=92,数学=99,英语=96',',')[1],'=')[1] as math,
	split(split('语文=92,数学=99,英语=96',',')[2],'=')[1] as english

可以得到结果：

chinese math english
92 99 96

1.4 一个单元格拆分为多行

explode(array)可以将一个array类型单元格中的数据数据拆分成多行。

explode(array)通常与lateral view结合使用，lateral view侧视图的本质就是为左表的每一行调用explode(array)函数，然后再将拆分出来的多行与原表中的这一行进行无条件连接，得到数据爆炸的效果。常用语法如下：

1
select 原表字段1...,拆分字段别名 from 原表 lateral view explode (split(字符串/原表拆分字段,分隔符)) 虚拟表别名 as 拆分字段别名;

2.用户属性行转列实例

2.1 背景

有一张用户画像表，在一行数据中标记了用户的性别、年龄、省份等属性。现在需要按照不同属性的属性值来对用户进行group by，得到每种属性值的用户数。由于行式属性并不便于存储聚合得到的用户数数值，而列式枚举属性值就比较便于在聚合后直接将属性值与对应用户数以kv形式存储了。所以在此实例中我们需要将用户属性行转化为列式。

2.2 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT 
	usr_log_acct,
	SPLIT(feature_value ,'@_@')[0] AS feature_typ,
	SPLIT(feature_value ,'@_@')[1] AS feature_name
(
  SELECT 
  usr_log_acct,
 CONCAT_WS(',',cpp_base_sex,cpp_base_age,cpp_adde_province,cpp_base_ulevel,cgp_cust_purchpower,cfv_sens_promotion,cfv_sens_comment,cpp_addr_city) AS feature
	FROM (
    SELECT 
    	usr_log_acct,
    	CONCAT_WS('@_@' ,'1' ,cpp_base_sex) AS cpp_base_sex,
    	CONCAT_WS('@_@' ,'2' ,cpp_base_age) AS cpp_base_age,
    	CONCAT_WS('@_@' ,'3' ,cpp_addr_province) AS cpp_addr_province,
    	CONCAT_WS('@_@' ,'4' ,cpp_base_ulevel) AS cpp_base_ulevel,
    	CONCAT_WS('@_@' ,'5' ,cgp_cust_purchpower) AS cgp_cust_purchpower,
    	CONCAT_WS('@_@' ,'6' ,cfv_sens_promotion) AS cfv_sens_promotion,
    	CONCAT_WS('@_@' ,'7' ,cfv_sens_comment) AS cfv_sens_comment,
    	CONCAT_WS('@_@' ,'9' ,cpp_addr_city) AS cpp_addr_city 
    FROM
      adm.adm_l01_nonpolar_view_da_ori
  ) usr_feature
) usr_feature LATERAL VIEW EXPLODE(SPLIT(feature,',')) feature as feature_value

2.3 技术要点

技术步骤：使用concat_ws()函数多列数据合并到同一个单元格，然后lateral view将一个单元拆分为多行。

重要：在行转列之前多拼接一个序号，用来标识原表一行数据中的不同列。此处也就是标识不同属性，比如用’1’标识性别，用’2’标识年龄，那么后续我们在需要使用某一属性是，直接在where条件中卡feature_typ字段就行了。

3.Hive表单元格存储array格式数据

Hive表单元格可以存储array格式的数据到一个单元格中，如下建表语句所示：

1
2
3
4
5
CREATE EXTERNAL TABLE `adm.adm_s03_zs_vend_bu_mapping`(
    `vend_id` bigint COMMENT '商家ID', 
    `log_in_pins` array<string> COMMENT '登录用户pin列表（包括子Pin和主Pin）', 
    `open_sz_version` array<string> COMMENT '开通商智的版本列表（服务期内）')
COMMENT '商家-部门关系表'

然后可以直接使用如下sql对每一行数据只关联本行的该array单元格，实现单元格拆分为多行的爆炸操作：

1
2
3
4
5
6
7
8
SELECT
	vend_id,
	t1.pin AS pin
FROM 
	adm.adm_s03_zs_vend_bu_mapping lateral VIEW explode(log_in_pins) t1 AS pin
WHERE 
	dt >= '""" + lastmonthfirst + """'
	AND dt <= '""" + lastmonthlast + """'