MySQL使用窗口函数ROW_NUMBER()、DENSE_RANK()查询每组第一名或每组前几名，窗口函数使用详解

MySQL数据表结构

创建 tbl_class_info 表，表中有四个字段 id、username、score、group_name

使用 ROW_NUMBER()、DENSE_RANK() 查询每组前三名

-- 查询每组前3名
SELECT username, score, group_name  
FROM (  
  SELECT username, score, group_name,  
         ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_rank,
         DENSE_RANK() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_dense_rank  
  FROM tbl_class_info 
) AS ranked_scores  
WHERE test_rank <= 3 OR test_dense_rank <= 3

查询结果：

使用 ROW_NUMBER()、DENSE_RANK() 查询第二名

-- 查询每组第2名
SELECT username, score, group_name  
FROM (  
  SELECT username, score, group_name,  
         ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_rank 
  FROM tbl_class_info 
) AS ranked_scores  
WHERE test_rank = 2

查询结果：

使用 ROW_NUMBER()、DENSE_RANK() 查询结果是不同的，

ROW_NUMBER()和DENSE_RANK()是常见的窗口函数，它们可以用于对结果集中的行进行排序和编号，它们的主要区别在于 使用DENSE_RANK()函数为每个组（group_name）中的记录根据成绩（score）降序排列，并为每组中的行分配一个唯一的序号（rank）。

与ROW_NUMBER()不同，如果存在相同的成绩，DENSE_RANK()会为它们分配连续的序号，而不会跳过任何数字。下面是实例说明：

ROW_NUMBER()、DENSE_RANK() 区别详解

使用ROW_NUMBER()查询数据：

SELECT
	username,
	score,
	group_name,
	ROW_NUMBER() OVER (PARTITION BY group_name
ORDER BY
	score DESC) AS rank_number
FROM
	tbl_class_info;

执行结果可以看到，即使成绩（score）相同，rank_number 序号也是不同的

使用 DENSE_RANK() 查询数据：

SELECT
	username,
	score,
	group_name,
	DENSE_RANK() OVER (PARTITION BY group_name
ORDER BY
	score DESC) AS rank_number
FROM
	tbl_class_info;

执行结果可以看到，即使成绩（score）相同，rank_number 序号也是相同的

所以总的来说，ROW_NUMBER() 和 DENSE_RANK() 都是非常有用的窗口函数，它们可以用于各种数据分析任务。但是，它们之间的区别意味着它们适用于不同的场景。如果您需要为每个行分配唯一的数字，即使在有重复值的情况下也是如此，那么 ROW_NUMBER() 是更好的选择。如果您需要为每个行分配唯一的数字，但如果有重复值，则需要跳过重复值，那么 DENSE_RANK() 是更好的选择。

MySQL窗口函数的使用语法

SELECT <窗口函数> OVER (
  [PARTITION BY <表达式>]
  [ORDER BY <表达式>]
  [ROWS BETWEEN <表达式> AND <表达式>]
)
FROM <表名>

 其中： 

• <窗口函数>  是窗口函数的名称。 
• PARTITION BY <表达式>  是分区表达式。分区表达式用于将表中的行分成多个分区。 
• ORDER BY <表达式>  是排序表达式。排序表达式用于对分区中的行进行排序。 
• ROWS BETWEEN <表达式> AND <表达式>  是行范围表达式。行范围表达式用于指定窗口函数计算的行的范围。

SELECT
	group_name,
	AVG(score) OVER (PARTITION BY group_name
ORDER BY
	score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_sales
FROM
	tbl_class_info;

执行结果：

注：在窗口函数中，ROWS BETWEEN <表达式> AND <表达式> 用于指定窗口的边界范围。这个范围是根据指定的表达式来确定的。通常，第一个表达式指定了窗口的起始行，第二个表达式指定了窗口的结束行。

在计算窗口函数之前，数据库会先确定窗口的范围。然后，根据指定的窗口范围，对范围内的行进行运算。通常，窗口函数会对窗口内的每一行执行计算，并返回一个与窗口范围相对应的结果。
 

MySQL中常见的窗口函数有哪些

  SUM()函数：计算指定列的总和。

SELECT column1, SUM(column2) OVER (PARTITION BY column1) AS total_sum  
FROM table;

  AVG()函数：计算指定列的平均值。

SELECT column1, AVG(column2) OVER (PARTITION BY column1) AS average_value  
FROM table;

  MIN()函数：返回指定列的最小值。

SELECT column1, MIN(column2) OVER (PARTITION BY column1) AS minimum_value  
FROM table;

  MAX()函数：返回指定列的最大值。

SELECT column1, MAX(column2) OVER (PARTITION BY column1) AS maximum_value  
FROM table;

  COUNT()函数：计算指定列的非空值的数量。

SELECT column1, COUNT(column2) OVER (PARTITION BY column1) AS count_value  
FROM table;

  RANK()函数：返回一组行的排名。

SELECT column1, RANK() OVER (ORDER BY column2 DESC) AS rank_value  
FROM table;

  DENSE_RANK()函数：返回一组行的不间断排名。

SELECT column1, DENSE_RANK() OVER (ORDER BY column2 DESC) AS dense_rank_value  
FROM table;

  ROW_NUMBER()函数：为一组行分配一个唯一的数字。

SELECT column1, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS row_number_value  
FROM table;

MySQL窗口函数和聚合函数的区别

窗口函数和聚合函数都是在数据库中对数据进行计算的函数。但是，它们之间有一些关键区别。 

• 窗口函数返回一个单个值，该值是基于一组行计算的。这组行称为窗口。窗口函数可以使用窗口中的所有行，也可以使用窗口中的部分行。 
• 聚合函数返回一个单个值，该值是基于整个表中的数据计算的。聚合函数不能使用窗口，因为它们需要访问整个表的数据才能计算结果。 

SELECT SUM(sales) FROM orders;

SELECT SUM(sales) OVER (PARTITION BY product_id ORDER BY order_date);

第一个查询使用聚合函数 SUM 计算整个表中所有订单的总销售额。
第二个查询使用窗口函数 SUM 计算每个产品的总销售额。窗口函数 SUM 使用 ORDER BY 子句对订单按日期排序，然后计算每个产品的总销售额。 
窗口函数和聚合函数都是在数据库中对数据进行计算的强大工具。但是，它们之间有一些关键区别，因此在选择使用哪种函数时需要考虑这些区别。