MYSQL之窗口函数

1.窗口函数

        MySQL 8.0 引入了窗口函数（也称为分析函数），这是一项对 MySQL 功能的重大扩展，提供了在 SQL 查询中进行更复杂的统计和数据分析功能。窗口函数允许用户对一组行（称为窗口）执行计算，这些行与查询结果中的当前行有某种关系，而不用对整个查询结果集分组或排序。使用窗口函数可以执行各种操作，如求和、计数、平均值计算，以及在数据集内进行行之间的比较等。窗口函数的基本语法：

                 <窗口函数> OVER ( [PARTITION BY <列名>] [ORDER BY <排序规则>] )

• PARTITION BY：这部分用于将数据分为特定的分区，窗口函数会在每个分区内独立计算。
• ORDER BY：用于在每个分区内对数据进行排序，窗口函数的计算可以依赖于这个顺序。
• 窗口帧：是指在 ORDER BY 中定义的当前行的上下文窗口。窗口帧可以通过 ROWS 或 RANGE 关键字进一步定义。

2.聚合窗口函数

       总数 SUM()、平均数AVG()、最小数MIN()、最大数MAX()、统计次数COUNT() 等。

sql实例：

SELECT 
  player_name, 
  game_date,
  points,
  AVG(points) OVER (PARTITION BY player_name ORDER BY game_date) AS avg_points
FROM 
  game_stats;

上述查询会为每个玩家计算其每场比赛至今的平均得分。

3.排名窗口函数

        ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE(n) 

sql实例：

SELECT 
  employee_id,
  sale_date,
  amount,
  RANK() OVER (PARTITION BY employee_id ORDER BY amount DESC) AS rank
FROM 
  sales;

上述查询会为每个玩家计算其每场比赛至今的平均得分。其中row_number、rank、dense_rank的区别如下，对于年龄集合{22，23，22}，使用它们的排序结果依次为{1，2，3}、{1，1，3}、{1，1，2}。

NTILE(n) 使用与上三者有所区别，实例如下：

SELECT 
  employee_id,
  sale_date,
  amount,
  NTILE(4) OVER (PARTITION BY employee_id ORDER BY amount DESC) AS quartile
FROM 
  sales;

NTILE(n) 函数用于将分区内的行分成指定数量的几乎等大的组，n 是你指定的组的数量。这会将每个员工的销售记录按销售额分成四个组，每个组包含相同数量的行（或尽可能平均）。每个这些函数都在数据分析和报告中非常有用，特别是当你需要按一定方式排序、分组或排名数据时。它们可以帮助你更好地理解数据，做出更有根据的业务决策。

4.分析窗口函数

  LEAD(), LAG(), FIRST_VALUE(), 和 LAST_VALUE() 是一组窗口函数，用于执行基于行与行之间的比较和关系操作。这些函数可以访问由 ORDER BY 在窗口函数中定义的数据行序列中的其他行，而不​​​​​​​需要进行自连接或复杂的子查询。以下是使用示例。

LEAD() 函数用于访问当前行之后的行（即“领先”的行）。它可以查看当前行后的第 N 行的数据。

示例查询： 假设有一个销售数据表 sales，包括日期和销售额：

SELECT 
  sale_date,
  sales_amount,
  LEAD(sales_amount, 1) OVER (ORDER BY sale_date) AS next_day_sales
FROM 
  sales;
这个查询显示每一天的销售额和下一天的销售额。同样的LAG查询显示每一天的销售额和上一天的销售额

FIRST_VALUE(), 和 LAST_VALUE() 使用有所不同

FIRST_VALUE() 函数用于在窗口帧中访问第一行的数据，使用销售数据表 sales 来展示每月的销售变化，以每月第一天的销售额为基准：

SELECT 
  sale_date,
  sales_amount,
  FIRST_VALUE(sales_amount) OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_date) AS first_day_sales_of_month
FROM 
  sales;
这个查询在每个月份中显示每天的销售额与该月第一天的销售额。

LAST_VALUE() 函数用于在窗口帧中访问最后一行的数据。在使用 LAST_VALUE() 时，必须正确设置窗口帧（使用 ROWS BETWEEN），否则可能得到意料之外的结果，因为窗口默认是从当前行到分区结束。

SELECT 
  sale_date,
  sales_amount,
  LAST_VALUE(sales_amount) OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_day_sales_of_month
FROM 
  sales;

这个查询展示了每月中每天的销售额与该月最后一天的销售额相比。