首页 mysql基础教程[basic] 详情

---

开窗函数是非常重要的一类函数，可以大幅度方便我们的计算，掌握开窗函数也是我们sql能力得以进阶的一个重要标志。mysql8.0以下版本不支持开窗函数。

窗口函数概念

函数名                函数用途 row_number     对行进行排序并为每一行增加一个唯一编号。这是一个非确定性函数 rank                     将数据行值按照排序后的顺序进行排名，在有并列的情况下排名值将被跳过 dense_rank     将数据行值按照排序后的顺序进行排名，在有并列的情况下也不跳过排名值 lag                     访问一个分区或结果集中之前的一行 lead                     访问一个分区或结果集中之后的一行 first_value     访问一个分区或结果集中第一行 last_value     访问一个分区或结果集中最后一行 nth_value     访问一个分区或结果集中的任意一行 percent_rank     将计算得到的排名值标准化 cume_dist()     将计算得到的排名值标准化 ntile                 将数据进行将数据行分组为单元

组成部分

分区子句、排序子句、开窗子句

function1 (argument1,argument2,..argumentN)
over w
window w as ([partition-by-clause] [order-by-clause] [windowing-clause])

窗口子句

• 默认窗口子句：rows between unbounded preceding and current row
• 如果你没有显示声明窗口，使用默认窗口
• 并不是所有的分析函数都支持开窗子句

窗口子句语法

[rows | range] between  and [end expr]

whereas
is [unbounded preceding | current row | n preceding | n following]
is [unbounded following | current row | n preceding | n following]

排序函数

分类：row_number、rank、dense_rank 功能：根据分组给出排序的序号 三者区别：

• row_number()    不重复排名，直接排名，1-2-3-4-5-6
• rank()                 重复排名,会跳过，1-2-3-4-4-6
• dense_rank()      重复排名,不跳过，1-2-3-4-4-5

实例：现在需要对分不同部门来看部门内的工资排名，且从大到小排列：

SELECT 
    a.empno,a.ename,a.deptno,a.sal,
    row_number() over(PARTITION BY a.deptno ORDER BY a.sal DESC) AS 'num',
    rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) AS 'rank',
    dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC)  AS 'dense_rank'
FROM emp a

前后

分类：lag前、lead后 功能：根据分组，取值上n条和下n条   如果是第一条或最后一条，就给个默认值

实例1

SELECT 
    a.empno,a.deptno,a.hiredate,a.sal,
    lag(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) AS 'pre_sal',
    lead(sal, 1, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) AS 'next_sal',
    lag(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC)  AS 'pre2_sal',
    lead(sal, 2, 0) over(PARTITION BY a.deptno ORDER BY hiredate ASC) AS 'next_2sal'
FROM emp a

实例2

将重复的分组依据设置为null

SELECT 
  (CASE
      WHEN deptno = lag (deptno, 1) over w 
      THEN NULL 
      ELSE deptno 
    END
  ) AS 'deptno',
  ename,
  lag (ename, 1, 'AAA') over(PARTITION BY deptno ORDER BY ename) AS 'lower_name',
  lead (ename, 1, 'ZZZ') over(PARTITION BY deptno ORDER BY ename) AS 'higher_name' 
FROM emp