hive多行转为一列，按指定分隔符分割

最新推荐文章于 2023-08-03 16:10:28 发布

原创最新推荐文章于 2023-08-03 16:10:28 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#hive多行转一行 #列转行

hive 专栏收录该内容

13 篇文章

订阅专栏

本文介绍了一种SQL方法，用于汇总用户在特定服务提供商（SP）处订阅的产品名称，并使用逗号作为分隔符。通过group by子句按用户ID和序列号对数据进行分组，并使用concat_ws函数将多个产品名称合并为一个字符串。

表:user_sp

user_id	serial_number	SP_product_name
A1	110	彩铃
A2	112	漏话提醒
A1	110	早间新闻
A2	112	天气预报

把用户订购所有SP_product_name汇总，并以","进行分割

select USER_ID,SERIAL_NUMBER,concat_ws(',',collect_list(SP_PRODUCT_NAME)) as PRODUCT_NAME
from user_sp group by USER_ID,SERIAL_NUMBER

---- collect_list 把字段值汇总成list
---- concat_ws  以指定分隔符进行分割

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

AA赵师傅

关注关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

HIVE多行转多列和多列转多行

zhengzaifeidelushang的博客

02-20

2899

1、HIVE多行转多列源数据样式把CAMERA_NO，RESULT_DATA两列转换为CAMERA_NO字段的数据为列名，RESULT_DATA字段对应CAMERA_NO的数据为值方法一：利用str_to_map函数 alter table ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new add if not exists par...

hive 行转列/列转行多行转一行/一行转多行

qq_28603127的博客

05-20

7337

mysql跟hive列转行/行转列一样,但是多行转一行,一行转多行就不太一样了链接:mysql 行转列,多行转一行,列转行,一行转多列 hive 启动hive时 ./hive -S (去除MR打印日志) hive命令行 set hive.exec.mode.local.auto=true; 设置本地模式数据准备: create table student_score(s_id int,s_name string,s_sub string,s_score bigint); insert into stu

参与评论您还未登录，请先登录后发表或查看评论

Hive的行列转换（行转多列、多列转行、行转单列、单列转行）

qq_43709558的博客

01-11

2651

hive

大数据hive操作行列转换之单列转多行

东城庞太师

02-06

986

原始数据： a b 1,2,3 c d 4,5,6 建表： create table col2row_21(col1 string,col2 string,col3 string) row format delimited fields terminated by '\t'; 表结构如下： +------------------+-----------------...

Hive和Oracle的多行转单列

mlwise的专栏

07-11

705

1. Hive多行转单列，即行转列一般行转列，Hive用concat_ws指定分隔符来拼接字符串，如果需要去重的话，还需要用collect_set，如果不需要去重，则可以用collect_list替代。模拟代码 select t1.ID, concat_ws(';',collect_set(cast(t1.name as string))) name from user_list t1 group by ID 2. Oracle的多行转单列，即行转列 Oracle的行...

Hive 行列转换

weixin_30745641的博客

12-21

407

