拿到北京银行offer，SQL进阶篇

聚合函数格式： 

聚合函数/指定函数(列) over(partition by 字段 order by 字段)

分析函数和聚合函数的区别是什么？ 

聚合函数，有多少分组，就返回几个值 

分析函数，有多少行数，就返回几个值

select deptno,sum(sal) from t_empgroup by deptnoorder by deptno

select deptno,sum(sal) over(partition by deptno)from t_emp Order by 1

1.求每个人工资占该员工所在部门总工资的比例

with tmp as 
(select deptno,sum(sal) deptno_sal from t_emp
group by deptno
order by deptno)
select e.deptno,e.empno,e.ename,e.sal,t.deptno_sal,round(e.sal/t.deptno_sal,
2) zb from t_emp e
inner join tmp t on e.deptno=t.deptno
order by 1,2

Select 
deptno,empno,ename,sal,
sum(sal) over(partition by deptno),
round(sal/sum(sal) over(partition by deptno),2) 
from t_emporder by 1,2

2.每个部门工资排序

select deptno,a_sal,count(1) 
from(select a.deptno,a.empno,a.sal a_sal,b.sal b_sal from t_emp a
inner join t_emp b  
on a.deptno=b.deptno and a.sal<=b.sal
order by a.deptno,a_sal) tmp
group by deptno,a_sal
order by 1,3 

select 
deptno,empno,ename,sal,
row_number() over(partition by deptno order by sal desc) rn 
from t_emp;

sum/count/..()    over(partiton by .. order by..)  根据不同函数作用不同
row_number()    over(partiton by .. order by..)  排序
dense_rank()     over(partiton by .. order by..)  排序（同值同排名，不跳号）
rank()           over(partiton by .. order by..)  排序（同值同排名）
first_value()      over(partiton by .. order by..)  取第一个值
last_value()      over(partiton by .. order by..)   取最后一个值
lead()           over(partiton by .. order by..)  上移
lag()            over(partiton by .. order by..)  下移

drop table t_emp_1;
create table t_emp_1
(
deptno int,
empno int,
ename string,
sal int,
comm int,
flag int
);
insert into t_emp_1 values (10,101,'张一',5000,null,0);
insert into t_emp_1 values (10,102,'张二',8000,null,0);
insert into t_emp_1 values (10,103,'张三',9000,1000,0);
insert into t_emp_1 values (20,201,'李一',11000,null,0);
insert into t_emp_1 values (20,202,'李二',15000,null,0);
insert into t_emp_1 values (20,203,'李三',15000,3000,0);
insert into t_emp_1 values (20,204,'李四',16000,null,1);
insert into t_emp_1 values (30,301,'赵一',50000,5000,0);

1.分析函数—聚合

select t.deptno ,t.empno,t.ename,sal, 
sum(sal)  over(partition by deptno)   v_sum, 
count(sal) over(partition by deptno) v_count, 
max(sal) over(partition by deptno) v_max, 
min(sal) over(partition by deptno) v_min, 
avg(sal) over(partition by deptno) v_avg
from t_emp_1 t
order by 1;

2.分析函数—分组TOP-N

select t.deptno ,t.empno,t.ename,sal, 
row_number() over(partition by deptno order by sal desc) rn_row , 
dense_rank() over(partition by deptno order by sal desc) rn_dense, 
rank() over(partition by deptno order by sal desc) rn_rank
from t_emp_1 t
order by 1;

3.其它分析函数

select t.deptno ,t.empno,t.ename,sal, 
first_value(sal) over(partition by deptno order by sal desc) v_fist, 
last_value(sal) over(partition by deptno order by sal desc) v_last, 
lead(sal,1) over(partition by deptno order by sal desc) lead, 
lag(sal,1) over(partition by deptno order by sal desc) lag
from t_emp_1 t
order by 1;

4.经典分析函数案例（包含所有）

select t.deptno ,t.empno,t.ename,sal, 
row_number() over(partition by deptno order by sal desc) rn_row , 
dense_rank() over(partition by deptno order by sal desc) rn_dense, 
rank() over(partition by deptno order by sal desc) rn_rank, 
sum(sal) over(partition by deptno) v_sum, count(sal) over(partition by deptno) v_count, 
first_value(sal) over(partition by deptno) v_fist, 
last_value(sal) over(partition by deptno) v_last, 
lead(sal,1) over(partition by deptno order by sal desc) lead, 
lag(sal,1) over(partition by deptno order by sal desc) lag
from t_emp_1 t
order by 1;

www.cnblogs.com/MOBIN/p/561…

1.字符类型

select 
lower('ABC'),
upper('abc'),
concat('a',',','b',',c'),
regexp_replace('abc','b','B'),' a b c ',
trim(' a b c '),ltrim(' a b c '),
rtrim(' a b c '),
substr('abcde',2),
substr('abcde',2,2),
length('abc'),
repeat('a',3)

2.数值类型

select 
abs(-1),
floor(1.8),
ceil(1.8),
round(2.5),
round(2.555,2)

3.时间类型

select 
current_date,
current_timestamp,
unix_timestamp(current_timestamp),
unix_timestamp(current_timestamp,'yyyy-MM-dd'),
from_unixtime(1550545559,'yyyy-MM-dd hh'),
from_unixtime(1550545559,'yyyy-MM-dd hh:mm:ss'),
year(current_timestamp),month(current_timestamp),
day(current_timestamp),
weekofyear(current_timestamp),
dayofmonth(current_timestamp),
hour(current_timestamp),
minute(current_timestamp),
second(current_timestamp),
--quarter(current_date) ,
date_add(current_date,1),
date_add(current_date,-1),
add_months(current_date,1),
add_months(current_date,-1),
last_Day(current_date),
next_Day(current_date ,'TU'),
date_add(current_date,-1),
datediff(current_date,'2019-03-08')

4.转类型函数

with 
tmp as  
(
select 8881 as num
union all
select 8882 as num
union all
select 8871 as num
)
select * from tmp
where cast(num as string) like  '888%