PHP转码法式技巧_用ORACLE分析函数decode实现行列转换

文章目录 [+]

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

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

PHP转码法式技巧_用ORACLE分析函数decode实现行列转换

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;

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

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）把转换后的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;

乱码。
姑息下..

总结

本文中的程序能够实现以下功能：

①打算具有相同关键字的最多的组合；

②根据剖析函数查询某一关键字所处的列的位置；

③把需合并列的所有的行换成列；

④把须要合并的某几列拼成一个串。

剖析函数除了拥有以上所先容的功能，还能够实现诸如求和、Top-N查询、统计某个范围的数据行窗口、交叉表查询等功能。

后面会分享更多关于sql怎么去优化的内容，感兴趣的朋友可以关注下~·