ORACLE的窗口函数（分析函数）
ORACLE的窗口函数（分析函数)
目录
- ORACLE的窗口函数（分析函数)
- 前言描述
- 常用分析函数汇总
- 1.排名函数
- 2.count() over()：计数
- 3.sum() over()：求和
- 4.avg() over()：求平均
- 5.min() over()；max() over()：求最小最大
- 6.ntile(n) over()：
- 7.first_value() over()/last_value() over()：
- 8.keep (dense_rank first/last order by ...)over():
- 9.lag(column_name,n,若首行无填充默认为null) over()
- 10.ratio_to_report(a) over(partition by b) ：
- 11.percent_rank() over():
- 12.cume_dist() over() ：
- 13.precentile_cont( x ) within group(order by ...) over()：
- 14.stddev() over()：
- 15.variance() over()：
- 16.covar_samp over()：
- decode函数
- Oracle 行转列 动态转换的列
- 查找两张表的数据，用一个标识区分
前言描述
分析函数带有一个开窗函数over()，包含三个分析子句:
分组(partition by),
排序(order by),
窗口(rows) ，
他们的使用形式如下：
分析函数名(参数) over (partition by 子句 order by 子句 rows/range… 子句)
(注：若窗口函数内和sql语句末尾共存在两个order by）
注意Partition by可以有多个字段。
a) order by 字段两者一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
b) order by 字段两者不一致：即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，那么sql语句中的排序将最后在分析函数分析结束后执行排序。)
窗口函数的由来：
partition by:分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是窗口函数的由来。通过PARTITION BY分组后的记录集合称为“窗口”
实例
以Scott用户中的emp表，结合sum()over()简单示例
累计收入求和的需要重新按empno或者empno、deptno排一下序,不然有些乱看的不是很清楚，基本常用的就是上面几种形式
select e.deptno,
e.empno,
e.ename,
e.sal,
sum(e.sal)over() 总收入,
sum(e.sal)over(partition by e.deptno) 部门总收入,--按部门分组求和
sum(e.sal)over(order by e.empno) 员工累计收入,--按照员工编号（empno）的排序取累计收入和
sum(e.sal)over(partition by e.deptno order by e.empno) 员工部门内累计收入,--按部门（deptno）分组，同时按员工编号（empno）排序取员工部门内累计收入和
sum(e.sal)over(partition by e.deptno order by e.empno rows between unbounded preceding and unbounded following) 部门总收入2--可指定范围，结果同上
from emp e;
DEPTNO EMPNO ENAME SAL 总收入 部门总收入 员工累计收入 员工部门内累计收入 部门总收入2
---------- ---------- -------------------- ---------- ---------- ---------- ------------ ------------------ -----------
10 7782 CLARK 2450 24925 8750 13175 2450 8750
10 7839 KING 5000 24925 8750 18175 7450 8750
10 7934 MILLER 1300 24925 8750 24925 8750 8750
20 7369 SMITH 800 24925 6775 800 800 6775
20 7566 JONES 2975 24925 6775 6625 3775 6775
20 7902 FORD 3000 24925 6775 23625 6775 6775
30 7499 ALLEN 1600 24925 9400 2400 1600 9400
30 7521 WARD 1250 24925 9400 3650 2850 9400
30 7654 MARTIN 1250 24925 9400 7875 4100 9400
30 7698 BLAKE 2850 24925 9400 10725 6950 9400
30 7844 TURNER 1500 24925 9400 19675 8450 9400
30 7900 JAMES 950 24925 9400 20625 9400 9400
说明：
--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
--unbounded：不受控制的，无限的
--preceding：在...之前
--following：在...之后
rows between unbounded preceding and unbounded following 表中的所有记录
rows between unbounded preceding and current row 是指第一行至当前行的汇总
rows between current row and unbounded following 指当前行到最后一行的汇总
rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总
rows between 1 preceding and 2 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
有关ROWS/RANGE窗口的例子（借鉴其他的博客）
注意事项：
1、窗口子句一般和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，如上例结果集中的defult_sum等于range_unbound_sum；
2、如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算；
3、range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如上例中range_sum（即range 1 preceing and 2 following）例的分析结果：
当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5(取id为1,1,3);
当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3）；
当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id为6，6，6，7，8）;
以此类推下去，结果如上例中所示。
4、rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），如上例中rows_sum例结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果：
当id=1（第一个1时）时，前一行没数，后二行分别是1和3，sum=1+1+3=5；
当id=3时，前一行id=1,后二行id都为6，则sum=1+3+6+6=16；
以此类推下去，结果如上例所示。
注：行比较分析函数lead和lag无window(窗口)子句。
with t as
(select (case
when level in (1, 2) then
1
when level in (4, 5) then
6
else
level
end) id
from dual
connect by level < 10)
select id,
sum(id) over(order by id) default_sum,
sum(id) over(order by id range between unbounded preceding and current row) range_unbound_sum,
sum(id) over(order by id rows between unbounded preceding and current row) rows_unbound_sum,
sum(id) over(order by id range between 1 preceding and 2 following) range_sum,
sum(id) over(order by id rows between 1 preceding and 2 following) rows_sum
from t;
常用分析函数汇总
1.排名函数
项目开发中，我们有时会碰到需要分组排序来解决问题的情况：
- 1、要求取出按field1分组后，并在每组中按照field2排序；
- 2、亦或更加要求取出1中已经分组排序好的前多少行的数据
这里通过一张表的示例和SQL语句阐述下oracle数据库中用于分组排序函数的用法。
1.row_number() over()
注：此分析函数必须要加order by排序
row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。
1 2 3 4 5
如：
select empno,ename,mgr,sal,deptno,row_number() over(order by empno) rn from emp;--按empno排序
select empno,ename,mgr,sal,deptno,row_number() over(partition by deptno order by empno) rn from emp;--（按部门分组，empno排序）添加一个伪列（和rownum类似）
--此分析函数在对数据去重时用的比较多
select * from(
select empno,ename,mgr,sal,deptno,row_number() over(partition by deptno order by empno) rn from emp)
where rn = 1;--每个部门只取一条数据（当然 partition by 后面可以按需求跟多个字段，来达到你想要的筛选目的）
2.rank() over()
注：此分析函数同row_number() over()必须要加order by排序
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
1 2 2 4 5
3.dense_rank() over()
注：此分析函数同row_number() over()必须要加order by排序
dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。
1 2 2 3 4
select empno,ename,mgr,sal,deptno
,rank() over(order by sal desc) 跳跃排序
,dense_rank() over(order by sal desc) 连续排序 from emp;--按sal金额排名
select empno,ename,mgr,sal,deptno
,rank() over(partition by deptno order by sal desc) 跳跃排序
,dense_rank() over(partition by deptno order by sal desc) 连续排序 from emp;--按deptno分组，组内、金额排名
create table tblscore(stuid number,name varchar2(50),score number);
insert into tblscore values(1001,'alex',70);
insert into tblscore values(1002,'jack',80);
insert into tblscore values(1003,'tom',90);
insert into tblscore values(1004,'jeson',80);
insert into tblscore values(1005,'tonny',90);
insert into tblscore values(1006,'hans',50);
dense_rank
select stuid,name,score,dense_rank() over(order by score desc) as rank from tblscore;
STUID NAME SCORE RANK
---------- -------------------------------------------------- ---------- ----------
1003 tom 90 1
1005 tonny 90 1
1004 jeson 80 2
1002 jack 80 2
1001 alex 70 3
1006 hans 50 4
6 rows selected
row_number() over()
QL> select stuid,name,score,row_number() over(order by score desc) as rank from tblscore;
STUID NAME SCORE RANK
---------- -------------------------------------------------- ---------- ----------
1003 tom 90 1
1005 tonny 90 2
1004 jeson 80 3
1002 jack 80 4
1001 alex 70 5
1006 hans 50 6
6 rows selected
rank() over()
SQL> select stuid,name,score,rank() over(order by score desc) as rank from tblscore;
STUID NAME SCORE RANK
---------- -------------------------------------------------- ---------- ----------
1003 tom 90 1
1005 tonny 90 1
1004 jeson 80 3
1002 jack 80 3
1001 alex 70 5
1006 hans 50 6
6 rows selected
2.count() over()：计数
select empno,ename,mgr,sal,deptno,count(*) over() from emp;--总计数
select empno,ename,mgr,sal,deptno,count(*) over(order by empno) from emp;--按照empno累计计数
select empno,ename,mgr,sal,deptno,count(*) over(partition by deptno) from emp;--按照deptno分组计数
select empno,ename,mgr,sal,deptno,count(*) over(partition by deptno order by empno) from emp;--按照deptno分组并累计计数
注意加order by和不加order by 的区别：
加了order by统计的是第一行到当前行的总数， 但是不加order by统计的是整个数据的总数
select empno,ename,mgr,sal,deptno,count(EMPNO) over(partition by deptno) from emp;
EMPNO ENAME MGR SAL DEPTNO COUNT(EMPNO)OVER(PARTITIONBYDEPTNO)
---------- -------------------- ---------- ---------- ---------- -----------------------------------
7839 KING 5000 10 3
7782 CLARK 7839 2450 10 3
7934 MILLER 7782 1300 10 3
7902 FORD 7566 3000 20 3
7369 SMITH 7902 800 20 3
7566 JONES 7839 2975 20 3
7900 JAMES 7698 950 30 6
7844 TURNER 7698 1500 30 6
7654 MARTIN 7698 1250 30 6
7521 WARD 7698 1250 30 6
7499 ALLEN 7698 1600 30 6
7698 BLAKE 7839 2850 30 6
select empno,ename,mgr,sal,deptno,count(EMPNO) over(partition by deptno order by empno) from emp;
EMPNO ENAME MGR SAL DEPTNO COUNT(EMPNO)OVER(PARTITIONBYDEPTNOORDERBYEMPNO)
---------- -------------------- ---------- ---------- ---------- -----------------------------------------------
7782 CLARK 7839 2450 10 1
7839 KING 5000 10 2
7934 MILLER 7782 1300 10 3
7369 SMITH 7902 800 20 1
7566 JONES 7839 2975 20 2
7902 FORD 7566 3000 20 3
7499 ALLEN 7698 1600 30 1
7521 WARD 7698 1250 30 2
7654 MARTIN 7698 1250 30 3
7698 BLAKE 7839 2850 30 4
7844 TURNER 7698 1500 30 5
7900 JAMES 7698 950 30 6
3.sum() over()：求和
select empno,ename,mgr,sal,deptno,sum(sal) over() from emp;--总和
select empno,ename,mgr,sal,deptno,sum(sal) over(order by empno) from emp;--按empno累计求和
select empno,ename,mgr,sal,deptno,sum(sal) over(partition by deptno) from emp;--按照deptno分组求和
select empno,ename,mgr,sal,deptno,sum(sal) over(partition by deptno order by empno) from emp;--按照deptno分组并按empno累计求和
4.avg() over()：求平均
select empno,ename,mgr,sal,deptno,avg(sal) over() from emp;--总平均
select empno,ename,mgr,sal,deptno,avg(sal) over(order by empno) from emp;--按照empno累计平均
select empno,ename,mgr,sal,deptno,avg(sal) over(partition by deptno) from emp;--按照deptno分组求平均
select empno,ename,mgr,sal,deptno,avg(sal) over(partition by deptno order by empno) from emp;--按照deptno分组并按empno一个个累计求平均
5.min() over()；max() over()：求最小最大
select empno,ename,mgr,sal,deptno
,min(sal) over() 最小金额
,max(sal) over() 最大金额 from emp;--总最小（大）
select empno,ename,mgr,sal,deptno
,min(sal) over(order by empno) 最小金额
,max(sal) over(order by empno) 最大金额 from emp;--按empno排序并一个个递增后的最小（大）
select empno,ename,mgr,sal,deptno
,min(sal) over(partition by deptno) 最小金额
,max(sal) over(partition by deptno) 最大金额 from emp;--按deptno分组后的最小（大）
select empno,ename,mgr,sal,deptno
,min(sal) over(partition by deptno order by empno) 最小金额
,max(sal) over(partition by deptno order by empno) 最大金额 from emp;--组内、递增累计后的最小（大）
6.ntile(n) over()：
将数据等分成n组（不够等分的按顺序添加到每个组内）；
注：必须要加order by
select empno,ename,mgr,sal,deptno,ntile(3) over(order by empno) from emp;--将数据等分3组（不够等分的按顺序添加到每个组内）
select empno,ename,mgr,sal,deptno,ntile(3) over(partition by deptno order by empno) from emp;--组内再将数据均分3组
7.first_value() over()/last_value() over()：
first_value() over():取对应第一条记录；
last_value() over()：取对应最后一条记录（可加 ignore nulls 空值填充）向下（上）找最近的不为空的值
还有：nth_value(value any, nth integer)：返回窗口框架中的指定值，如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值
select empno,ename,mgr,sal,deptno
,first_value(sal ignore nulls) over() 整表sal的第一条记录
,last_value(sal ignore nulls) over() 整表sal的最后一条记录 from emp;
select empno,ename,mgr,sal,deptno
,first_value(ename) over(order by empno) 第一条ename记录
,last_value(ename) over(order by empno) 最后一条ename记录 from emp;--按empno顺序第一、最后一条数据
select empno,ename,mgr,sal,deptno
,first_value(sal) over(partition by deptno) 第一条记录
,last_value(sal) over(partition by deptno) 最后一条记录 from emp;--部门组内数据第一、最后一条
select empno,ename,mgr,sal,deptno
,first_value(sal) over(partition by deptno order by empno) 第一条记录
,last_value(sal) over(partition by deptno order by empno) 最后一条记录 from emp;--部门组内、递增数据第一、最后一条
8.keep (dense_rank first/last order by ...)over():
配合max()/min()取集合内第一或最后一条
select empno,ename,mgr,sal,deptno
,max(sal) keep(dense_rank first order by sal desc)over() 第一条记录
,max(sal) keep(dense_rank last order by sal desc)over() 最后一条记录 from emp;--取集合内第一或最后一条
select empno,ename,mgr,sal,deptno
,max(sal) keep(dense_rank first order by sal desc)over(partition by deptno) 第一条记录
,max(sal) keep(dense_rank last order by sal desc)over(partition by deptno) 最后一条记录 from emp;--组内排序后第一或最后一条
9.lag(column_name,n,若首行无填充默认为null) over()
：取出前n行数据；lead() over() ：取出前(后)第n行数据；注：必须要加order by排序（11g中支持ignore nulls）
select empno,ename,mgr,sal,deptno
,lag(sal,1) over(order by empno) sal的上一条记录
,lead(sal,1) over(order by empno) sal的下一条记录 from emp;--取出前(后)第1行数据
select empno,ename,mgr,sal,deptno
,lag(sal,2,0) over(partition by deptno order by empno) sal的上一条记录
,lead(sal,2,0) over(partition by deptno order by empno) sal的下一条记录 from emp;--取出组内前(后)第2行数据
10.ratio_to_report(a) over(partition by b) ：
求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段；注：禁用order by
简单理解就是a/b
select empno,ename,mgr,sal,deptno
,ratio_to_report(1) over() 比上总行数
,ratio_to_report(1) over(partition by deptno) 比上组内行数 from emp;--1/总（组内）行数
select empno,ename,mgr,sal,deptno
,ratio_to_report(sal) over() sal占总比
,ratio_to_report(sal) over(partition by deptno) sal组内占比 from emp;--金额占比
11.percent_rank() over():
（所在序号-1）/（总行数-1） 注：必须要加order by排序
12.cume_dist() over() ：
所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置
select empno,ename,mgr,sal,deptno,cume_dist() over(order by sal) 行数总比 from emp;
select empno,ename,mgr,sal,deptno,cume_dist() over(partition by deptno order by sal) 组内行数比 from emp;
13.precentile_cont( x ) within group(order by ...) over()：
over()中partition by可选，order by 不可选
x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则取上下对应两个值的平均值：
select empno,ename,mgr,sal,deptno
,percentile_cont(0.5) within group(order by sal) over() Percentile_Cont
,percent_rank() over(order by sal) Percent_Rank from emp;
--Percentile_Cont输入百分比为0.5，则他在对应Percent_Rank：0.46；0.53之间，值就取他两对应值的平均数（1500+1600）/2=1550下面同理
select empno,ename,mgr,sal,deptno
,percentile_cont(0.5) within group(order by sal) over(partition by deptno) Percentile_Cont
,percent_rank() over(partition by deptno order by sal) Percent_Rank from emp;
14.stddev() over()：
计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选
stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选
stddev_pop() over()：计算总体标准差，partition by 可选，order by 可选
select empno,ename,mgr,sal,deptno
,stddev(sal) over() 所有记录样本标准差1
,stddev_samp(sal) over() 所有记录样本标准差2
,stddev_pop(sal) over() 所有记录总体标准差
from emp;
select empno,ename,mgr,sal,deptno
,stddev(sal) over(order by empno) 累加的样本标准差1
,stddev_samp(sal) over(order by empno) 累加的样本标准差2
,stddev_pop(sal) over(order by empno) 累加的总体标准差
from emp;
select empno,ename,mgr,sal,deptno
,stddev(sal) over(partition by deptno) 组内样本标准差1
,stddev_samp(sal) over(partition by deptno) 组内样本标准差2
,stddev_pop(sal) over(partition by deptno) 组内总体标准差
from emp;
select empno,ename,mgr,sal,deptno
,stddev(sal) over(partition by deptno order by empno) 计算组内累加的样本标准差1
,stddev_samp(sal) over(partition by deptno order by empno) 计算组内累加的样本标准差2
,stddev_pop(sal) over(partition by deptno order by empno) 计算组内累加的样本总体标准差
from emp;
15.variance() over()：
计算样本方差，只有一行数据时返回0，partition by 可选，order by 可选
var_samp() over()：计算样本方差，只有一行数据时返回null，partition by 可选，order by 可选
var_pop() over()：计算总体方差，partition by 可选，order by 可选
select empno,ename,mgr,sal,deptno
,variance(sal) over() 所有记录样本方差1
,var_samp(sal) over() 所有记录样本方差2
,var_pop(sal) over() 所有记录总体方差
from emp;
select empno,ename,mgr,sal,deptno
,variance(sal) over(order by empno) 累加的样本方差1
,var_samp(sal) over(order by empno) 累加的样本方差2
,var_pop(sal) over(order by empno) 累加的总体方差
from emp;
select empno,ename,mgr,sal,deptno
,variance(sal) over(partition by deptno) 组内样本方差1
,var_samp(sal) over(partition by deptno) 组内样本方差2
,var_pop(sal) over(partition by deptno) 组内总体方差
from emp;
select empno,ename,mgr,sal,deptno
,variance(sal) over(partition by deptno order by empno) 计算组内累加的样本方差1
,var_samp(sal) over(partition by deptno order by empno) 计算组内累加的样本方差2
,var_pop(sal) over(partition by deptno order by empno) 计算组内累加的样本总体方差
from emp;
--stddev()=sqrt(variance()) sqrt()--求开方
--stddev_samp()=sqrt(var_samp())
--stddec_pop=sqrt(var_pop())
16.covar_samp over()：
返回一对表达式的样本协方差，partition by 可选，order by 可选
covar_pop over()： 返回一堆表达式的总体协方差，partition by 可选，order by 可选
corr() over() ：返回一对表达式的相关系数，partition by 可选，order by 可选
REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用
decode函数
decode函数类似于case when
decode函数可以实现行列转换
在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。
如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
需要注意的是，这里的if、then及else 都可以是函数或计算表达式。
列1 按照行不列出学生信息
Create table student(id number,name varchar2(10),sex char(1));
Insert into student values(1, '张', '1');
Insert into student values(2, '王', '2');
Insert into student values(3, '李', '1');
1、decode实现
Select name ,decode(sex, '1','男生', '2','女生')
from student;
select id,name,decode(sex,'1','m','2','w') from student;
2、case实现
select id,name,
case sex
when '1' then '男'
when '2' then '女'
end 性别
from student;
列2、取下表中m对应的n或者f中较大的数据
create table sales(m number,n number,f number);
insert into sales values(01,10,20);
insert into sales values(02,50,60);
insert into sales values(03,30,10);
case实现:
select m,case
when n-f>0 then n
else f
end
from sales;
第二种写法
select m,case sign(n-f)
when 1 then n
else f
end
from sales;
测试使用表达式：
select m,case n-f
when -10 then n
else f
end
from sales;
不能使用下面这种>,<,应该使用函数sign替换
select m,case n-f
when >0 then n
else f
end
from sales;
decode实现：
select m,decode(sign(n-f),1,n,f) from sales;
(sign)函数 如果sign(x)中的x>0 返回1
x>0 返回1
x<0 返回-1
x=0 返回0
decode实现行和列的转换
一、需要将格式1转换为格式2 的表样式
格式1：
商品名称 季度 销售额
---------- ---- ----------
电视机 01 100
电视机 02 200
电视机 03 300
空调 01 50
空调 02 150
空调 03 180
格式2：
商品名称 一季度 二季度 三季度 四季度
---------- ---------- ---------- ---------- ----------
电视机 100 200 300 0
空调 50 150 180 0
实例：
create table 销售(商品名称 varchar2(10), 季度 char(2), 销售额 number);
insert into 销售 values('电视机', '01', 100);
insert into 销售 values('电视机', '02', 200);
insert into 销售 values('电视机', '03', 300);
insert into 销售 values('空调', '01', 50);
insert into 销售 values('空调', '02', 150);
insert into 销售 values('空调', '03', 180);
decode实现行列转换
select 商品名称,sum(decode(季度,'01',销售额)) as 第一季度,
sum(decode(季度,'02',销售额)) as 第二季度,
sum(decode(季度,'03',销售额)) as 第三季度,
sum(decode(季度,'04',销售额)) as 第四季度
from 销售
group by 商品名称;
注意group by 分组函数的使用特点：
在分组函数的select列中只能是group by 后面的分组列，或者聚合函数（比如count(),sum(),avg()等）。
否则会出错。
Oracle 行转列 动态转换的列
pivot进行静态行转列
--建表
--drop table SalesList;
create table SalesList(
keHu varchar2(20), --客户
shangPin varchar2(20), --商品名称
salesNum number(8) --销售数量
);
--插入数据
declare
--谈几个客户
cursor lr_kh is
select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual
connect by level <= 4;
--进点货
cursor lr_sp is
select regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual
connect by level <= 4;
begin
--循环插入
for v_kh in lr_kh loop
for v_sp in lr_sp loop
insert into SalesList
select v_kh.keHu, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
end loop;
end loop;
commit;
end;
/
pivot进行转换的SQL
求不同的客户购买的不同商品的最高价格
--行转列
select *
from SalesList pivot(
max(salesNum) for shangPin in ( --shangPin 即要转成列的字段
'上衣' as 上衣, --max(salesNum) 此处必须为聚合函数，
'裤子' as 裤子, --in () 对要转成列的每一个值指定一个列名
'袜子' as 袜子,
'帽子' as 帽子
)
)
where 1 = 1; --这里可以写查询条件，没有可以直接不要where
KEHU 上衣 裤子 袜子 帽子
---------------------------------------- ---------- ---------- ---------- ----------
张三 33 24 49 38
王五 35 35 45 39
赵六 35 42 20 41
李四 19 46 29 16
求不同的客户购买的不同商品的总价
select *
from SalesList pivot(
sum(salesNum) for shangPin in ( --shangPin 即要转成列的字段
'上衣' as 上衣, --max(salesNum) 此处必须为聚合函数，
'裤子' as 裤子, --in () 对要转成列的每一个值指定一个列名
'袜子' as 袜子,
'帽子' as 帽子
)
);
pivot进行动态行转列
其实原理很简单，就是通过动态sql，去把你不愿意写，或者说是不确定的转换列数，通过查询查出来，拼接进去，然后执行拼接后的sql，创建视图。
创建存储过程：
create or replace procedure p_RowsToCols(as_sql in varchar2 --源数据的查询sql
,as_sql_cols in varchar2 --动态转换列的查询sql,要求转为列的那列，字段名必须为cols，支持排序
,as_aggCol in varchar2 --对应pivot函数的 聚合函数
,as_changeCol in varchar2 --源数据中，要转为列的字段名
,as_viewName in varchar2 --结果输出的视图名，执行完后查此视图即可
) is
ls_sql varchar2(4000);
ls_in varchar2(4000);
begin
--拼接in的内容
ls_sql := 'select listagg(''''''''||cols||'''''' "''||cols||''"'', '','')within group(order by rn) ' ||
'from (select rownum rn, cols from (' || as_sql_cols || '))';
execute immediate ls_sql
into ls_in;
--创建视图
ls_sql := 'create or replace view ' || as_viewName ||' as ' ||
'select * from (' || as_sql || ') ' ||
'pivot (' || as_aggCol || ' for ' || as_changeCol || ' in (' || ls_in || '))';
execute immediate ls_sql;
end p_RowsToCols;
--建表
--drop table SalesList;
create table SalesList(
keHu varchar2(20), --客户
shangPinId number(8), --商品Id
shangPin varchar2(20), --商品名称
salesNum number(8) --销售数量
);
--插入数据
declare
--谈几个客户
cursor lr_kh is
select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual
connect by level <= 4;
--进点货
cursor lr_sp is
select level shangPinId, regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual
connect by level <= 4;
begin
--循环插入
for v_kh in lr_kh loop
for v_sp in lr_sp loop
insert into SalesList
select v_kh.keHu, v_sp.shangPinId, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
end loop;
end loop;
commit;
end;
/
--看下源数据
select * from salesList a;
--固定行转列
select *
from (select kehu, shangPin, salesNum from salesList) pivot(
max(salesNum) for shangPin in (
'上衣' as 上衣,
'裤子' as 裤子,
'袜子' as 袜子,
'帽子' as 帽子
)
);
--动态行转列
call p_RowsToCols('select keHu, shangPin, salesNum from salesList',
'select distinct shangPinId, shangPin cols from salesList order by shangPinId',
'max(salesNum)',
'shangPin',
'sales_RowsToCols');
select * from sales_RowsToCols;
查找两张表的数据，用一个标识区分
a
id name sex
b
id other
查找a表中id在b表和不在b表中的数据,并使用flag标识数据的来源
select id,name,sex,1 flag from a where id in(select id from b)
union
select id,name,sex,2 flag from a where id not in(select id from b);
本文来自博客园，作者：数据库小白（专注），转载请注明原文链接：https://www.cnblogs.com/xulinforDB/p/17496452.html
