MySQL_窗口函数

目录

一、窗口函数的作用：

二、语法结构

三、常见的窗口函数（按功能分）  

1.排序函数

  ① ROW_NUMBER()

  ② RANK()

  ③ DENSE_RANK()

2.前后函数

  ① LAG(expr,n)函数

  ② LEAD(expr,n)函数 

3.首尾函数

  ① FIRST_VALUE(expr)函数

  ②LAST_VALUE(expr)函数

4.分布函数

  ① PERCENT_RANK()函数

  ② CUME_DIST()函数 

 5.其他函数

  ①NTH_VALUE(expr,n)函数

  ②NTILE(n)函数

  ③ 聚合函数

四、窗口函数中的关键字:

---

一、窗口函数的作用：

        用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。可分为静态窗口函数和动态窗口函数。

        静态窗口函数：窗口大小和位置不变，通常基于固定的行数或时间区间，适合用于固定区间的数据计算。通常用于滚动计算（如滚动平均、滚动总和等），窗口的范围是固定的。

        动态窗口函数：通常使用PARTITION BY子句来定义窗口边界，用于分组统计，窗口的范围基于数据的某些属性（如时间、类别、客户等）动态变化。

注意：

        ①PARTITION BY的使用与否不是动态和静态窗口函数的唯一区别。

        ②窗口函数是动态还是静态，说的是使用函数时，创建的窗口是动态的还是静态的，与函数本身无关。

---

二、语法结构

#窗口函数的语法结构是：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

#或者
函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）


/*
OVER 关键字指定函数窗口的范围。
    -如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
    -如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。

窗口名：为窗口设置一个别名，用来标识窗口。

PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分
别执行。

ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据
记录的顺序进行编号。

FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
*/

三、常见的窗口函数（按功能分）
        

---

1.排序函数

   ① ROW_NUMBER()

         根据某个排序规则，为每一行分配唯一的行号

#举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) 
AS row_num,id,category_id, category, NAME, price, stock 
FROM goods;

   ② RANK() 

        使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。

#举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock 
FROM goods;

   ③ DENSE_RANK()

        DENSE_RANK()函数对序号进行并列排序，不会跳过重复的序号，比如序号为1、1、2。

#举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, 
id, category_id, category, NAME, price, stock
FROM goods;

---

2.前后函数

    ① LAG(expr,n)函数

        返回当前行的前n行的expr的值。

#举例：查询goods数据表中前一个商品价格与当前商品价格的差值。
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM ( SELECT  id, category, NAME, price,LAG(price,1) OVER w AS pre_price
       FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;

    ② LEAD(expr,n)函数 

        返回当前行的后n行的expr的值。用法同上。

---

3.首尾函数

    ① FIRST_VALUE(expr)函数

       返回第一个expr的值

#举例：按照价格排序，查询第1个商品的价格信息。
SELECT id, category, NAME, price, stock,FIRST_VALUE(price) OVER w AS first_price 
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

  ②LAST_VALUE(expr)函数

        返回最后一个expr的值，用法同上。

---

4.分布函数

  ① PERCENT_RANK()函数

        返回当前行相对于窗口内其他行的百分比排名，按照 (rank - 1) / (rows - 1)计算。其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。

#举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
#写法一：
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r,
PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;
#写法二：
SELECT RANK() OVER w AS r, PERCENT_RANK() OVER w AS pr, id, category_id, category, NAME, price, stock 
FROM goods 
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price 
DESC);

  ② CUME_DIST()函数 

        CUME_DIST()函数主要用于查询小于或等于某个值的比例

举例：查询goods数据表中小于或等于当前价格的比例。
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC) AS cd, 
id, category, NAME, price 
FROM goods;

---

 5.其他函数

    ①NTH_VALUE(expr,n)函数

          NTH_VALUE(expr,n)函数返回第n个expr的值。

#举例：查询goods数据表中排名第2和第3的价格信息。
SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
NTH_VALUE(price,3) OVER w AS third_price 
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

    ②NTILE(n)函数

           NTILE(n)函数将分区中的有序数据分为n个桶（组），记录桶编号。

#举例：将goods表中的商品按照价格分为3组。
SELECT NTILE(3) OVER w AS nt,id, category, NAME, price 
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

---

   ③ 聚合函数

        以下聚合函数也可以在窗口函数中使用，只不过聚合计算的值会出现在每个分组的每条数据中。

• SUM(): 按组进行累计求和。
• AVG(): 按组进行累计平均值。
• MIN(): 按组查找最小值。
• MAX(): 按组查找最大值。
• COUNT(): 按组计算行数。

四、窗口函数中的关键字:

• ROWS: 指定窗口是基于行数来定义的。使用ROWS关键字可以精确控制窗口的大小（例如，前几行到当前行）。
• RANGE: 指定窗口是基于值范围来定义的。使用RANGE关键字时，窗口是基于数据的排序列的值范围，而不是物理行数（例如，当前行之前的所有行）。
• BETWEEN ... AND ... : 用于定义窗口的起始点和终止点。结合ROWS或RANGE关键字使用。
• UNBOUNDED PRECEDING : 表示窗口从分区的第一行开始，不受任何限制
• UNBOUNDED FOLLOWING : 表示窗口延伸到分区的最后一行，也不受任何限制。
• CURRENT ROW : 指定窗口的结束或开始点为当前行。