1.将结果集转至为一行
with temp as(
select 10 deptno,3 cnt from dual
union all
select 20 deptno,5 cnt from dual
union all
select 30 deptno,6 cnt from dual
)
select sum(decode(deptno,10,cnt,0)) deptno_10,
sum(decode(deptno,20,cnt,0)) deptno_20,
sum(decode(deptno,30,cnt,0)) deptno_30
from temp
2.把结果集转为多行
with temp as(
select job,ename from emp
)
select max(decode(job,'CLERK',ename,null)) clerks,
max(decode(job,'SALESMAN',ename,null)) analyst,
max(decode(job,'JEDI',ename,null)) manager,
max(decode(job,'PRESIDENT',ename,null)) presiden,
max(decode(job,'MANAGER',ename,null)) manager,
max(decode(job,'ANALYST',ename,null)) analyst
from (select job,ename,row_number() over(partition by job order by ename) rn from emp) X
group by rn
3.反向转置结果集
select dept.deptno,
case when dept.deptno = 10 then emp_cnts.deptno_10
when dept.deptno = 20 then emp_cnts.deptno_20
when dept.deptno = 30 then emp_cnts.deptno_30
end counts_by_dept
from (
select sum(case when deptno = 10 then 1 else 0 end) deptno_10,
sum(case when deptno = 20 then 1 else 0 end) deptno_20,
sum(case when deptno = 30 then 1 else 0 end) deptno_30
from emp) emp_cnts,
(select deptno from dept where deptno <= 30) dept
4.将结果集反向转置为一列
select case when rn = 1 then ename when rn = 2 then job when rn = 3 then cast(sal as char(4)) end emps
select *
from(
select e.ename,e.job,e.sal,row_number() over(partition by e.empno order by e.empno) rn from emp e,
(select * from emp where job='CLERK') four_rows) x
5.抑制结果集中的重复值
select to_number(decode(lag(deptno) over(order by deptno),deptno,null,deptno)) deptno,ename from emp
6.转置结果集以便于跨行计算
select max(decode(deptno,10,sal,null)) deptno_10,
max(decode(deptno,20,sal,null)) deptno_20,
max(decode(deptno,30,sal,null)) deptno_10
from(
select deptno,sum(sal) sal from emp
where deptno is not null group by deptno order by deptno
)
7.创建固定数据大小的数据桶
select ceil(row_number() over(order by empno)/5) grp,empno,ename from emp
8.创建预定数目的桶
select ntile(4) over(order by empno) grp,empno,ename from emp
9.创建横向直方图
select deptno,lpad('*',count(*),'*') from emp group by deptno order by deptno
10.创建纵向直方图
select max(deptno_10) deptno_10,max(deptno_20) deptno_20,max(deptno_30) deptno_30 from(
select row_number() over(partition by deptno order by empno) rn,
decode(deptno,10,'*',null) deptno_10,
decode(deptno,20,'*',null) deptno_20,
decode(deptno,30,'*',null) deptno_30
from emp
) group by rn
order by rn desc
11.返回未包含在GROUP BY中的列
select deptno,ename,job,sal,
case when sal = max_by_dept then 'TOP SAL IN DEPT'
when sal = min_by_dept then 'LOW SAL IN DEPT'
end dept_status,
case when sal = max_by_job then 'TOP SAL IN JOB'
when sal = min_by_job then 'LOW SAL IN JOB'
END job_status from(
select deptno,ename,job,sal,
max(sal) over(partition by deptno) max_by_dept,
max(sal) over(partition by job) max_by_job,
min(sal) over(partition by deptno) min_by_dept,
min(sal) over(partition by job) min_by_job
from emp) emp_sals
where sal in (max_by_dept,max_by_job,min_by_dept,min_by_job)
12.计算简单的小计
select nvl(job,'总计'),sum(sal) from emp group by rollup(job)
13.计算所有表达式组合的小计
select deptno,job,case grouping(deptno) || grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,sum(sal) sal from emp
group by cube(deptno,job)
order by grouping(deptno),grouping(job)
14.判别非小计的行
select deptno,job,sum(sal) sal,grouping(deptno) deptno_subtotals,
grouping(job) job_subtotals from emp
group by cube(deptno,job)
15.使用decode的函数给行做标记
select ename,decode(job,'CLERK',1,0) is_clerk,
decode(job,'SALSMAN',1,0) is_salsman,
decode(job,'MANAGER',1,0) is_manager,
decode(job,'PRESIDENT',1,0) is_president,
decode(job,'ANALYST',1,0) is_analyst from emp
16.创建希疏矩阵
select decode(deptno,10,ename,null) D10,decode(deptno,20,ename,null) D20,decode(deptno,30,ename,null) D30,
decode(job,'CLERK',ename,null) is_clerk,
decode(job,'SALSMAN',ename,null) is_salsman,
decode(job,'MANAGER',ename,null) is_manager,
decode(job,'PRESIDENT',ename,null) is_president,
decode(job,'ANALYST',ename,null) is_analyst from emp
17.按时间单位给行分组
with trx_log as(
select 1 trx_id,'28-JUL-2005 19:03:07' trx_date,44 trx_cnt from dual
union all
select 2 trx_id,'28-JUL-2005 19:03:08' trx_date,18 trx_cnt from dual
union all
select 3 trx_id,'28-JUL-2005 19:03:09' trx_date,23 trx_cnt from dual
union all
select 4 trx_id,'28-JUL-2005 19:03:10' trx_date,29 trx_cnt from dual
union all
select 5 trx_id,'28-JUL-2005 19:03:11' trx_date,27 trx_cnt from dual
union all
select 6 trx_id,'28-JUL-2005 19:03:12' trx_date,45 trx_cnt from dual
union all
select 7 trx_id,'28-JUL-2005 19:03:13' trx_date,45 trx_cnt from dual
union all
select 8 trx_id,'28-JUL-2005 19:03:14' trx_date,32 trx_cnt from dual
union all
select 9 trx_id,'28-JUL-2005 19:03:15' trx_date,15 trx_cnt from dual
union all
select 10 trx_id,'28-JUL-2005 19:03:16' trx_date,24 trx_cnt from dual
union all
select 11 trx_id,'28-JUL-2005 19:03:17' trx_date,47 trx_cnt from dual
union all
select 12 trx_id,'28-JUL-2005 19:03:18' trx_date,37 trx_cnt from dual
union all
select 13 trx_id,'28-JUL-2005 19:03:19' trx_date,48 trx_cnt from dual
union all
select 14 trx_id,'28-JUL-2005 19:03:20' trx_date,46 trx_cnt from dual
union all
select 15 trx_id,'28-JUL-2005 19:03:21' trx_date,44 trx_cnt from dual
union all
select 16 trx_id,'28-JUL-2005 19:03:22' trx_date,36 trx_cnt from dual
union all
select 17 trx_id,'28-JUL-2005 19:03:23' trx_date,41 trx_cnt from dual
union all
select 18 trx_id,'28-JUL-2005 19:03:24' trx_date,33 trx_cnt from dual
union all
select 19 trx_id,'28-JUL-2005 19:03:25' trx_date,19 trx_cnt from dual
union all
select 20 trx_id,'28-JUL-2005 19:03:26' trx_date,10 trx_cnt from dual
)
select ceil(trx_id/5) grp,min(trx_date) trx_start,max(trx_date) trx_end,sum(trx_cnt) total
from trx_log group by ceil(trx_id/5)
18.对不同组,分区同时实现聚集
select ename,deptno,count(empno) over(partition by deptno order by deptno) deptno_cnt,
job,count(empno) over(partition by job order by deptno) job_cnt,count(empno) over() total
from emp
19.对移动范围的值进行聚集(2天内聚集)
select hiredate,sal,
sum(sal) over(order by hiredate
range between 2 preceding and current row) spending_pattern from emp
20.转置带小计的结果集
select mgr,sum(decode(deptno,10,sal,0)) dept10,
sum(decode(deptno,20,sal,0)) dept20,
sum(decode(deptno,30,sal,0)) dept30,
sum(decode(flag,'11',sal,null)) total
from(
select deptno,mgr,sum(sal) sal,
cast(grouping(deptno) as char(1)) || cast(grouping(mgr) as char(1)) flag
from emp
where mgr is not null group by rollup(deptno,mgr)) x
group by mgr