SQL GROUPBY 分组后取每组的 Top 200

SQL GROUPBY 分组后取每组的 Top 200

在SQL中，经常会遇到需要对数据进行分组并取每个组中的Top N条数据的情况。这种需求在实际开发中十分常见，例如在电商平台中需要取每个商品分类下销量前几名的商品，或者在社交平台中需要取每个用户发帖量前几名的用户等等。

在本文中，将通过实际案例演示如何使用SQL的GROUP BY语句结合子查询来实现分组后取每组的Top 200条数据。

案例说明

假设有一个用户表 tb_user，字段包括 user_id, username, post_count，现在需要取每个用户发帖量前200名的用户。用户表结构如下：

CREATE TABLE tb_user (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  post_count INT
);

INSERT INTO tb_user VALUES (1, 'Alice', 300);
INSERT INTO tb_user VALUES (2, 'Bob', 250);
INSERT INTO tb_user VALUES (3, 'Cathy', 350);

解决方案

步骤一：使用ROW_NUMBER()函数对每个用户进行排序

首先，我们需要使用ROW_NUMBER()函数对每个用户进行排序，以便后续取Top 200。ROW_NUMBER()函数是一种窗口函数，能够为结果集中的每一行分配一个唯一的序号。

SELECT 
  user_id,
  username,
  post_count,
  ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY post_count DESC) AS rn
FROM tb_user;

执行以上SQL语句可以得到每个用户的发帖量，并按照发帖量降序排列，并为每行添加一个序号rn：

步骤二：使用子查询取每个用户Top 200

接下来，我们将上一步得到的结果作为子查询，并在外部查询中使用 WHERE 子句过滤出每个用户的Top 200。

WITH temp AS (
  SELECT 
    user_id,
    username,
    post_count,
    ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY post_count DESC) AS rn
  FROM tb_user
)
SELECT 
  user_id,
  username,
  post_count
FROM temp
WHERE rn <= 200;

执行以上SQL语句可以得到每个用户发帖量前200名的用户：

优化：限制每个用户Top 200

在实际应用中，我们可能只需要每个用户的Top N，而不是全部Top 200。此时可以根据实际需求修改 WHERE rn <= 200 中的数字。

WITH temp AS (
  SELECT 
    user_id,
    username,
    post_count,
    ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY post_count DESC) AS rn
  FROM tb_user
)
SELECT 
  user_id,
  username,
  post_count
FROM temp
WHERE rn <= 1;  -- 取每个用户的Top 1

执行以上SQL语句可以得到每个用户发帖量前1名的用户：

总结

通过使用SQL的GROUP BY语句结合子查询，我们可以轻松实现分组后取每组的Top 200。这种方法简单高效，适用于各种场景下的数据处理需求。在实际开发中，可以根据具体需求调整Top N的数值，灵活应用在不同的业务场景中。

本文链接：http://so.lmcjl.com/news/13921/