oracle 学习之 unpivot/pivot函数及hive实现该功能

Oracle中pivot函数详解_实泽有之，无泽虚之的博客-CSDN博客pivot函数格式：pivot(聚合函数 for 需要转为列的字段名 in(需要转为列的字段值))；pivot函数说明：实现将指定字段的值转换为列的效果。https://blog.csdn.net/qq_40018576/article/details/128287105

oracle 总转横函数,详解Oracle行列转换函数-pivot函数和unpivot函数-多智时代_吴寿鹤的博客-CSDN博客今天主要介绍一下Oracle行转列及列转行常见函数，下面一起来看看吧！行列转换pivot函数：行转列函数语法：pivot(任一聚合函数 for 需专列的值所在列名 in (需转为列名的值))；unpivot函数：列转行函数语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))；执行原理：将pivot函数或unpivot函数接在查询结果集的后面。相当...https://blog.csdn.net/weixin_30899789/article/details/116470147

pivot和unpivot函数_weixin_34168700的博客-CSDN博客今天小编整理的都是固定行转列（列转行）的例子！一：unpivot列转行函数举例演示：创建一张表tmp_test,数据如图所示代码展示：select code,name,cource,grade from tmp_test unpivot(grade for source in (chinese,math,english));数据结果展示：二：pivot行转列函数举例演示：创建一张表tmp_te...https://blog.csdn.net/weixin_34168700/article/details/93016924 

pivot行转列函数

unpivot列转行函数

总结：
pivot函数：行转列函数：
　　语法：pivot(任一聚合函数 for 需专列的值所在列名 in (需转为列名的值))；
unpivot函数：列转行函数：
　　语法：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名))；
执行原理：将pivot函数或unpivot函数接在查询结果集的后面。相当于对结果集进行处理。

在学习之前，我个人觉得很多时候行转列 列转行，一行转多行，一列变多列，这些概念都不是很清楚

先看看网上说法 行转列、列转行 - 知乎

所谓行转列，即将一行数据转成多行显示，或者说将多列数据转成一列显示。通常将转化后的列名为某一行中某一列的值，来识别原先对应的数据

所谓列转行，即将多行转为一行显示，或者说将一列转为多列显示。通常转化后将某一列distinct后的值作为列名，将此值对应的多行数据显示成一行

 又有人评论说上面的说法反了

行转列：
即将多行一列数据转为一行多列显示。通常转化后将某一列分类后的值作为新的列名，将此值对应的多行数据显示成一行。
列转行：
即将一行多列数据转成多行一列显示。通常将转化后的列名为某一行中某一列的值，来识别原先对应的数据。

搞得我也迷惑了。再次搜索MySQL的行转列与列转行_什么是 列转行、行转列_¥程序猿¥的博客-CSDN博客 

 这里有句话说的好，

行转列 就是行数少了列数多了。

列转行 就是列数少了行数多了。

一列转多列 就是行数不变，列数多了

一行转多行 就是列数不变，行数多了

所以这才是真正的概念，因为a转b 肯定是a转化为b，一个少一个多。

——————————————————————————————————————————

pivot语法个人总结为 

select * from table  pivot(  --注意这里啊 pivot函数是对 table 进行行转列 table pivot是一个整体

聚合函数(column1)  --column1你要聚合的字段             

for column2 in (       --column2 是你要转成列的字段。比如下面的 COL_X 我要转列

        value1 as newcolumn1 , -- value1 value2 必须是column2的值

        value2 as newcolumn2

        )

)

数据准备

create table test.test_pivot(name varchar2(10),subject varchar2(10),score int);

INSERT INTO test.test_pivot
SELECT * FROM (
select 'cc1','语文',12 from dual union all
select 'cc1','数学',24 from dual union all
select 'cc1','英语',44 from dual union all
select 'cc2','语文',12 from dual union all
select 'cc2','数学',24 from dual union all
select 'cc2','英语',44 from dual union all
select 'cc3','语文',12 from dual union all
select 'cc3','数学',24 from dual union all
select 'cc3','英语',44 from dual union all
select 'cc4','语文',12 from dual union all
select 'cc4','数学',24 from dual union all
select 'cc4','英语',44 from dual union all
select 'cc5','语文',12 from dual union all
select 'cc5','数学',24 from dual union all
select 'cc5','英语',44 from dual union all
select 'cc6','语文',12 from dual union all
select 'cc6','数学',24 from dual union all
select 'cc6','英语',44 from dual union all
select 'cc7','语文',12 from dual union all
select 'cc7','数学',24 from dual union all
select 'cc7','英语',44 from dual union all
select 'cc8','语文',12 from dual union all
select 'cc8','数学',24 from dual union all
select 'cc8','英语',44 from dual union all
select 'cc9','语文',12 from dual union all
select 'cc9','数学',24 from dual union all
select 'cc9','英语',44 from dual union all
select 'cc10','语文',12 from dual union all
select 'cc10','数学',24 from dual union all
select 'cc10','英语',44 from dual
)

--如果oracle执行失败因为是csdn复制的时候多了空格。。自己处理

开始实战。

 SELECT * FROM test.test_pivot

 ----这里我先说下 为什么要行转列？？什么情况下需要行转列，各位考虑过没有？ 

这张图和上面那张图 都能够看到学生的各科成绩，但是下面的图看起来更加直观一点，是不是更好呢？ 我觉得不一定，因为每个人看待问题得角度不一样。

其实要是吹牛逼的话 还可以说下，存储的数据大小不一样，

最上面的是3*10 主要存的是 cc1 英语 12

下面的是4*10 主要寸的是cc1 24 12 44  

