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

摘要用SQL实现数据库中数据的行列转换，不仅要编写复杂的程序代码，还要编写存储过程。如果引入ORACLE中的分析功能，流程会简单很多。首先找出表中所有关键字的最大属性数，设为N，其

用SQL实现数据库中数据的行列转换，不仅要编写复杂的程序代码，还要编写存储过程。如果引入ORACLE中的分析功能，流程会简单很多。首先找出表中所有关键字的最大属性数，设为N，其次为每个关键字增加N列，利用分析函数查询关键字的属性所在列的位置。然后，将每个关键字的多行属性转换为多列属性，最后将生成的新列拼成一个字符串形成一列，从而实现行列转换。

介绍

分析功能旨在解决“累计计算”等问题。虽然大部分问题可以用PL/SQL解决，但是性能并不理想。首先，查询本身不容易编写。其次，有些直接用SQL很难做到的查询，其实是很常见的操作，比如数据表中转行列。在SQL中查询这样的问题是非常困难的。在分析函数出现之前，我们必须使用由自连接查询或子查询甚至复杂的存储过程实现的语句。现在只需要一条简单的SQL语句就可以实现，执行效率大大提高。本文将举例说明如何使用分析函数实现数据的行列互换。

原则

1。分析函数的格式和语法

分析功能是根据记录行的分组来计算记录行的总值。行的分组被称为窗口，由分析语句定义。为每个记录行定义一个“滑动”窗口。此窗口确定当前行计算的范围。窗口的大小可以由每行的实际数量或逻辑间隔(如时间)来确定。

分析从以下形式开始:

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

(1)分析函数:1)分析函数的名称。Oracle10gR2内置了很多分析函数，包括:AVG、CORR、COVAR_POP、COVAR_SAMP、计数、滞后、最后、领先、最大、最小、排名、求和等。对于用户定义的分析函数，分析函数的名称需要满足标识符规则。

(2)自变量:参数。分析函数通常有0到3个参数，这些参数可以是任何数值类型或可以隐式转换为数值类型的数据类型。用户自定义参数可根据实际情况使用。

(3)OVER:是分析函数必须使用的关键字。对于既可以用作聚集函数又可以用作分析函数的函数，Oracle无法识别，所以需要使用over来标识它们是分析函数。

(4)Query-Partition-Clause:查询分组子句，PARTITION BY根据分区表达式设置的规则，将一个结果逻辑上分成N个分组表达式。分析功能独立应用于每个数据包，并在应用时重置。

(5)Order-By-Clause: (group by …)，这是一个根据一个或多个排序表达式对组进行排序的排序子句。

(6)Windowing-Clause窗口生成语句:窗口生成语句用于定义滑动或固定数据窗口，分析函数分组进行分析。该语句可以计算分组中任何定义的滑动或固定窗口。

2。示例原则介绍

在本例中，具有相同关键字的多条记录中的不同列被合并到一列中。例如，一个临时表包含用户号码、电话号码、产品名称、业务区域和相关业务名称五个字段，每个用户的业务可能有多个项目，因此创建一个数据表会造成冗余。现在要想办法把号码、电话、产品名称、营业区域相同的用户的相关业务属性合并到一列，解决冗余问题。利用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;

3)先检查课程名称最多的组合。

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

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;

随机代码。峡江..

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_5from (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;

随机代码。峡江..

6)将转换后的名字拼成一个字符串，放在一行。

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;

随机代码。峡江..

本文的程序可以实现以下功能:

①计算同一个关键字的最多组合；

②根据分析函数查询某个关键字所在列的位置；

③将待合并列的所有行改为列；

④拼写一些需要合并成一个字符串的列。

分析函数除了上述功能外，还可以实现求和、Top-N查询、一定范围内数据行的统计、交叉表查询等功能。