oracle——SQL复习07

select ltrim('  hello  ') from dual;
select ltrim('abcHello   ','abc') from dual;
select rtrim('   Hello   ') from dual;
select rtrim('abcHello  abc','abc') from dual;


select trim('   Hello   ') from dual;

select replace('Holle','e','o') from dual ;

select sysdate from dual;

select stddev(4) from dual;  

select * from emp e ;
 
select e.sal,e.deptno , row_number() over(partition by e.deptno order by e.sal desc)  rn from emp e;  

select stddev(e.sal) over(partition by e.deptno )  rn from emp e;   

select  * from emp e order by  e.deptno,e.sal desc ;


select variance(e.sal) from emp e ;

select stddev(e.sal) from emp e ;

select sum(e.sal)/count(1),sum(e.sal),count(1)   from emp e;




select e.sal, rank() over(partition by e.deptno order by e.sal desc) from emp e;

select e.sal, dense_rank() over(partition by e.deptno order by e.sal desc) from emp e;

select e.sal, row_number() over(partition by e.deptno order by e.sal desc) from emp e;

select e.sal, sum(e.sal) over(partition by e.deptno ) from emp e;
select e.sal, avg(e.sal) over(partition by e.deptno ) from emp e;
select e.sal, stddev(e.sal) over(partition by e.deptno ) from emp e;  



            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后
            
----显示各部门员工的工资，并附带显示该部分的最高工资。
select e.sal,
       last_value(e.sal) over(partition by e.deptno order by e.sal rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  from emp e;


--按照deptno分组，然后计算每组值的累加值
SELECT  
       SAL,
       deptno,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;
 
 
 
select e.sal,e.deptno, sum(e.sal) over(partition by e.deptno order by e.sal ) from emp e;





/**

1.  自动汇总函数rollup,cube,
2.  rank 函数, rank,dense_rank,row_number
3.   lag,lead函数
4.    sum,avg,的移动增加,移动平均数
5.    ratio_to_report报表处理函数 ,占百分比
6.    first_value,last_value取基数的分析函数
*/


select e.ename,e.deptno,e.sal,sum(e.sal) over(partition by e.deptno order by e.ename  ) from emp e;
select e.ename,e.deptno,e.sal,sum(e.sal) over(partition by e.deptno order by e.ename desc ) from emp e;  
select e.ename,e.deptno,e.sal,sum(e.sal) over(partition by e.deptno order by e.ename rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) from emp e;

-- lag()  
select e.ename,
       e.deptno,
       e.sal,
       lag(e.sal) over( order by e.sal desc) front_sal
  from emp e  
 
--查找 比 CLARK 工资高的前一名的人名 :BLAKE
select *
  from emp e
 where e.sal = (
                select front_sal
                  from (
                         select e.ename,
                                 e.deptno,
                                 e.sal,
                                 lag(e.sal) over(order by e.sal desc) front_sal
                           from emp e) t
                 where t.ename = 'CLARK')
 
--lead()
select e.ename,
       e.deptno,
       e.sal,
       lead(e.sal) over( order by e.sal desc)
  from emp e

--查找 比 CLARK 工资低的下一位人员信息 :ALLEN
select *
  from emp e
 where e.sal = (
                select front_sal
                  from (
                         select e.ename,
                                 e.deptno,
                                 e.sal,
                                 lead(e.sal) over(order by e.sal desc) front_sal
                           from emp e) t
                 where t.ename = 'CLARK')
 
 
 
-- ratio_to_report
select e.ename,
       e.deptno,
       e.sal,
       round(ratio_to_report(e.sal) over( partition by e.deptno),2)  precent_sal
  from emp e
 
 
   last
 
 
 --first_value
 
select e.ename,
       e.deptno,
       e.sal,
        first_value(e.sal) over( partition by e.deptno order by e.sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )  
  from emp e ;
 
 
 
 --last_value
 --range  between unbounded preceding and current row是默认的窗口范围
select e.ename,
       e.deptno,
       e.sal,
        last_value(e.sal) over( partition by e.deptno order by e.sal desc  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  
  from emp e