hive语句实现列转行

最新推荐文章于 2025-12-02 15:55:42 发布

原创最新推荐文章于 2025-12-02 15:55:42 发布 · 4.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hive #数据库 #SQL

大数据同时被 2 个专栏收录

21 篇文章

订阅专栏

数据库

9 篇文章

订阅专栏

本文介绍了如何在Hive中使用explode函数实现列转行的操作，对比了Oracle中的unpivot函数，并提供了具体的建表语句和实际效果展示，展示了将多个字段拼接后拆分为多行数据的方法。

hive语句实现列转行

建表语句

CREATE  TABLE XXXX表(
  evel_id string DEFAULT NULL COMMENT 'xxxxid', 
  object_id string DEFAULT NULL COMMENT '所属对象', 
  object_name string DEFAULT NULL COMMENT '所属对象名称', 
  dept_id string DEFAULT NULL COMMENT '单位编码', 
  dept_name string DEFAULT NULL COMMENT '单位名称', 
  ......省略一堆字段
  rcount bigint DEFAULT NULL COMMENT '记录属性总数 ', 
  qcount bigint DEFAULT NULL COMMENT '问题属性总数', 
  nqcount bigint DEFAULT NULL COMMENT '新增问题总数 ', 
  sqcount bigint DEFAULT NULL COMMENT '解决问题总数 ', 
  score decimal(5,2) DEFAULT NULL COMMENT '属性分数', 
  create_date timestamp DEFAULT NULL COMMENT '创建时间', 
  update_date timestamp DEFAULT NULL COMMENT '更新时间', 
  create_person string DEFAULT NULL COMMENT '创建人'
)
COMMENT 'XXXX表'

原数据部分展示：
在这里插入图片描述

Oracle中列转行语法，使用 unpivot函数

unpivot（字段属性 for 字段别名 in(字段1 as 别名1，字段2 as 别名2 …))

select EPT_NAME as DEPT_NAME,
       OBJECT_NAME,
       OBJ_COUNT,
       ROUND(SUM(SCORE) / COUNT(*), 2) as SCORE
  from XXXX表
       unpivot(SCORE for OBJ_COUNT in(RCOUNT as '属性总数',
                                      QCOUNT as '问题属性总数',
                                      SCORE as '得分'))
 where 1 = 1
   AND SYS_ID = '1234567890'
 GROUP BY OBJECT_NAME, DEPT_NAME, OBJ_COUNT
 ORDER BY OBJECT_NAME;

实现效果：
在这里插入图片描述

hive中列转行语法，使用explode函数

因为LATERAL VIEW explode(split(字段,’,’))是将一个字段拆分展示为多条数据显示，所以使用CONCAT_WS语句，将多个字段拼接起来，可以满足将一条数据多个特定字段展示为多条数据一个特定字段。
LATERAL VIEW explode(split(CONCAT_WS(’,’,to_char(RCOUNT),to_char(QCOUNT),to_char(SCORE)),’,’)) mytable AS obj_view

 select DEPT_NAME ,
       OBJECT_NAME,
       ROUND(SUM(obj_view) / COUNT(*), 2) as SCORE
  from XXXX表 
   LATERAL VIEW explode(split(CONCAT_WS(',',to_char(RCOUNT),to_char(QCOUNT),to_char(SCORE)),',')) mytable AS obj_view
 where 1 = 1
   AND SYS_ID = '1234567890'
 GROUP BY OBJECT_NAME, DEPT_NAME,obj_view
 ORDER BY OBJECT_NAME ;

实现效果为：
在这里插入图片描述

最近将解释字段添加上的语句

select DEPT_NAME ,
       OBJECT_NAME,
       substr(obj_view,1,INSTR(obj_view,'|')-1) OBJ_COUNT,
       ROUND(SUM(substr(obj_view,INSTR(obj_view,'|')+1)) / COUNT(*), 2) as SCORE
  from XXXX表
   LATERAL VIEW explode(split(CONCAT_WS(',',
   CONCAT_WS('|','属性总数',to_char(RCOUNT)),
   CONCAT_WS('|','问题属性总数',to_char(QCOUNT)),
   CONCAT_WS('|','得分',to_char(SCORE))),',')) mytable AS obj_view
 where 1 = 1
   AND SYS_ID = '1234567890'
 GROUP BY OBJECT_NAME, DEPT_NAME,OBJ_COUNT
 ORDER BY DEPT_NAME DESC,OBJECT_NAME;

效果和Oracle实现的列转行效果一样
在这里插入图片描述