Oracle 11g使用Pivot函数实现数据聚合行转列

经常使用数据库,我们有很大的精力应付在处理各种各样的数据类型,处理各种类型的报表。需要对数据进行行列转换,Oracle 11G 提供了pivot函数,方便我们对数据进行行列转换。

表设计如下:

create table tongji(
id number primary key, 
name varchar2(10),
time date, --日期
temperature number --温度
)

数据如下:

insert into tongji values(1,'河南',to_date('2015-01-01','yyyy-mm-dd'),10);
insert into tongji values(2,'河南',to_date('2015-02-01','yyyy-mm-dd'),15);
insert into tongji values(3,'四川',to_date('2015-01-01','yyyy-mm-dd'),12);
insert into tongji values(4,'四川',to_date('2015-02-01','yyyy-mm-dd'),14);
insert into tongji values(5,'北京',to_date('2015-01-01','yyyy-mm-dd'),15);
insert into tongji values(6,'北京',to_date('2015-02-01','yyyy-mm-dd'),16);
insert into tongji values(7,'上海',to_date('2015-01-01','yyyy-mm-dd'),15);
insert into tongji values(8,'上海',to_date('2015-02-01','yyyy-mm-dd'),15);
insert into tongji values(9,'山东',to_date('2015-01-01','yyyy-mm-dd'),17);
insert into tongji values(1,'山东',to_date('2015-02-01','yyyy-mm-dd'),17);

select * from tongji

查询结果为:

Oracle 11g使用Pivot函数实现数据聚合行转列_第1张图片

如果要查询每个地市,每月的最高气温,查询语句如下:

select t.name,to_char(t.time,'mm') datetime,max(t.temperature) maxTempt
from tongji t
group by t.name,to_char(t.time,'mm')
order by 1, 2
查询结果:

Oracle 11g使用Pivot函数实现数据聚合行转列_第2张图片

可以查出每个地市,月最高气温。但是此时数据,每月一行存储。而我们有时需要数据每行存储一个地市的且每月占一列。

这时候我们可以选择使用Oracle 11G提供的pivot函数,或者使用后台算法处理(表格数据反转)我前面写过这里就不在赘述。

这里介绍pivot,语法:

select *

from t

pivot 

(

   max(value)  --聚合操作函数

   for object_type --行转列标准

   in ('TABLE','INDEX') --行转列列取值和顺序

)
表t中只含参与聚合、排序、转换的列。而函数仅需三部分内容

1、 聚合列取值。需要告诉pivot函数进行转列的过程中,聚合操作的函数和处理对象;

2、行转列标准。依据那个列进行行转列;

3、 列转行取值。因为要将数据行取值转成列,我们需要告诉Oracle那些取值成列,并且这些取值成列的过程中,列顺序是如何的;

查询语句:

with tj as(
select t.name, to_char(t.time,'mm') datetime,t.temperature from tongji t
)
select *
from tj 
pivot(
max(tj.temperature)
for datetime in ('01','02') 
)

with tj as(
select t.name, to_char(t.time,'mm') datetime,t.temperature from tongji t
)
select *
from tj 
pivot(
max(tj.temperature) as maxvalue
for datetime in ('01' as p01,'02' as p02) 
)


 
  
with .. as 语法可自行学习,简而言之,它的作用就相当于处理数据,制作临时表。

查询结果:

Oracle 11g使用Pivot函数实现数据聚合行转列_第3张图片

对比上下查询结果,完成所需功能。

当然,这里仅仅是抛砖引玉,具体更加详细的功能,还需要聪明的你去发现。

结论

现实工作中会遇到各种奇怪的需求。在选择工具的时候,应尽可能的选择类库、预定义提供的方法函数,不要轻易的选择自定义方法。因为对现成的类库和产品来说,它在实现这个方法的时候倾注了很大心血,其效率一定是比我们自己写的要强。多练习、运用、总结。

你可能感兴趣的