Mysql从8.0版本开始才支持窗口函数

1、什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列明> order by <用于排序的列名>)

那么语法中的<窗口函数都有哪些呢>？
<窗口函数>的位置，可以放一下两种函数：

1）专用窗口函数，包括后面要讲的rank, dense_rank,row_number等专用窗口函数。

  • 这三个函数主要是处理并列的情况， 在rank()函数，如果有并列的情况会占用下一个名次的位置，比如，成绩为100的学生有三个并列第一，那么99分的学生是第二名，通过rank()函数，名词是： 1,1,1,4；
  • 在dense() 函数中，如果有并列的情况，不会占用下一个名词，同用上个例子，名次是：1,1,1,2；
  • 在row_number() 函数汇总，会忽略并列的情况，同用上述列子，名次是：1,2,3,4

2） 聚合函数，如sum , ave, count, max ,min等

因为窗口函数式对where或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

  • 窗口函数这里的“窗口”表示范围，可以理解为将原数据划分范围，即分组，然后用函数实现某些目的。
  • 窗口函数有分组和排序的功能
  • 不减少原表的行数


2、窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题： 每个部门按业绩来排名 topN问题： 找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了

3、窗口函数的使用？

image.png

用RANK() 或 ROW_NUMBER 都行，sql如下：

  1. SELECT
  2.     MIN(log_id) START_ID,
  3.     MAX(log_id) END_ID
  4. FROM
  5.     (
  6.     SELECT DISTINCT 
  7.         log_id, 
  8.         log_id - ROW_NUMBER() OVER ( ORDER BY log_id ASC ) reference
  9.     FROM 
  10.         Logs
  11.     ) T
  12. GROUP BY
  13.     reference
  14. ORDER BY
  15.     START_ID

如果一下子看不懂，
请先看第一步：

  1. ["log_id", "rn", "reference"], 
  2. [1, 1, 0], 
  3. [2, 2, 0], 
  4. [3, 3, 0], 
  5. [7, 4, 3], 
  6. [8, 5, 3], 
  7. [10, 6, 4]

可见reference相同的log_id都是连续的，加上rn字段是为了便于理解，实际不需要。
所以下一步按reference求最大和最小log_id就好了。

当然这道题也可以使用笛卡尔积的形式