SQL Cookbook 系列 - 报表和数据仓库运算

1. 将结果集转置为一行
2. 将结果集转置为多行
3. 反向转置结果集
4. 将结果集反向转置为一列
5. 抑制结果集中的重复值
6. 转置结果集以利于跨行计算
7. 创建固定大小的数据桶
8. 创建预定数目的桶
9. 创建横向直方图
10. 创建纵向直方图
11. 返回未包含在group by中的列
12. 计算简单的小计
13. 计算所有表达式组合的小计
14. 判别非小计的行
15. 使用case表达式给行做标记
16. 创建稀疏矩阵
17. 按时间单位给行分组
18. 对不同组/分区同时实现聚集
19. 对移动范围内的值进行聚集
20. 转置带小计的结果集

 

1.将结果集转置为一行，将一列数据转换为一行

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,

sum(case when deptno=20 then 1 else 0 end) as deptno_20

from emp;

Note：根据原始结果集当中某一列的值进行转置，当然可以是多列，那么就要忍受更加复杂的逻辑了

 

2.将结果集转置为多行，根据一列数据值的分组转化为多行

db2/oracle/sqlserver:

select max(case when job='worker' then ename else null end) as workers,

max(case when jon='manager' then ename else null end) as managers

from (

select job,ename,row_number()over(partition by job order by ename) rn

from emp

) group by rn;

mysql/postgresql:

select max(case when job='worker' then ename else null end) as workers,

max(case when jon='manager' then ename else null end) as managers

from (

select job,ename,

(select count(*) from emp d

where e.job=d.job and e.empno<d.empno) as rn

from emp e

) group by rn;

Note：这个是第一个案例的重复，只不过更加复杂一点

 

3.反向转置结果集，将转换后的结果集转换回去

select dept.deptno,

case dept.deptno

when 10 then emp_cnts.deptno_10

when 20 then emp_cnts.deptno_20

end as counts_by_dept

from (

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,

sum(case when deptno=20 then 1 else 0 end) as deptno_20

from emp

) emp_cnts,(

select deptno from dept where deptno<=20

) dept;

Note：这个只是为了练习技术，效果吗一点也没有变化

 

4.将结果集反向转置为一列

db2/oracle/sqlserver:

select case rn when 1 then ename

when 2 then job

when 3 then cast(sal as char(4))

end emps from (

select e.ename,e.job,e.sal,

row_number()over(partition by e.empno

order by empno) rn

from emp e,(

select * from emp where job='worker'

) four_rows where e.deptno=10

) x;

Note：将某一个主题的信息进行罗列，例如个人信息

 

5.抑制结果集中的重复值

db2/sqlserver:

select case when empno=min_empno then deptno else null

end deptno,ename from (

select deptno,min(empno)over(partition by deptno)

min_empno,empno,ename

from emp

);

oracle:

select to_number(decode(lag(deptno)over(order by deptno),deptno,null,deptno))

deptno,ename

from emp;

Note:去重的另一种思路

 

6.转置结果集以利于跨行计算，先聚集，再计算

select d20_sal-d10_sal as d20_10_diff,

d30_sal-d20_sal as d30_20_diff

from (

select sum(case when deptno=10 then sal end) as d10_sal,

sum(case when deptno=20 then sal end) as d20_sal,

sum(case when deptno=30 then sal end) as d30_sal

from emp

);

Note:基于部分计算之后的再次计算

 

7.创建固定大小的数据桶

db2/oracle/sqlserver:

select ceil(row_number()over(order by empno)/5.0) grp,

empno,ename

from emp;

mysql/postgresql:

select ceil(rnk/5.0) as grp,empno,ename from (

select e.empno,e.ename,

(select count(*) from emp d where e.empno<d.empno)+1 as rnk

from emp e

) order by grp;

Note：第一见识到数据仓库的内容

 

8.创建预定数目的桶

db2:

select mod(row_number()over(order by empno),4)+1 grp,

empno,ename from emp order by 1;

oracle/sqlserver:

select ntile(4)over(order by empno) grp,

empno,ename

from emp;

mysql/postgresql:

select mod(count(*),4)+1 as grp,

e.empno,e.ename

from emp e,emp d where e.empno>=d.empno

group by e.empno,e.ename order by 1;

Note:看上去并不复杂

 

9.创建横向直方图

db2:

select deptno,repeat('*',count(*)) as cnt

from emp group by deptno;

oracle/postgresql/mysql:

select deptno,lpad('*',count(*),'*') as cnt

from emp group by deptno;

sqlserver:

select deptno,replicate('*',count(*)) cnt

from emp group by deptno;

Note：用*代替直方图

 

10.创建纵向直方图

db2/oracle/sqlserver:

select max(deptno_10) d10,

max(deptno_20) d20

from (

select row_number()over(partition by deptno order by empno) rn,

case when deptno=10 then '*' else null end deptno_10

case when deptno=20 then '*' else null end deptno_20

from emp

) group by rn order by 1 desc,2 desc;

mysql/postgresql:

select max(deptno_10) d10,

max(deptno_20) d20

from (

select (select count(*) from emp d where e.deptno=d.deptno and e.empno<d.empno) as rn,

case when deptno=10 then '*' else null end deptno_10

case when deptno=20 then '*' else null end deptno_20

from emp e

) group by rn order by 1 desc,2 desc;

