报表和数据仓库运算

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