MySQL函数

目录

1.分布函数 CUME_DIST 和 percent_rank

2.前后函数 -LAG和LEAD

3.头尾函数 -FIRST_VALUE和LAST_VALUE

4.其他函数 -NTH_VALUE(expr,n),NTILE(n)

-NTH_VALUE(expr,n)

  NTILE(n)

---

1.分布函数 CUME_DIST 和 percent_rank

 用途: 分组内小于，等于当前rank值的行数 / 分组内总行数  求比例
 应用场景: 查询小于等于当前薪资(salary)的比例
 

 use mydb4;
 
 select 
  dname,
  ename,
	salary,
	cume_dist() over(order  by salary) as rn1,
	cume_dist() over(partition by dname order  by salary) as rn2
  from employee;
 
 /*
 0.25  3/12=0.25
 0.416 5/12=0.416
 */

select 
  dname,
  ename,
	salary,
	rank() over(partition by dname order  by salary) as rn1,
  PERCENT_RANK() over(partition by dname order  by salary) as rn2
  from employee;

2.前后函数 -LAG和LEAD

用途: 返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
应用场景:查询前一名同学的成绩和当前同学成绩的差值

select 
  dname,
	ename,
	salary,
	hiredate,
	lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) AS time1,
  lag(hiredate,2) over(partition by dname order by hiredate) as time1
 from employee;
 
 
 select 
  dname,
	ename,
	salary,
	hiredate,
	lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) AS time1,
  lead(hiredate,2) over(partition by dname order by hiredate) as time1
 from employee;
 

3.头尾函数 -FIRST_VALUE和LAST_VALUE

用途: 返回第一个FIRST_VALUE(expr) 或最后一个LAST_VALUE(expr)的值
应用场景: 截止到目前，按照日期排序查询第1个入职和最后1个入职员工的薪资

select 
  dname,
	ename,
	salary,
	hiredate,
	FIRST_VALUE(salary) over(partition by dname order by hiredate) ,
  LAST_VALUE(salary) over(partition by dname order by hiredate) 
 from employee;

4.其他函数 -NTH_VALUE(expr,n),NTILE(n)

-NTH_VALUE(expr,n)

 用途: 返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
 应用场景:截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

select 
  dname,
	ename,
	salary,
	hiredate,
	nth_VALUE(salary,2) over(partition by dname order by hiredate) ,
  nth_VALUE(salary,3) over(partition by dname order by hiredate) 
 from employee;
 

 
 NTILE(n)

 用途:讲分区中的有序数据分为n个等级，记录等级数
 应用场景:将每个部门员工按照入职日期分成3组

select 
  dname,
	ename,
	salary,
	hiredate,
	NTILE(4) over(partition by dname order by hiredate) 

-- 取出每一个部门的第一组员工
 SELECT
 *
 from(
 select 
  dname,
	ename,
	salary,
	hiredate,
	NTILE(3) over(partition by dname order by hiredate ) as rn
	from employee
	) t 
	where t.rn=1;