SQL WITH子句、分析函数

SQL WITH子句、分析函数

2018年10月23日 01:06:58 362188 阅读数 457更多

WITH子句
WITH子句提供了一种定义临时表的操作方法，如果在一个查询之中，要反复使用到一些数据，那么就可以将这些数据定义在WITH子句之中。

子查询 —— 范例
使用WITH子句将emp表中的数据定义为临时表
with e as (select * from emp)select * from e;
/子查询 —— 范例查询每个部门的编号、名称、位置、部门平均工资/
with a as(select deptno, round(avg(sal) ,1) avgsal,count(empno) count
from emp
group by deptno)
select a.avgsal,a.count,d.dname,d.loc from dept d,a
where d.deptno=a.deptno;
/查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、
工资、部门编号、部门名称，显示的结果按照部门编号进行/
with a as (select deptno,max(sal) msal from emp group by deptno)
select e.empno,e.ename,e.job,e.hiredate,e.sal,d.deptno,d.dname
from emp e,dept d, a
where e.deptno=d.deptno(+)
and e.deptno=a.deptno
and e.sal=a.msal
order by e.deptno；
找出在⼀操场进⾏⽐赛的各项⽬名称及其冠军的姓名
with a as(select itemid,max(mark) mmark from grade group by itemid)
select i.itemid,i.itemname,s.name,g.mark from item i,sporter s,a,grade g
where s.sporterid=g.sporterid
and i.itemid=g.itemid
and i.location=‘一操场’
and a.itemid=g.itemid
and mmark=g.mark;

小结
WITH子句可以构建一张临时表供查询使用。

行列转换

了解行列转换的基本概念；了解pivot和unpivot函数的
行、列转换的基本形式
查询每个部门中各个职位的总工资 —— 按照部门编号及职位进行分组
select deptno ,job,sum(sal) from emp group by deptno ,job;
行列转换如下：
select deptno ,
sum(decode(job,‘PRESIDENT’, sal , 0 )) president_job ,
sum(decode(job,‘MANAGER’, sal , 0)) manager_job ,
sum(decode(job ,‘ANALYST’, sal , 0 )) analyst_job ,
sum(decode(job ,‘CLERK’, sal, 0 )) clerk_job ,
sum(decode(job ,‘SALESMAN’, sal , 0)) salesman_job
from emp
group by deptno;
pivot函数(了解形式即可)
在Oracle 11g版本之后，专门增加了pivot和unpivot两个转换函数pivot

SELECT * | 列 [别名] … FROM 子查询
PIVOT (统计函数(列)s FOR 转换列名称 IN (内容1 [[AS] 别名] ,内容2 [[AS] 别名] ,… 内容n [[AS] 别名]))
[WHERE 条件(s)]
[GROUP BY 分组字段1 , 分组字段2 , ….]
[HAVING 过滤条件(s)]
[ORDER BY 排序字段 ASC|DESC]

子查询：此处规定了在PIVOT（）函数操作过程中，所需要使用到的数据（设置子查询确定行和列）
统计函数（列）：在转换过程中，设置要进行统计的数据列及统计函数，可以设置多个统计函数。
FOR转换列名称：将子查询中返回的制定数据变为显示的

select * from (select deptno , job , sal from emp)
pivot (sum(sal)
for job in (‘PRESIDENT’ as president_job ,
‘MANAGER’ as manager_job ,
‘ANALYST’ as analyst_job ,
‘CLERK’ as clerk_job ,
‘SALESMAN’ as salesman_job))
order by deptno;

设置多个统计函数。
查询出每个部门不同职位的总工资，和每个部门不同职位的最高
select * from (select deptno , job , sal from emp)
pivot (sum(sal) as sum_sal ,
max(sal) as sum_max
for job in (‘PRESIDENT’ as president_job ,‘MANAGER’ as manager_job ,
‘ANALYST’ as analyst_job ,‘CLERK’ as clerk_job ,
‘SALESMAN’ as salesman_job))
order by deptno;

unpivot函数
include nulls：列变为行转换之后保留所有的null数据；
exclude nulls（默认）：列变为行转换之后不保留null数

SELECT * | 列 [别名] … FROM 子查询
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS](统计函数(列)s
FOR 转换列名称 IN (内容1 [[AS] 别名] ,内容2 [[AS] 别名] ,… 内容n [[AS] 别名]))[WHERE 条件(s)]
[GROUP BY 分组字段1 , 分组字段2 , ….]
[HAVING 过滤条件(s)]
[ORDER BY 排序字段 ASC|DESC]

行列转换 —— 范例 默认不保留null
with temp as (select * from (select deptno , job , sal from emp)
pivot (sum(sal)
for job in (‘PRESIDENT’ as president_job ,
‘MANAGER’ as manager_job ,‘ANALYST’ as analyst_job ,
‘CLERK’ as clerk_job ,‘SALESMAN’ as salesman_job))
order by deptno )
select * from temp
unpivot (sal_sum
for job in (president_job as ‘PRESIDENT’,manager_job as ‘MANAGER’,
analyst_job as ‘ANALYST’,clerk_job as ‘CLERK’,salesman_job as ‘SALESMAN’) )
order by deptno;