Note：先对数据做处理，然后进行排列

 

11.返回未包含在group by中的列

db2/oracle/sqlserver:

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

) where sal in (max_by_dept,max_by_job,min_by_dept,min_by_job);

mysql/postgresql:

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,

(select max(sal) from emp d where d.deptno=e.deptno) as max_by_dept,

(select max(sal) from emp d where d.job=e.job) as max_by_job,

(select min(sal) from emp d where d.deptno=e.deptno) as min_by_dept,

(select min(sal) from emp d where d.job=e.job) as min_by_job

from emp e

) where sal in (max_by_dept,max_by_job,min_by_dept,min_by_job);

Note：获取极值列

 

12.计算简单的小计

db2/oracle:

select case grouping(job) when 0 then job

else 'total' end job,

sum(sal) sal

from emp group by rollup(job);

mysql/sqlserver:

select coalesce(job,'total') job,sum(sal) sal

from emp group by job with rollup;

postgresql:

select job,sum(sal) as sal

from emp group by job

union all

select 'total',sum(sal) from emp;

Note：分组统计

13.计算所有表达式组合的小计

db2:

select deptno,job,

case cast(grouping(deptno) as char(1))||cast(grouping(job) as char(1))

when '00' then 'total by dept and job'

when '10' then 'total by job'

when '01' then 'total by dept'

when '11' then 'total for table'

end category,sum(sal)

from emp group by cube(deptno,job)

order by grouping(job),grouping(deptno);

oracle:

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 'total for table'

end category,sum(sal)

from emp group by cube(deptno,job)

order by grouping(job),grouping(deptno);

sqlserver:

select deptno,job,

case cast(grouping(deptno) as char(1))+cast(grouping(job) as char(1))

when '00' then 'total by dept and job'

when '10' then 'total by job'

when '01' then 'total by dept'

when '11' then 'total for table'

end category,sum(sal)

from emp group by deptno,job with cube

order by grouping(job),grouping(deptno);

mysql/postgresql:

select deptno,job,'total by dept and job' as category,sum(sal) as sal

from emp group by deptno,job

union all

select null,job,'total by job',sum(sal)

from emp group by job

union all

select deptno,null,'total by dept',sum(sal)

from emp group by deptno

union all

select null,null,'toatl for table',sum(sal) from emp;

Note：同时对多个分组进行统计

 

14.判别非小计的行

db2/oracle:

select deptno,job,sum(sal) sal,

grouping(deptno) deptno_subtotals,

grouping(job) job_subtotals

from emp group by cube(deptno,job);

sqlserver:

select deptno,job,sum(sal) sal,

grouping(deptno) deptno_subtotals,

grouping(job) job_subtotals

from emp group by deptno,job with cube;

Note：第一次见

 

15.使用case表达式给行做标记

select ename, case when job='worker' then 1 else 0

end as is_worker

from emp order by 2;

Note：没有什么难度，通用用法

 

16.创建稀疏矩阵

select max(case deptno when 10 then ename end) d10,

max(case deptno when 20 then ename end) d20

from (

select deptno,job,ename,

row_number()over(partition by deptno order by empno) rn

from emp

) group by rn;

Note：转置之后的统计

 

17.按时间单位给行分组

select hr,grp,sum(trx_cnt) total from (

select trx_date,trx_cnt,to_number(to_char(trx_date,'hh24')) hr,

cail(to_number(to_char(trx_date-1/24/60/60,'miss'))/5.0) grp

from tex_log

) group by hr,grp;

Note：用在日志追踪上

 

18.对不同组、分区同时实现聚集

db2/oracle/sqlserver:

select ename,deptno,count(*)over(partition by deptno) deptno_cnt,

job,count(*)over(partition by job) job_cnt,

count(*)over() total

from emp;

mysql/postgresql:

select e.ename,e.deptno,

(select count(*) from emp d where d.deptno=e.deptno) as deptno_cnt,

e.job,

(select count(*) from emp d where d.job=e.job) as job_cnt,

(select count(*) from emp) as total

from emp e;

Note:同时进行的多个分组、分区聚集

 

19.对移动范围的值进行聚集

db2:

select hiredate,sal,

sum(sal)over(order by days(hiredate) range between 90 preceding and current row)

spending_pattern

from emp e;

oracle:

select hiredate,sal,

sum(sal)over(order by hiredate range between 90 preceding and current row)

spending_pattern

from emp e;

mysql/postgresql/sqlserver:

select e.hiredate,e.sal,

(select sum(sal) from emp d where d.hiredate

between e.hiredate-90 and e.hiredate) as spending_pattern

from emp e order by 1;

Note：对动态范围内的数据进行的统计

 

20.转置带小计的结果集

db2/oracle:

select mgr,sum(case deptno when 10 then sal else 0 end) dept10,

sum(case deptno when 20 then sal else 0 end) dept20,

sum(case flag when '11' then sal else null end) 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 deptno,mgr with rollup

) group by mgr;

sqlserver:

select mgr,sum(case deptno when 10 then sal else 0 end) dept10,

sum(case deptno when 20 then sal else 0 end) dept20,

sum(case flag when '11' then sal else null end) 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 deptno,mgr with rollup

) group by mgr;

Note：只是多了一层嵌套