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 本周知识回顾