函数-2（分析、行列转换）

分析函数

分析函数基于一组行计算聚合值。 但是，与聚合函数不同，分析函数可能针对每个组返回多行。 可以使用分析函数来计算移动平均线、运行总计、百分比或一个组内的前 N 个结果。

一、应用格式

函数名（列名）over(规则)

二、语法格式

--聚合开窗函数
select 函数名（列名）over(partition by 分组字段) from 表名;
--排序开窗函数
select 函数名（列名）over( order by 排序字段 rows between [current row|unbounded preceding|1 following] and [current row|unbounded following|1 following])

注：

• []内部为可选字段
• current row、1均可表示当前行，preceding表示在…之前，following表示在…之后，unbounded 表示无限
• between unbounded preceding and unbouned following表示全表

三、注意事项

• 聚合函数与分析函数连用后，该整体几乎可以在select语句中各处使用
• 一个select语句中可以使用多个分析函数
• over函数中partition by 可以单独使用也可以与order by 搭配使用，但当其后接rows…后order by必须出现

四、具体实例

1. 显示各个部门的平均工资

select distinct deptno 部门号,avg(sal)over(partition by deptno) 部门平均工资 from emp; 

运行效果如下：

四、拓展

--取第一行数据
first_value(列名)over(相应规则)
--取最后一行数据
last_value(列名)over(相应规则)

示例如下：

--first_value(列名)over(相应规则)
--求每个部门最高分
select distinct deptno,first_value(sal)over(partition by deptno order by sal desc) 最高分
from emp;
--last_value(列名)over(相应规则)
--求每个部门最高分
select distinct deptno,
last_value(sal)over(partition by deptno
order by sal desc rows between
unbounded preceding and 
unbounded following) 最低分
from emp

运行效果如下：

行转列

--建表语句
create table studentScores(
       username varchar2(20),
       subject  varchar2(30),
       score    number
);
--插入数据
insert into studentScores values('张三','语文',90);
insert into studentScores values('张三','英语',80);
insert into studentScores values('张三','数学',70);
insert into studentScores values('李四','语文',60);
insert into studentScores values('李四','英语',61);
insert into studentScores values('李四','数学',62);
--事务提交
commit;

一、case when 实现
首先确定要转置后几行，例如上表中转置就需要四行
确定要换的列和不换的列
用case…when…实现数据的对应
给相应列起别名
代码如下：

select
username,
sum(case when subject='语文' then score end) 语文,
sum(case when subject='数学' then score end) 数学,
sum(case when subject='英语' then score end) 英语
from studentScores
group by username;

运行效果如下：

二、pivot（）函数实现

--语法格式
select 不变列名，转置后列名1，转置后列名2，转置后列名3
from 表名 pivot(sum(聚合列取值列名),
for 转置前列名 in (转置后列名1，转置后列名2，转置后列名3))

case…when中列子使用pivot写则为下方代码所示：

select username,"'语文'","'数学'","'英语'"
from studentScores
pivot(sum(score) 
for subject in('语文','数学','英语'));

列转行

--建表语句
create table studentScores2(
       username varchar2(20),
       yuwen  number,
       yingyu   number,
       shuxue   number
);
--插入数据
insert into studentScores2 values('张三',90,80,70);
insert into studentScores2 values('李四',60,61,62);
--事务提交
commit;

一、union all 实现
语法格式：

--语法格式
select username, '转置列的值1' as 列名1,原列值 as 新列名 from emp
union all
select username, '转置列的值2' as 列名2 ,原列值 as 新列名 from emp
union all
select username, '转置列的值3' as  列名3,原列值 as 新列名 from emp;

具体步骤：
1.确定固定的列
2.确定转置的列（每列分数合并一列，各个分数列名填充到一列）

• 各个分数列名填充到一列

--将yuwen填充到subject列
select 'yuwen' as subject from studentScores2;
--将shuxue填充到subject列
select 'shuxue' as subject from studentScores2;
--将yingyu填充到subject列
select 'yingyu' as subject from studentScores2;

注意：
（1）as后的列名可以随意起，不用在原表中存在
（2）‘值’ as 列名表明将引号内值填充到as后列名中，列名 as 列名表示起别名

• 分数列移动

--给yuwen列名起别名为score
select yuwen as score from studentScores2;
--给shuxue列名起别名为score
select shuxue as score from studentScores2;
--给yingyu列名起别名为score
select yingyu as score from studentScores2;

• 添加固定列并将上述两语句合并

--表结构为username,subject，score
select username,'yuwen' as subject,yuwen as score from studentScores2;
select username,'shuxue' as subject,shuxue as score from studentScores2;
select username,'yingyu' as subject,yingyu as score from studentScores2;

• 取三表并集

select username,'yuwen' as subject,yuwen as score from studentScores2
union all
select username,'shuxue' as subject,shuxue as score from studentScores2
union all
select username,'yingyu' as subject,yingyu as score from studentScores2;

注意：
（1)并集使用union all与union效果相同，需额外注意union会去除重复项
二、unpivot（）函数实现

select 不变列名,转置后列名1,转置后列名2 from 表名
unpivot(多列变一列的列名
for 聚合列别名 in (值1，值2，值三))

用unpivot()重写代码如下：

select username,subject,score from studentScores2
unpivot(score
for subject in ('yuwen','shuxue','yingyu'))

注意：

• for subject in (‘yuwen’,‘shuxue’,‘yingyu’)可以理解为将in 后值，循环写入subject列