一、列转行 (对某列拆分，一列拆多行) 使用函数：lateral view explode(split(column, ',')) num eg: 如表：t_row_to_column_tmp 数据如下，对tag列进行拆分 SQL代码： select id,tag,tag_new from t_row_to_column_tmp lateral view explode(spl...

Hive中将数据一行转多行、多行转一行、再转为以逗号分隔的string类型

some_apples的博客

06-15

5007

Hive中将数据一行转多行、多行转一行、再转为以逗号分隔的string类型背景表content中有观看内容id（content_id），内容标签id列表（tag_id_list），如下所示： content_id tag_id_list 1 1000,1001,1002 2 1000,1003 3 1002,1003 4 1002,1004 表id_decode中有内容标签id（tagid），内容标签中文（tagname），如下所示： tagid tagname

hivesql 列转行，并用逗号分隔

Wflowerd的博客

07-07

1883

hivesql 列转行

odps TRANS_ARRAY 列一行转多行

I_very_vegetable的博客

02-10

4100

odps函数trans_array根据分隔符列数据一行转多行

hive 多列转单行

yonghutwo的专栏

05-17

4692

Hive行列转换

weixin_30379911的博客

08-29

208

Hive行列转换 1、行转列 (根据主键，进行多行合并一列) 使用函数：concat_ws(‘,’,collect_set(column)) collect_list 不去重 collect_set 去重 column 的数据类型要求是 string 1.1、构建测试数据 vi row_to_col.txt a b 1 a b...

Hive之行转列-合并多列-列转行

热门推荐

onway_goahead的博客

03-28

1万+

行转列：collect_list(不去重) collect_set(去重) 它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重 concat_ws(',',collect_set(column)) 做简单的实验加深理解，创建一张实验用表，存放用户每天点播视频的记录： 1 2 3 4 5 ...

Hive 多列转行（if函数）

weixin_43370502的博客

10-09

1834

最近有个项目需要做数据导出(基于底层宽表)，具体业务逻辑在这就不详细介绍，以下是具体实现方案：列转换 –方案一 case when 与max 或sum select * from (select dzgs_dqbm, max(bs_wks) bs_wks, max(bs_jxz) bs_jxz, max(bs_ywc) bs_ywc, max(bs_wks_ztdm) wks_ztdm, max...

Hive SQL 行转列和列转行 1列转多列函数和示例

终码一生

09-27

3051

起因最近收到一个需求，做一张宽表的清洗处理，基于一张表格做条件，于是便把表格直接抽为一张字典表，用于对基础数据做match匹配。表格数据如下：整理后表数据如下（仅作演示，省略了其他条件字段）： hive> select arch_value, industry from dim.company_arch_value_condition_tag limit 10; OK 高 IT互联网_IT信息技术高金融_银行业|金融_保险业|金融_资本市场_证券期货高教育高商贸高（除上

hive转置数据(多列转换为多行)

Junz

12-19

4740

1.目标：将如下表格的多列数据 id yes no 001 21 89 002 11 65 转换为下表的多行形式： id bool value 001 yes 21 001 no 89 002 yes 11 002 no 65 2.实现： ...

hive 列转行行转列

littlecarton的博客

08-14

1743

1. explode explode就是将hive一行中复杂的 array 或者 map 结构拆分成多行。 hive wiki对于expolde的解释如下： explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTF...

HIVE中的行转列和列转行

weixin_43200379的博客

05-20

332

列转行多列转多行编码准确达标量上传达标量 k01 5 3 k02 7 2 目标：将准确达标量和上传达标量统一到一列中。编码达标量类型 k01 5 准确 k01 3 准确 k02 7 上传 k02 2 上传

hive中行转列，列转行的各种情况及解决方法

lbg20211023的博客

08-03

996

行转列：一般用case when,再做一个group by 去掉0值。如果应用多行 lateral view explode则做笛卡尔积。列转行：union all。

sql 按换行符分割成多个字段

最新发布

03-24

<think>嗯，用户的问题是关于如何在SQL中按换行符拆分字符串为多列或多行。首先，我需要回忆一下SQL中处理字符串拆分的方法。根据之前学过的知识，不同的数据库系统可能有不同的函数，比如MySQL的SUBSTRING_INDEX，或者PostgreSQL的regexp_split_to_table。另外，用户提到换行符，可能需要处理不同的换行符表示，比如\n或者\r\n。然后，用户提供的引用内容里有提到使用LENGTH和REPLACE函数来计算分隔符的数量，这可能用于确定拆分的次数。例如，计算换行符的数量，然后循环生成对应的行数。但具体怎么应用到拆分多列或多行呢？拆分多列的话，可能需要将字符串按换行符分成多个部分，每个部分作为一列。例如，使用SUBSTRING_INDEX函数，取第一个换行符前的部分作为第一列，第二个换行符前的部分作为第二列，依此类推。但这种方法需要提前知道最多有多少个换行符，或者动态处理，这在SQL中可能比较麻烦，尤其是列数不固定的话。而拆分为多行的话，可能更适合使用递归CTE或者数据库特定的函数，比如HIVE中的explode函数。引用[3]提到HIVE的explode函数可以将数组或map转换成多行，这可能适用于拆分后的数组。不过用户的问题是关于SQL的，可能需要通用的方法或者特定数据库的解决方案。用户提供的引用[2]中提到了单行变多行的方法，可能涉及到使用split函数将字符串拆分为数组，然后通过unnest或者lateral join来展开数组为多行。例如，在PostgreSQL中，可以使用regexp_split_to_table按换行符拆分字符串，生成多行记录。另外，考虑换行符在不同系统中的表示，可能需要处理不同的转义字符，比如\n或者\r\n，这时候可能需要使用正则表达式来匹配不同的换行符情况。总结下来，可能需要分情况讨论，如果是拆分为多列，使用SUBSTRING_INDEX或类似函数，逐个提取；如果是拆分为多行，使用递归CTE或特定函数如regexp_split_to_table或explode。同时，需要注意不同数据库系统的差异，比如MySQL、PostgreSQL、Hive等可能有不同的实现方式。此外，用户提供的引用[1]中提到用LENGTH和REPLACE计算分隔符个数，这可以用于确定拆分的次数，比如在递归CTE中作为终止条件。例如，先计算换行符的数量，然后循环拆分每个部分。最后，可能需要给出示例代码，并说明不同数据库的适配情况，同时提醒用户注意换行符的处理和性能问题，比如大数据量时的递归效率。</think>### SQL按换行符拆分字符串的实现方法在SQL中实现按换行符（`\n`或`\r\n`）拆分字符串为多列或多行，需根据数据库类型选择不同方法。以下是两种典型场景的实现思路： --- #### **一、拆分为多列（横向拆分）** 适用于已知最大行数的情况，例如将前3行内容拆分为3列。 **示例（MySQL语法）：** ```sql SELECT SUBSTRING_INDEX(content, '\n', 1) AS line1, SUBSTRING_INDEX(SUBSTRING_INDEX(content, '\n', 2), '\n', -1) AS line2, SUBSTRING_INDEX(SUBSTRING_INDEX(content, '\n', 3), '\n', -1) AS line3 FROM table; ``` **原理**：通过`SUBSTRING_INDEX`函数逐层截取换行符之间的内容。若字段行数不固定，需结合分隔符数量动态生成列[^1]。 --- #### **二、拆分为多行（纵向拆分）** 适用于将单行数据扩展为多行，常用递归CTE或内置函数实现。 **1. 通用方法（支持递归的数据库如PostgreSQL/SQL Server）：** ```sql WITH RECURSIVE split_cte AS ( SELECT content, 1 AS pos, SUBSTRING_INDEX(content, '\n', 1) AS line FROM table UNION ALL SELECT content, pos + 1, SUBSTRING_INDEX(SUBSTRING_INDEX(content, '\n', pos + 1), '\n', -1) FROM split_cte WHERE pos <= LENGTH(content) - LENGTH(REPLACE(content, '\n', '')) + 1 ) SELECT line FROM split_cte; ``` **原理**：通过递归计算换行符数量（`LENGTH(content) - LENGTH(REPLACE(content, '\n', ''))`）确定循环次数[^1]，逐行提取内容。 **2. 特定数据库优化（如Hive）：** ```sql SELECT exploded_line FROM table LATERAL VIEW explode(split(content, '\n')) t AS exploded_line; ``` **原理**：利用`split`将字符串转为数组，再通过`explode`展开为多行[^3]。 --- ### **注意事项** 1. **换行符兼容性**：需统一处理`\n`和`\r\n`，可用正则表达式如`\r?\n` 2. **性能优化**：递归方法在大数据量时效率较低，建议优先使用数据库内置函数（如PostgreSQL的`regexp_split_to_table`） 3. **空值处理**：添加条件过滤空行，例如`WHERE line != ''` ---