MySQL8.0数据库开窗函数

简介

      数据库开窗函数是一种在SQL中使用的函数，它可以用来对结果集中的数据进行分组和排序，以便更好地分析和处理数据。开窗函数与聚合函数不同，它不会将多行数据聚合成一行，而是保留每一行数据，并对其进行分组和排序。

常见的开窗函数包括ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG()、LEAD()等。这些函数可以帮助用户在结果集中生成分组和排序的结果，以便更好地理解和分析数据。

例如，使用ROW_NUMBER()函数可以根据一个或多个字段对结果集进行分组，并在每个分组内生成一个行号，以便用户可以轻松地跟踪数据。使用LAG()和LEAD()函数可以在结果集中的每一行之前和之后提取数据，以便用户可以查看当前行之前或之后的数据。

开窗函数是SQL中非常有用的工具，可以帮助用户对结果集中的数据进行分组和排序，以便更好地分析和处理数据。

MySQL 官方文档: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

注意: 官方解释 开窗函数只有MySQL8.0版本之后才有哦。

一、开窗函数 与 聚合函数 有什么区别?

• 数据处理范围：聚合函数只能对整个数据表或者数据集进行操作，计算结果为单一值。而开窗函数则可以对每个行进行操作，计算结果会在每个行上显示。
• 计算结果：聚合函数的计算结果只有一个，通常用于执行诸如求和、取平均值、计算最大值/最小值等的操作。而开窗函数的计算结果可以有多个，它提供给查询结果集中每一行的附加列。
• 语法：聚合函数通常用于SELECT语句中的SELECT子句和HAVING子句，而开窗函数通常在OVER关键字后使用。

  二、官方解释的开窗函数

  

  •  翻译

    

    官方说的很官方，稍有点难以理解还是。

    三、开窗函数细分

    3.1、序号

    • ROW_NUMBER()：该函数可以根据一个或多个字段对结果集进行分组，并在每个分组内生成一个行号，以便用户可以轻松地跟踪数据。
    • RANK()：该函数可以根据一个或多个字段对结果集进行排序，并在每个排序中生成一个排名，以便用户可以了解数据的大小和顺序。
    • DENSE_RANK()：该函数可以根据一个或多个字段对结果集进行排序，并在每个排序中生成一个排名，但跳过的位次比RANK()函数少一位。

      3.2、分布

      • PERCENT RANK()：函数用于计算数据集中每个值的百分比排名。
      • CUME_DIST()：函数用于计算数据集中每个值的累积密度排名。

        3.3、前后

        • LAG()：该函数可以在结果集中的每一行之前提取数据，以便用户可以查看当前行之前的数据。
        • LEAD()：该函数可以在结果集中的每一行之后提取数据，以便用户可以查看当前行之后的数据。

          3.4、首尾

          • FIRST_VALUE()：函数返回结果集的有序分区中的第一个值。
          • LAST_VALUE()：函数返回结果集的有序分区中的最后一个值。

            3.5、其它

            • NTILE()：该函数可以根据一个或多个字段对结果集进行分组，并将每个分组分配到指定数量的桶中，以便用户可以更好地分析和分组数据。
            • NTH_VALUE()：函数返回结果集的有序分区中第n行的值。

              四、语法使用

              4.1、语法结构

               OVER ([PARTITION BY ] [ORDER BY  {ASC|DESC}] [|] [||])

              • 表示要执行的聚合函数，如SUM、AVG、MAX、MIN、COUNT等；
              • 表示要进行分组的列；
              • 表示按照哪个列进行排序，可以指定多个排序列，用逗号分隔；
              • 和分别表示行级窗口和范围级窗口；
              • 、和表示窗口的起始位置、结束位置和长度。

                 在 MySQL 8.0 中，行窗口是指一组连续的行，这些行被视为一个整体，并且可以用于窗口函数的计算。

                行窗口由以下关键字指定：

                • ROWS：表示行窗口。
                • BETWEEN：用于指定行窗口的起始位置和结束位置。
                • PRECEDING：表示行窗口的起始位置。
                • FOLLOWING：表示行窗口的结束位置。

                  常用的行窗口指定方式：

                  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：表示从结果集的第一个行到当前行，包括当前行。
                  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：表示从当前行到结果集的最后一个行，包括当前行。
                  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：表示包含当前行在内的前后各一行。

                    说明: 行窗口可以用于计算每组的总和、平均值、计数等聚合操作，也可以用于计算每个行的排名、累积和等操作。

                    4.2、普通聚合函数做开窗函数

                    • 普通聚合函数只能对整个数据表或者数据集进行操作，计算结果为单一值。而开窗函数可以针对每个行进行操作，计算结果会在每个行上显示。

                      4.2.1、表结构

                      DROP TABLE IF EXISTS `order_for_goods`;
                      CREATE TABLE `order_for_goods`  (
                        `order_id` int(0) NOT NULL AUTO_INCREMENT,
                        `user_id` int(0) NULL DEFAULT NULL,
                        `money` decimal(10, 2) NULL DEFAULT NULL,
                        `quantity` int(0) NULL DEFAULT NULL,
                        `join_time` datetime(0) NULL DEFAULT NULL,
                        PRIMARY KEY (`order_id`) USING BTREE
                      ) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

                      4.2.2、表数据

                      INSERT INTO order_for_goods (user_id, money, quantity, join_time )
                      VALUES
                      	( 1001, 1800.90, 1, '2023-06-07'),
                      	( 1001, 3600.89, 5, '2023-05-02'),
                      	( 1001, 1000.10, 6, '2023-01-08'),
                      	( 1002, 1100.90, 9, '2023-04-07'),
                      	( 1002, 4500.99, 1, '2023-03-14'),
                      	( 1003, 2500.10, 3, '2023-02-14'),
                      	( 1002, 2500.90, 1, '2023-03-14'),
                      	( 1003, 2500.90, 1, '2022-12-12'),
                      	( 1003, 2500.90, 2, '2022-09-08'),
                          ( 1003, 6000.90, 8, '2023-01-10');

                      4.2.3、普通函数做开窗函数

                      1、语句如下

                      select 
                      	*,
                      	sum(money) over(partition by user_id order by order_id) as alias_sum,
                      	avg(money) over(partition by user_id order by order_id) as alias_avg,
                      	max(money) over(partition by user_id order by order_id) as alias_max,
                      	min(money) over(partition by user_id order by order_id) as alias_min,
                      	count(money) over(partition by user_id order by order_id) as alias_count
                      from order_for_goods;

                      • 从 order_for_goods 表中选择了所有的列，并计算了每个用户在每个订单中的总金额、平均金额、最大金额、最小金额和计数。
                      • 这个查询使用了 sum()、avg()、max()、min() 和 count() 函数来计算每个订单的总金额、平均金额、最大金额、最小金额和计数。这些函数后面跟着 over() 子句，用于指定计算的窗口。在这个例子中，窗口是按照 user_id 分区，按照 order_id 排序的。

                        2、查询结果返回了选择的列和计算出的别名列如下

                        

                        4.3、序号函数

                        4.3.1、ROW_NUMBER()函数

                        1、执行语句

                        select *
                        from (
                        	select *,row_number() over(partition by user_id order by money desc) as alias_row_number
                        	from order_for_goods) t
                        where alias_row_number