上面的英语两个汉字占的空间可比下面占的多点。

好了直接实战。

1.显示每个学生的各科成绩

SELECT * FROM test.test_pivot pivot( 
sum(score)   
FOR subject IN ('数据','英语','语文')
)

 --这里还是注意 这个函数pivot 和table还是一起的，说明是对整张表去列转行

--这个sum 啥用没有 你换成 avg min max 都一样

如何用hive表示呢？hive没有这类函数 直接group by 来处理

hive版本

SELECT NAME,
sum(CASE SUBJECT WHEN '语文' THEN  SCORE ELSE 0 END  ) AS yuwen,
sum(CASE SUBJECT WHEN '数学' THEN  SCORE ELSE 0 END  )AS shuxue,
sum(CASE SUBJECT WHEN '英语' THEN  SCORE ELSE 0 END  ) AS yingyu
FROM test.test_pivot
GROUP BY NAME

--这也没啥好说的，就是注意一个问题 这里也用了sum 其实换做avg max也行 min就算了。

select*FROM  test.test_pivot pivot
(
sum(score)
FOR name IN(
'cc1','cc2','cc3','cc4','cc5','cc6','cc7','cc8','cc9','cc10'
)
)

这种类似上面的。

SELECT subject,
sum(CASE name WHEN 'cc1'  THEN  SCORE ELSE 0  END)  AS cc1,
sum(CASE name WHEN 'cc2'  THEN  SCORE ELSE 0  END)  AS cc2,
sum(CASE name WHEN 'cc3'  THEN  SCORE ELSE 0  END)  AS cc3,
sum(CASE name WHEN 'cc4'  THEN  SCORE ELSE 0  END)  AS cc4,
sum(CASE name WHEN 'cc5'  THEN  SCORE ELSE 0  END)  AS cc5,
sum(CASE name WHEN 'cc6'  THEN  SCORE ELSE 0  END)  AS cc6,
sum(CASE name WHEN 'cc7'  THEN  SCORE ELSE 0  END)  AS cc7,
sum(CASE name WHEN 'cc8'  THEN  SCORE ELSE 0  END)  AS cc8,
sum(CASE name WHEN 'cc9'  THEN  SCORE ELSE 0  END)  AS cc9,
sum(CASE name WHEN 'cc10' THEN  SCORE ELSE 0  END)  AS cc110
FROM test.test_pivot
group by subject 

--反正就是group by

2.显示每个学生的总成绩

select*FROM
(SELECT name,score from test.test_pivot) pivot
(
sum(score)
FOR name IN(
        'cc1','cc2','cc3','cc4','cc5','cc6','cc7','cc8','cc9','cc10'
        )
)

hive 如何实现呢？

第一步 先求sum总分

SELECT name,sum(score) from test.test_pivot
GROUP BY name 

WITH tmp AS (SELECT name ,sum(score ) score  FROM test.test_pivot GROUP BY name) 
SELECT 
sum(CASE name WHEN 'cc1' THEN SCORE ELSE 0 END) AS cc1, 
sum(CASE name WHEN 'cc2' THEN SCORE ELSE 0 END) AS cc2, 
sum(CASE name WHEN 'cc3' THEN SCORE ELSE 0 END) AS cc3, 
sum(CASE name WHEN 'cc4' THEN SCORE ELSE 0 END) AS cc4, 
sum(CASE name WHEN 'cc5' THEN SCORE ELSE 0 END) AS cc5, 
sum(CASE name WHEN 'cc6' THEN SCORE ELSE 0 END) AS cc6, 
sum(CASE name WHEN 'cc7' THEN SCORE ELSE 0 END) AS cc7, 
sum(CASE name WHEN 'cc8' THEN SCORE ELSE 0 END) AS cc8, 
sum(CASE name WHEN 'cc9' THEN SCORE ELSE 0 END) AS cc9 
FROM tmp 
GROUP BY 1

反正就是这么个语法 我的不一定最好，只是一个思路。

unpivot

语法

select * from table  pivot(  --注意这里啊 pivot函数是对 table 进行行转列 table pivot是一个整体

聚合函数(column1)  --column1你要聚合的字段             

for column2 in (       --column2 是你要转成列的字段。比如下面的 COL_X 我要转列

        value1 as newcolumn1 , -- value1 value2 必须是column2的值

        value2 as newcolumn2

        )

)

数据准备 

直接将pivot的数据转化下久iu是unpivot的了 这两个函数本身就是相互转化的。

CREATE TABLE test.test_unpivot AS 
SELECT * FROM test.test_pivot pivot(sum(score)FOR subject IN ('数学' AS 数学,'英语' AS 英语,'语文' AS 语文 ))
--这里注意有个坑，一般来说汉字 我们都要打引号的，oracle这里不需要。 如果你不 加as别名，你最后的字段名就是'数学'而不是 数学

--里面的分数我自己随便改了点 不影响。

 

unpivot简单使用 行转列之一行转多列

select name,subject,grade from test.test_unpivot  UNPIVOT(
    grade for subject in(数学,英语,语文)) 

 

如何用hive语法，其实说mysql语法也行。实现如上功能呢？ 

上面行转列pivot 我们都是采用group by

那么列转行unpiovt 一般都是采用 union all。

 SELECT * 
    FROM (
    SELECT name ,'数学',数学 FROM test.test_unpivot UNION ALL 
    SELECT name ,'语文',语文 FROM test.test_unpivot UNION ALL 
    SELECT name ,'英语',英语 FROM test.test_unpivot
    ) ORDER BY name 

就这样吧

总结下

pivot 我们在hive就用 case when +group by 来实现

unpivot我们在hive就用 union all来实现