MySQL窗口函数基础初试（一）（待补充）

MySQL支持窗口函数，对于数据row的查询，会使用一些相关的rows进行计算出一个row。如下将会讨论如何使用窗口函数，包括OVER以及WINDOW子句，本结只讨论非聚集窗口函数。聚集窗口函数参考：聚集窗口函数 。更多关于优化以及窗口函数参考：窗口函数优化 。MySQL窗口函数主要涉及一下内容：

12.21.1 Window Function Descriptions

12.21.2 Window Function Concepts and Syntax

12.21.3 Window Function Frame Specification

12.21.4 Named Windows

12.21.5 Window Function Restrictions

 

 

MySQL8版本开始支持窗口函数，丰富的一些复杂逻辑的开发，示例代码如下：

show create table open_id_to_pin;

CREATE TABLE `open_id_to_pin`
(
    `id`          int NOT NULL AUTO_INCREMENT,
    `open_id`     varchar(32) DEFAULT NULL,
    `pin`         varchar(32) DEFAULT NULL,
    `expire_time` datetime    DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


# 计算行号

SELECT @rownum := @rownum + 1 AS rownum,
       e.*
FROM (SELECT @rownum := 0) r,
     open_id_to_pin e;


#相关子查询
select *
from open_id_to_pin a
where a.expire_time in (select max(b.expire_time) from open_id_to_pin b where a.open_id = b.open_id)

# 窗口函数实现openid相同取最新的记录
select *
from (select *, row_number() over (partition by open_id order by expire_time desc ) as rn
      from open_id_to_pin
     ) tab
where tab.rn = 1;


# ---------------------------------
# 相同点：RANK()和DENSE_RANK()的是排名函数
# 不同点：RANK()是跳跃排序,即如果有两条记录重复,接下来是第三级别
# 如：1 2 2 4,会跳过3
# DENSE_RANK()是连续排序,即如果有两条记录重复，接下来是第二级别
# 如：1 2 2 3

# 按照open_id划分窗口以及按照 exipre_time 排序
select *, rank() over (partition by open_id order by expire_time desc) as rk
from open_id_to_pin;


select *, dense_rank() over (partition by open_id order by expire_time desc) as rk
from open_id_to_pin;


# 按照open_id划分窗口以及按照open_id排序
select *, rank() over (partition by open_id order by open_id desc) as rk
from open_id_to_pin;


select *, dense_rank() over (partition by open_id order by open_id desc) as rk
from open_id_to_pin;

官方参考：https://dev.mysql.com/doc/refman/8.0/en/window-functions.html