当前位置：首页 > 经验

数据库sql语句大全实例 oracle行列转换函数

经验
2021-11-08

概述

对数据库中的数据用SQL实现行列转换，不但需要编写复杂的程序代码，还需要编写存储过程。若引入ORACLE中的分析函数则会使该过程简便很多。首先找出表中所有关键字的属性个数的最大值，设为n，其次为每个关键字新添加n列，并用分析函数查询关键字的属性所处列的位置，然后将每个关键字的多行属性转换成多列属性，最后把生成的多个新列拼成一个串形成一列，从而实现行列转换。

引言

分析函数的设计目的是为了解决诸如“累计计算”等问题。虽然大部分的问题都可以用PL/SQL解决，但是性能并不理想，首先查询本身并不容易编写，其次有些很难在SQL中直接做的查询但实际上是很普通的操作，比如实现数据表中行列传换。这样的问题在SQL中做查询就很困难。在分析函数出现以前，我们必须使用自联查询或者子查询甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。本文将以一个实例来描述如何采用分析函数实现数据中的行列互换。

原理

1、分析函数的格式及语法

分析函数是在一个记录行分组的基础上计算它们的总值。行的分组被称窗口，并通过分析语句定义。对于每记录行，定义了一个“滑动”窗口。该窗口确定“当前行”计算的范围。窗口的大小可由各行的实际编号或由时间等逻辑间隔确定。

分析函数以如下形式开头：

Analytic-Function（<Argument>，<Argument>，...）
OVER （<Query-Partition-Clause><Order-By-Clause><Windowing-Clause>）

（1）Analytic-Function：分析函数的名称，Oracle10gR2带的内置分析函数有多个，包括：AVG、CORR、COVAR_POP、COVAR_SAMP、COUNT、LAG、LAST、LEAD、MAX、MIN、RANK、SUM等；对于用户自定义的分析函数，分析函数名称需要满足标识符规则。

（2）Arguments：参数，分析函数通常有0到3个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。对于用户自定义的参数，可以根据实际情况使用。

（3）OVER：是分析函数就必须使用的关键字，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数。

（4）Query-Partition-Clause：查询分组子句，根据划分表达式设置的规则，PARTITION BY将一个结果逻辑分成N个分组划分表达式。分析函数独立应用于各个分组，并在应用时重置。

（5）Order-By-Clause：（按…排序分组），是排序子句，根据一个或多个排序表达式对分组进行排序。

（6）Windowing-Clause窗口生成语句：窗口生成语句用以定义滑动或固定数据窗口，分析函数在分组内进行分析。该语句能够对分组中任意定义的滑动或固定窗口进行计算。

2、实例原理介绍

本实例是将具有相同关键字的多条记录中的某一不同列合并成一列，例如在一个临时表中包含有用户的编号、电话号码、产品名称、所在营业区以及相关业务名称5个字段，而每个用户的业务可能有多项，这样创建数据表将会造成冗余，现在要想办法将表中编号、电话号码、产品名称、所在营业区四个字段相同的用户的相关业务属性合并成一列解决冗余问题，使用SQL语句会比较困难，甚至需要一定的存储过程。使用Orcale中的分析函数来实现这样的行列转换就比较简单方便了。

3、实例

1）创建临时表

create table temp (
num varchar2(15),
name varchar2(20),
sex varchar2(2),
classes varchar2(30),
course_name varchar2(50) );

2）构造数据

insert into temp(num,name,sex,classes,course_name) values ('206211','王艺','男','06-1班','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务管理');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','财务会计');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','电子商务');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公共经济学');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','公司理财');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','管理学原理');
insert into temp(num,name,sex,classes,course_name) values ('206213','陈雅诗','女','06-2','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','保险学');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务管理');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','财务会计');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','电子商务');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹阳','男','06-1','公共经济学');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','环境管理学');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','管理学原理');
insert into temp(num,name,sex,classes,course_name) values ('206215','杨伊琳','女','06-3班','商务谈判');
insert into temp(num,name,sex,classes,course_name) values ('206216','李佳琪','男','06-2','土地估计');
Commit;

用ORACLE分析函数decode实现行列转换

3）先查一下course_name最多的组合

select max(count(course_name))
from temp
group by num,name,sex,classes;

用ORACLE分析函数decode实现行列转换

4）列的位置

用分析函数中的row_number函数，在num，name，sex，classes相同的情况下course_name所处的列的位置（第几列）。

row_number函数解释：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。

select num,
 name,
 sex,
 classes,
 course_name,
 row_number() over(partition by num, name, sex, classes order by course_name) rn
 from temp;

用ORACLE分析函数decode实现行列转换

乱码。将就下..

5）把course_name的所有的行换成列

select num,name,sex,classes,
 max(decode(rn,1,course_name,null)) course_name_1,
 max(decode(rn,2,course_name,null)) course_name_2,
 max(decode(rn,3,course_name,null)) course_name_3,
 max(decode(rn,4,course_name,null)) course_name_4,
 max(decode(rn,5,course_name,null)) course_name_5
from (select num,name,sex,classes,course_name,
row_number() over(partition by num,name,sex,classes order by course_name) rn
 from temp)
group by num,name,sex,classes;

用ORACLE分析函数decode实现行列转换

乱码。将就下..

6）把转换后的name拼成一个字符串，放在一行

select num,
 name,
 sex,
 classes,
 (max(decode(rn, 1, course_name, null)) ||
 max(decode(rn, 2, ',' || course_name, null)) ||
 max(decode(rn, 3, ',' || course_name, null)) ||
 max(decode(rn, 4, ',' || course_name, null)) ||
 max(decode(rn, 5, ',' || course_name, null))) name
 from (select num,
 name,
 sex,
 classes,
 course_name,
 row_number() over(partition by num, name, sex, classes order by course_name) rn
 from temp)
 group by num, name, sex, classes;

用ORACLE分析函数decode实现行列转换