行列转换 —— 范例 保留null
with temp as (select * from (select deptno , job , sal from emp)
pivot (sum(sal)
for job in (‘PRESIDENT’ as president_job ,
‘MANAGER’ as manager_job ,‘ANALYST’ as analyst_job ,
‘CLERK’ as clerk_job ,‘SALESMAN’ as salesman_job) )
order by deptno )
select * from temp
unpivot include nulls(sal_sum
for job in (president_job as ‘PRESIDENT’,manager_job as ‘MANAGER’,
analyst_job as ‘ANALYST’,clerk_job as ‘CLERK’,salesman_job as ‘SALESMAN’) )
order by deptno;

分析函数

传统SQL的问题
虽然利用SQL之中提供的各种查询命令可以完成大部分的查询要求，但是还有许多功能是无法实现的，例如：计算运行总量：逐一累加当前行与其之前行的每行记录数据；查找当前行数据占总数据的百分比；分区显示：按照不同的部门或职位进行排列、统计；计算流动数据行的平均值

分析函数的基本语法基本语法：
函数名称([参数 , …])
OVER (PARTITION BY 子句 字段 , …
[ORDER BY 子句 字段 , … [ASC | DESC]
[NULLS FIRST | NULLS LAST]
[WINDOWING 子句]) ;
本语法组成如下：函数名称：类似于统计函数（COUNT()、SUM()等），但是在此时有了更多的函数支持；
OVER子句：为分析函数指明一个查询结果集，此语句在SELECT子句之中使用；
PARTITION BY子句：将一个简单的结果集分为N组（或称为分区），而后按照不同的组对数据进行统计；
ORDER BY 子句：明确指明数据在每个组内的排列顺序，分析函数的结果与排列顺序有关；
NULLS FIRST | NULLS LAST：表示返回数据行中包含NULL值是出现在排序序列前还是尾；
WINDOWING 子句（代名词）：给出在定义变化的固定的数据窗口方法，分析函数将对此数据进行操

组合顺序
在分析函数之中存在有三种子句：
PARTITION BY、ORDER BY、WINDOWING，而这三种子句的组合顺序有如下几种：
第一种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句 , ORDER BY 子句 , WINDOWING子句)；
第二种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句 , ORDER BY 子句)；
第三种组合：函数名称([参数 ,…]) OVER(PARTITION BY 子句)；
第四种组合：函数名称([参数 ,…]) OVER(ORDER BY 子句 , WINDOWING子句)；
第五种组合：函数名称([参数 ,…]) OVER(ORDER BY 子句)；
第六种组合：函数名称([参数 ,…]) OVER(）；

分析函数 —— 范例
使用PARTITION子句
select deptno , ename, sal ,sum(sal) over (partition by deptno) sum from emp;
不使用PARTITION进行分区，直接利用OVER子句操作
将所有数据变为一个分区
select deptno , ename, sal ,sum(sal) over () sum from emp;
通过PARTITION设置多个分区字段
select deptno , ename, sal ,job ,sum(sal) over (partition by deptno ,job) sum from emp;
观察ORDER BY子句（用于设置在每个分区内数据的排序结果）
select deptno , ename, sal ,rank() over (partition by deptno order by sal desc) rk from emp;
设置多个排序字段（sal和hiredate）
select deptno , ename, sal , hiredate ,
rank() over (partition by deptno order by sal , hiredate desc) rk from emp;
直接利用ORDER BY排序所有数据
select deptno , ename, sal , hiredate ,sum(sal) over (order by ename desc) sum from emp;

ORDER BY子句选项
在ORDER BY子句之中还存在两个选项：
NULLS FIRST和NULLS LAST
其中NULLS FIRST表示在进行排序前，出现null值的数据行排列在最前面，而NULLS LAST则表示出现的null值数据行排列在最后面

使用NULLS LAST
select deptno , ename, sal , comm ,rank() over (order by comm desc nulls last) rk ,sum(sal) over (order by comm desc nulls last) sum from emp;

查询雇员编号是7369的雇员姓名、职位、基本工资、部门编号、
部门的人数、平均工资、最高工资、最低工资、总工资
SELECT *FROM (SELECT empno,ename,job,sal,deptno ,COUNT(empno)
OVER (PARTITION BY deptno) count ,ROUND(AVG(sal)
OVER (PARTITION BY deptno)) avg ,SUM(sal)
OVER (PARTITION BY deptno) sum ,MAX(sal)
OVER (PARTITION BY deptno) max ,MIN(sal)
OVER (PARTITION BY deptno) min FROM emp ) temp
WHERE temp.empno=7369;

观察rank()和dense_rank()函数
select deptno,ename,sal,rank()
over (partition by deptno
order by sal) rank_result ,
dense_rank() over (partition by deptno order by sal) dense_rank_result
from emp;
使用ROW_NUMBER()函数
select deptno,ename,sal,row_number()
over (partition by deptno
order by sal) row_result_deptno ,row_number()
over (order by sal) row_result_all from emp;

计算各部门工资所占的总工资比率
select deptno ,sum(sal) ,round(ratio_to_report(sum(sal)) over () ,5) rate ,
round(ratio_to_report(sum(sal))
over () ,5) * 100 || ‘%’ precent from emp group by deptno;