Mysql trick -- windows function. Mysql小技巧:窗口函数

,

窗口函数是我最近在做数据分析时学到的小技巧, 下面就写一篇blog记录一下。。。

基本语法

比起熟悉的 group by 操作, 窗口函数的好处就是不会不减少原表的行数, 以及使代码变得更加优雅(bushi)。

首先来看看 mysql 窗口函数的语法格式: 

1
2
函数名（[expr]） over 子句
over 是关键字，用来指定函数执行的窗口范围

而子句则有三类:

  • partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。
  • order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。
  • frame子句：定义子集的规则，通常用来作为滑动窗口使用。

比如要根据班级进行分组并按年纪进行排序, 使用rank函数计算排名: 

1
select *,rank() over (partition by classname order by grade desc) as ranking from class;

比如说要操作 Logs 表连续出现次数至少3次的数字： 

1
2
3
4
5
6
7
8
9
with
    p as (
    select id, num, 
    lead(num, 1) over (order by id) as num1, -- 选求num列当前行后面第1个数字
    lead(num, 2) over (order by id) as num2  -- 选求num列当前行后面第2个数字
    from Logs)

    select distinct p.num as ConsecutiveNums
        from p where p.num=p.num1 and p.num1=p.num2;

思路是使用 with 语句创建一个运用完窗口函数的临时表 p: 

1
2
3
4
5
6
7
8
9
| id | num | num1 | num2 |
| -- | --- | ---- | ---- |
| 1  | 1   | 1    | 1    |
| 2  | 1   | 1    | 2    |
| 3  | 1   | 2    | 1    |
| 4  | 2   | 1    | 2    |
| 5  | 1   | 2    | 2    |
| 6  | 2   | 2    | null |
| 7  | 2   | null | null |

当然 mysql 支持的窗口函数还有很多, 需要用到的时候就直接查 api 啦。。。