ETL复习--2020年3月27日--函数扩展、开窗函数（分析函数）
--2020年3月27日 AM
0、函数扩展
0.1 wm_concat 同字段拼接
select wm_concat(ename) from emp;
select replace(wm_concat(ename),',','%%') from emp;
0.2 next_day
select next_day(sysdate,'星期一') from dual;--注意指代的到底是哪个周的星期几
select next_day(sysdate,'星期日') from dual;
select next_day(sysdate,'星期日')+2 from dual;
--为避免混乱，直接指定到下周的第一天“星期日”，通过加天数的方式跳到下个周的某天
--成为真正意义上的下周二
0.3 lpad() rpad() 填充函数 从左、从右填充
select job, lpad(job,10,'@'), rpad(job,10,'@') from emp;
select lpad(concat(empno,ename),15,'#') from emp;
0.4 trim() 修剪函数 默认去除左右两侧的空格
ltrim() rtrim() 用于去除数据左侧、右两侧的空格
select * from emp for update;--行级锁，开锁后直接修改表数据，仅用于练习
select ltrim(ename) from emp;
select rtrim(ename) from emp;
练习题：
将ename修改为中间字母大写，其余小写的形式，考虑字母数是奇数、偶数两种情况
select case
when mod(length(ename), 2) = 1 then
lower(substr(ename, 1, trunc(length(ename) / 2))) ||
substr(ename, trunc(length(ename) / 2) + 1, 1) ||
lower(substr(ename, trunc(length(ename) / 2) + 2))
when mod(length(ename), 2) = 0 then
lower(substr(ename, 1, length(ename) / 2 - 1)) ||
substr(ename, length(ename) / 2, 2) ||
lower(substr(ename, length(ename) / 2 + 2))
end
from emp;
1、开窗函数（分析函数）
关键词 over // partition by // order by
select e.*, avg(sal) over(partition by deptno) from emp e;
select e.*, max(sal) over(partition by deptno) from emp e;
select e.*, row_number() over(partition by deptno order by sal desc) from emp e;
--注意此处rownum的变种row_number()
select e.*, row_number() over(order by sal desc) from emp e;
--去掉partition by，没有指定分组，则将整个表作为一个组
select *
from (select e.*, row_number() over(order by sal desc) r from emp e)
where r between 6 and 10;
--轻松查询出排名区间
关键词：rank() // dense_rank() 解决排名相同的情况
select e.*, rank() over(partition by deptno order by sal desc) r
from emp e;
--允许出现并列排名，但不完美，跳过了某些名次，两个第一名后接第三名
select e.*, dense_rank() over(partition by deptno order by sal desc) r
from emp e;
--允许出现并列排名，但不允许跳过，两个第一名后继续接第二名
扩展//分析函数使用范围：rows 当前窗口下 range 一定范围内
练习题：
1.按照deptno分组，显示所有员工信息和每组的工资总和。
select e.*, sum(sal) over(partition by deptno) from emp e;
2.按照deptno分组，显示所有员工信息和每组员工的个数。
select e.*, count(1) over(partition by deptno) from emp e;
3.求emp表中sal排名第一和倒数第一的的员工信息。（不知道人数的情况下）
方法1：
select *
from (select e.*, dense_rank() over(order by sal) r from emp e)
where r = 1
union all
select *
from (select e.*, dense_rank() over(order by sal desc) r from emp e)
where r = 1;
方法2：（表连接）
select *
from (select e.*, row_number() over(order by sal) r from emp e),
(select e.*, row_number() over(order by sal desc) r from emp e)
where rownum = 1;
4.对各部门进行分组，按各组的工资进行降序排序。
select e.*, dense_rank() over(partition by deptno order by sal desc) r from emp e
5.求emp表中工资降序排名第三名和第五名的员工信息。
select *
from (select e.*, dense_rank() over(order by sal desc) r from emp e)
where r in (3, 5);
--注意并列排名的情况，使用dense_rank()
--PM 本周知识回顾
