hive 行转列/列转行 多行转一行/一行转多行
Mysql
创建表语句:
CREATE TABLE student_score(
id BIGINT PRIMARY key auto_increment,
s_name VARCHAR(20) ,
s_sub VARCHAR(20),
s_score INT
);
insert into student_score values(null,'张三','数学',90);
insert into student_score values(null,'张三','语文',85);
insert into student_score values(null,'张三','英语',92);
insert into student_score values(null,'李四','数学',88);
insert into student_score values(null,'李四','语文',91);
insert into student_score values(null,'李四','英语',99);
insert into student_score values(null,'王五','数学',100);
insert into student_score values(null,'王五','语文',82);
insert into student_score values(null,'王五','英语',88);
select * from student_score;
行转列
业务场景,按照上图看的话很难只看同学们的数学成绩或者语文成绩,所以要按照以下格式展示:
s_name | 数学 | 语文 | 英语 |
张三 | 90 | 85 | 92 |
李四 | 88 | 91 | 99 |
李四 | 100 | 82 | 88 |
思路:
第一步可以将每一行根据学科s_sub分成数学,语文,英语三列 , sql语句:
case s_sub when '数学' then s_score else 0 end 数学完整sql:
select s_name,
case s_sub when '数学' then s_score else 0 end 数学,
case s_sub when '语文' then s_score else 0 end 语文,
case s_sub when '英语' then s_score else 0 end 英语
from student_score结果:
最后,需要按照s_name为维度group by聚合一下
select s_name,
sum(case s_sub when '数学' then s_score else 0 end) 数学,
sum(case s_sub when '语文' then s_score else 0 end) 语文,
max(case s_sub when '英语' then s_score else 0 end) 英语
from student_score group by s_name最终结果:
如果不是int类型而是 varchar 那么上面是行不通的,因为sum里面不能跟字符串,需要用group_concat()函数替代sum,可先看多行转一行之后再看下方sql
select s_name,
GROUP_CONCAT(case s_sub when '数学' then CAST(s_score AS char) else null end) 数学,
GROUP_CONCAT(case s_sub when '语文' then CAST(s_score AS char) else null end) 语文,
GROUP_CONCAT(case s_sub when '英语' then CAST(s_score AS char) else null end) 英语
from student_score group by s_name多行转一行
业务场景,以张三为例,将张三的三个成绩拼接成字符串 放到一个单元格中显示:
'张三' '数学:90,语文:85,英语92'上述 类似于
select s_name, sum(s_score) from student_score group by s_namesum 就是一个聚合函数,只不过它是将所有的数字相加得到结果,根据上面业务场景,此时要用到一个函数 group_concat ,类似于sum 但是它的作用是将聚合后的数据进行字符拼接
group_concat (a,b,c,… separator ’ ‘) 或者
group_concat (a,b,c,…)
group_concat 中的参数是可变参数,多个参数拼接,最后一个speparator xxx 是多行数据拼接后的分隔符,如果不写默认的分隔符是’,'逗号.并且记住要用group by 维度聚合,不写group by 会拼接所有的行
完整的sql:
select s_name,group_concat(s_sub,':',s_score separator '@') all_score from student_score group by s_name结果:
列转行
首先要用到上面行转列的数据
create table student_score2 as
select s_name,
sum(case s_sub when '数学' then s_score else 0 end) 数学,
sum(case s_sub when '语文' then s_score else 0 end) 语文,
max(case s_sub when '英语' then s_score else 0 end) 英语
from student_score group by s_name
下面是实现列转行的sql
SELECT
s_name,
'数学' AS s_sub,
数学 AS s_score
FROM s_score
UNION
SELECT
s_name,
'语文' AS s_sub,
语文 AS s_score
FROM s_score
UNION
SELECT
s_name,
'英语' AS s_sub,
英语 AS s_score
FROM s_score结果
一行转多行
一行转多行在关系型数据库中是有点儿矛盾的,因为关系型数据库设计原则就是单条数据不能再分,如果需要再分是外部(hive/java程序)分完之后进入关系型数据库的,虽然矛盾,但是也是可以实现的.
创建表:
create table student_score3 as
select s_name,group_concat(s_sub,':',s_score separator '@') all_score
from student_score group by s_name先sql再说思路:
SELECT
a.s_name,
substring_index( substring_index( a.all_score, '@', b.id), '@',- 1 )
FROM
student_score3 a
JOIN account b ON
b.id <= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );account表是作为辅助结构的提供1-n个自增长数字
查询结果:
思路:(根据sql思想一步步替换成可执行的sql)
①根据张三的一条数据来看:首先,把张三分成三条,三是all_score中想切分成的元素个数决定的
name index all_score
张三 1 数学:90@语文:85@英语:92
张三 2 数学:90@语文:85@英语:92
张三 3 数学:90@语文:85@英语:92上图sql思想是:
select a.s_name, allscore from a join b on n <= all_score_arr.size(); n>=1
--这样子可以根据n的个数join出n条数据.②转化成下面我们想要的这样:
name index score
张三 1 数学:90
张三 2 语文:85
张三 3 英语:92如果把all_score看成一个数组的话,刚好score就是取的all_score_arr的第index个元素
可以得出sql的思路:
select a.s_name,all_score_arr[n] from a join b on n <= all_score_arr.size();如果把上面的sql转换成mysql可以执行的sql语句,就能完成多行转一行的目的.
③初步思路有了以后,先看mysql的函数
先是函数 substring_index(str,sp,x)
str是要切分的字符串,sp指分隔符,x是int类型通过下面sql
select substring_index(all_score,'@',x) from student_score3;可以看规律 x 为1 结果是’数学:90’,x为2 结果是’数学:90@语文85’(从右往左取)
x为-1 结果是’英语:90’,x为-2 结果是’语文:85@英语;92’(从左往右取)
所以要用两个substring_index()函数来实现类似于将字符串all_score根据@切分成数组再根据all_score_arr索引取元素.
substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )
n= 1时 取的是’数学:90’
n = 2时 取的是’语文:85’
…
这就完成了模拟数组取元素
所以all_score_arr[n] 可以用 substring_index( substring_index( all_score, ‘@’, n), ‘@’,- 1 )替代:
select a.s_name,substring_index( substring_index( all_score, '@', n), '@',- 1 )
from a join b on n <= all_score_arr.size();n代表着整数可以通过另外一个表的自增主键来替代,b.id就代表了从1-n的整数,得到下面sql:
select a.s_name,substring_index( substring_index( all_score, '@', b.id), '@',- 1 )
from a join b on b.id <= all_score_arr.size();将a与b与实际的表进行替换
select a.s_name,substring_index(substring_index( all_score, '@', b.id), '@',- 1 )
from student_score2 a join account b on b.id <= all_score_arr.size();all_score_arr.size()也就是元素的个数应该如何表示呢?
可以通过(分隔符+1)来表示元素个数,
length( a.all_score ) 是字符长度 length( replace( a.all_score, ‘@’, ‘’ ) ) 是去掉分隔符后的长度
两者相减就得到分隔符数,分隔符数加1就是元素个数
所以arr.size() 可以用 length( a.all_score ) - length( replace( a.all_score, ‘@’, ‘’ ) ) + 1 表示
替换后得到最终结果:
select a.s_name,substring_index( substring_index( a.all_score, '@', b.id+ 1 ), '@',- 1 )
from a join b on b.id<= ( length( a.all_score ) - length( replace( a.all_score, '@', '' ) ) + 1 );
