分页场景下如何实现ROW_NUMBER函数的分组连续排序效果？

需求是按category_id分组，统计每组内按created_at排序的行序号。常规使用ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at)能得到正确的全局分组序号，但启用分页后，序号会基于当前页的子集重新计算，导致序号重置（比如每页4条时第二页序号错误），需要实现分页时仍保留全局的分组连续序号。

示例数据与结果对比 #

原表格

id | category_id | item_id | created_at
---|-------------|---------|-----------
 7 |      11     |   106   | 2024-05-06
 6 |      3      |   102   | 2024-05-06
 5 |      11     |   101   | 2024-05-05
 4 |      9      |   98    | 2024-05-04
 3 |      3      |   97    | 2024-05-03
 2 |      1      |   91    | 2024-05-02
 1 |      11     |   89    | 2024-05-01

常规查询正确结果

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    3
 6 |      3      |   102   | 2024-05-06 |    2
 5 |      11     |   101   | 2024-05-05 |    2
 4 |      9      |   98    | 2024-05-04 |    1
 3 |      3      |   97    | 2024-05-03 |    1
 2 |      1      |   91    | 2024-05-02 |    1  
 1 |      11     |   89    | 2024-05-01 |    1

分页（每页4条）第二页错误结果

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    2
 6 |      3      |   102   | 2024-05-06 |    1
 5 |      11     |   101   | 2024-05-05 |    1

期望结果

id | category_id | item_id | created_at | order
---|-------------|---------|------------|-------
 7 |      11     |   106   | 2024-05-06 |    3
 6 |      3      |   102   | 2024-05-06 |    2
 5 |      11     |   101   | 2024-05-05 |    2

问题原因 #

常规分页逻辑是先通过LIMIT/OFFSET截取当前页数据，再对这个子集计算ROW_NUMBER()，窗口函数仅作用于当前页的少量数据，自然会导致序号重置。要解决这个问题，必须先计算全局所有数据的分组序号，再对包含序号的完整结果集执行分页。

解决方案 #

1. 原生SQL实现

通过子查询先完成全局分组序号的计算，外层再执行分页操作：

SELECT *
FROM (
    SELECT 
        `table`.*, 
        ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) AS `order`
    FROM `table`
) AS ranked_table
ORDER BY created_at DESC -- 此处排序需和分页逻辑一致，保证分页数据的连贯性
LIMIT 4 OFFSET 4; -- 第二页，每页4条数据

2. Laravel 查询构造器实现

利用fromSub先构造包含全局序号的子查询，再基于该子查询进行分页：

$perPage = 4;
$page = 2;

// 第一步：构建计算全局序号的子查询
$rankedQuery = DB::table('table')
    ->selectRaw('*, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) AS `order`');

// 第二步：基于子查询执行分页
$result = DB::table($rankedQuery, 'ranked_table')
    ->orderBy('created_at', 'desc')
    ->forPage($page, $perPage)
    ->get();

如果需要使用Laravel自带的分页器（返回带分页参数的集合），可以用闭包写法：

$result = DB::table(function ($query) {
    $query->from('table')
          ->selectRaw('*, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at) AS `order`');
}, 'ranked_table')
->orderBy('created_at', 'desc')
->paginate($perPage, ['*'], 'page', $page);

验证结果 #

执行上述代码后，分页返回的结果会和期望结果完全一致，分组序号保持全局连续，不会因分页重置。

内容的提问来源于stack exchange，提问作者pileup