在 SQL 中计算分页元数据，无需额外的往返_市场资讯

在 SQL 中计算分页元数据，无需额外的往返

admin

2024-03-19 14:26:15

0次

在 SQL 中对结果进行分页时，我们使用标准 SQL或供应商特定的版本，例如。例如：OFFSET .. FETCHLIMIT .. OFFSET

SELECT first_name, last_name FROM actor ORDER BY actor_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

与往常一样，我们将Sakila 数据库用于此示例。

这是相当简单的。它将为我们提供 N 页中的第 2 页，页面大小为 10。但是我们如何计算这些值呢？我们怎么知道我们在第 2 页？我们怎么知道页数？我们可以在没有额外往返的情况下计算这一点，例如计算演员总数：N

-- Yuck, a second round-trip! SELECT COUNT(*) FROM actor

我们可以使用单个SQL查询和窗口函数来做到这一点，但是在我解释如何做到这一点之前，请考虑阅读这篇文章，了解为什么OFFSET分页对您的性能不利。

正在上传…重新上传取消

如果您仍然确信分页是您需要的，而不是键集分页，让我们看看如何使用SQL计算上述元数据。OFFSET

我们需要什么元数据？

我们通常需要使用以下元数据进行分页：OFFSET

TOTAL_ROWS：未分页时的记录总数
CURRENT_PAGE：我们所在的当前页面
MAX_PAGE_SIZE：最大页面大小
ACTUAL_PAGE_SIZE：实际页面大小（在最后一页上时）
ROW：返回行的实际偏移量
LAST_PAGE：我们是否在最后一页

最大页面大小是我们为查询设置的，因此不必计算。其他一切都需要计算。以下是在单个查询中执行此操作的方法

SELECT t.first_name, t.last_name, -- Calculate some meta data COUNT(*) OVER () AS actual_page_size, MAX(row) OVER () = total_rows AS last_page, -- Metadata from the subquery total_rows, row, ((row - 1) / :max_page_size) + 1 AS current_page FROM ( SELECT u.*, -- Calculate some meta data, repeating the ORDER BY from -- the original query COUNT(*) OVER () AS total_rows, -- Order by some deterministic criteria, e.g. a primary key ROW_NUMBER () OVER (ORDER BY u.actor_id) AS row -- Original query with all the predicates, joins, as a derived table FROM ( SELECT * FROM actor ) AS u -- Ordering and pagination done here, where :offset is -- The maximum row value of the previous page + 1 ORDER BY u.actor_id OFFSET :offset ROWS FETCH NEXT :max_page_size ROWS ONLY ) AS t ORDER BY t.actor_id

就是这样。令人印象深刻？不要害怕，我会一步一步地引导你完成这些事情。如果您对 SQL 语法感到困惑，请考虑本文解释 SQL 操作的逻辑顺序，对于我们的示例：

FROM（派生表的递归排序）
WHERE（示例省略）
WINDOW计算
SELECT（投影）
ORDER BY
OFFSET .. FETCH

注释我们的查询，将操作逻辑排序为 1.1、1.2、2.1、2.2、2.3、2.4、2.5、3.1、3.2、3.3、3.4：

-- 3.3 SELECT t.first_name, t.last_name, -- 3.2 COUNT(*) OVER () AS actual_page_size, MAX(row) OVER () = total_rows AS last_page, -- 3.3 total_rows, row, ((row - 1) / :max_page_size) + 1 AS current_page -- 3.1 FROM ( -- 2.3 SELECT u.*, -- 2.2 COUNT(*) OVER () AS total_rows, ROW_NUMBER () OVER (ORDER BY u.actor_id) AS row -- 2.1 FROM ( -- 1.2 SELECT * -- 1.1 FROM actor ) AS u -- 2.4 ORDER BY u.actor_id -- 2.5 OFFSET :offset ROWS FETCH NEXT :max_page_size ROWS ONLY ) AS t -- 3.4 ORDER BY t.actor_id

分步说明

首先，原始查询包装为调用的派生表。您几乎可以使用此原始查询执行任何操作，只需应用几个转换：SELECT * FROM actoru

1.1、1.2、2.1：您需要投影（子句）原始查询投影的列以及所需的列。因为我在最外面的查询中投影了正确的内容，并且原始查询中没有子句，所以我方便地投影了。或者，我可以投影，（因为这是在原始查询中投影的），并且（因为这就是我们）。SELECTORDER BYDISTINCT*FIRST_NAMELAST_NAMEACTOR_IDORDER BY
2.2：在该派生表上，我们现在能够计算一些元数据，包括asandas。窗口函数有一个空的窗口规范，这意味着它计算由,,,子句产生的所有行，即from在我们的特定示例中。没有第二次往返！对所有行进行排序，并根据该顺序为它们分配唯一的行号。确保顺序条件是确定性的，否则如果排序产生关系，您可能会得到随机结果。uTOTAL_ROWSCOUNT(*) OVER ()ROWROW_NUMBER () OVER (ORDER BY t.actor_id)COUNT(*) OVER ()OVER ()FROMWHEREGROUP BYHAVINGuROW_NUMBER () OVER (ORDER BY u.actor_id)uu.actor_id
2.3：窗口函数是隐式计算的，因为它们位于此派生表的投影中。我们还将再次方便地投影所有内容，因为最外层的查询是显式投影列的查询。u.*
2.4：原始排序已移至此处，因为无法保证如果我们订购了内容，则不会保持排序。但是我们需要排序在之后立即计算uOFFSET .. FETCH
2.5：这是我们分页的地方。对应于我们之前遇到的最大值。我们从下一页开始，页面大小为，我们使用下一页。请记住，虽然索引基于SQL，但基于SQL。OFFSETROW015151OFFSET0
3.1：以上所有内容再次包装在一个派生表中，以便对其进行进一步计算，即：
3.2：我们可以再次计算，计算由 ,,, 子句（即 from）产生的总行数在我们的特定示例中。这一次，行数不能超过，因为这就是里面的（或）子句所说的。但它也可以更少，所以这就是我们用来计算的。最后，我们比较看看我们是否在最后一页，这意味着当前页面中产生的最大值与总行数相比。计算值的另一种方法是如果，即COUNT(*) OVER ()FROMWHEREGROUP BYHAVINGtMAX_PAGE_SIZEFETCHLIMITtACTUAL_PAGE_SIZEMAX(row) OVER () = total_rowsrowtLAST_PAGEACTUAL_PAGE_SIZE < MAX_PAGE_SIZECOUNT(*) OVER () < :MAX_PAGE_SIZE
3.3：除了原始列的通常投影（我们现在不再投影！），我们正在做一些最终计算，包括除法以获得页码。您可以计算更多的东西，例如获取值。FIRST_NAMELAST_NAME*ROW / TOTAL_ROWSTOTAL_ROWS / MAX_PAGE_SIZETOTAL_PAGES
3.4：最后，我们必须再次重申，不要让任何人告诉你。在 SQL 中，如果不这样做，则排序是未定义的。当然，对于优化器来说，在没有任何充分理由的情况下重新排序是愚蠢的。我们已经在 2.4 中对子查询的内容进行了排序，但不能保证这种排序是稳定的。只需向查询添加或导致哈希连接或其他一些随机运算符，排序就会中断。所以，总是如果订购对您很重要。ORDER BY t.actor_idORDER BYDISTINCTUNIONJOINORDER BY

我们完成了！

如何在jOOQ中做到这一点？

这是jOOQ真正闪耀的用例，因为所有这些都与动态SQL有关。您的实际业务逻辑包含在深度嵌套表中。其他一切都是“表示逻辑”，它在SQL中实现的原因非常明显：提高性能。u

而且，由于您希望仅在自己的某个库中实现所有这些操作一次，因此不必在每个查询上都玩此游戏，因此可以使这种查询动态化。该实用程序将如下所示：

// Assuming as always the usual static imports, including: // import static org.jooq.impl.DSL.*; // import com.generated.code.Tables.*; static Select paginate( DSLContext ctx, Select original, Field[] sort, int limit, int offset ) { Table u = original.asTable("u"); Field totalRows = count().over().as("total_rows"); Field row = rowNumber().over().orderBy(u.fields(sort)) .as("row"); Table t = ctx .select(u.asterisk()) .select(totalRows, row) .from(u) .orderBy(u.fields(sort)) .limit(limit) .offset(offset) .asTable("t"); Select result = ctx .select(t.fields(original.getSelect().toArray(Field[]::new))) .select( count().over().as("actual_page_size"), field(max(t.field(row)).over().eq(t.field(totalRows))) .as("last_page"), t.field(totalRows), t.field(row), t.field(row).minus(inline(1)).div(limit).plus(inline(1)) .as("current_page")) .from(t) .orderBy(t.fields(sort)); // System.out.println(result); return result; }

注意到要调试的 println 了吗？它将再次打印类似于我们原始查询的内容（但默认情况下，您还将在调试日志输出中看到jOOQ）：

select t.ACTOR_ID, t.FIRST_NAME, t.LAST_NAME, count(*) over () as actual_page_size, (max(t.row) over () = t.total_rows) as last_page, t.total_rows, t.row, ((t.row / 15) + 1) as current_page from ( select u.*, count(*) over () as total_rows, row_number() over (order by u.ACTOR_ID) as row from ( select ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME from ACTOR ) as u order by u.ACTOR_ID offset 30 rows fetch next 15 rows only ) as t order by t.ACTOR_ID

以下是您如何调用该实用程序：

System.out.println( paginate( ctx, ctx.select(ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME) .from(ACTOR), new Field[] { ACTOR.ACTOR_ID }, 15, 30 ).fetch() );

请注意，您可以将任意 SQL 片段插入到该实用程序中并对其进行分页。无论复杂性如何（包括连接、其他窗口函数、分组、递归等等），jOOQ 都会为您提供帮助，现在将为您分页。

上述结果为：

+--------+----------+---------+----------------+---------+----------+----+------------+
|ACTOR_ID|FIRST_NAME|LAST_NAME|actual_page_size|last_page|total_rows| row|current_page|
+--------+----------+---------+----------------+---------+----------+----+------------+
|      31|SISSY     |SOBIESKI |              15|false    |       200|  31|           3|
|      32|TIM       |HACKMAN  |              15|false    |       200|  32|           3|
|      33|MILLA     |PECK     |              15|false    |       200|  33|           3|
|      34|AUDREY    |OLIVIER  |              15|false    |       200|  34|           3|
|      35|JUDY      |DEAN     |              15|false    |       200|  35|           3|
|      36|BURT      |DUKAKIS  |              15|false    |       200|  36|           3|
|      37|VAL       |BOLGER   |              15|false    |       200|  37|           3|
|      38|TOM       |MCKELLEN |              15|false    |       200|  38|           3|
|      39|GOLDIE    |BRODY    |              15|false    |       200|  39|           3|
|      40|JOHNNY    |CAGE     |              15|false    |       200|  40|           3|
|      41|JODIE     |DEGENERES|              15|false    |       200|  41|           3|
|      42|TOM       |MIRANDA  |              15|false    |       200|  42|           3|
|      43|KIRK      |JOVOVICH |              15|false    |       200|  43|           3|
|      44|NICK      |STALLONE |              15|false    |       200|  44|           3|
|      45|REESE     |KILMER   |              15|false    |       200|  45|           3|
+--------+----------+---------+----------------+---------+----------+----+------------+

或者，在最后一页上，偏移量为 195

+--------+----------+---------+----------------+---------+----------+----+------------+
|ACTOR_ID|FIRST_NAME|LAST_NAME|actual_page_size|last_page|total_rows| row|current_page|
+--------+----------+---------+----------------+---------+----------+----+------------+
|     196|BELA      |WALKEN   |               5|true     |       200| 196|          14|
|     197|REESE     |WEST     |               5|true     |       200| 197|          14|
|     198|MARY      |KEITEL   |               5|true     |       200| 198|          14|
|     199|JULIA     |FAWCETT  |               5|true     |       200| 199|          14|
|     200|THORA     |TEMPLE   |               5|true     |       200| 200|          14|
+--------+----------+---------+----------------+---------+----------+----+------------+

结论

jOOQ是关于动态SQL的。几乎没有任何jOOQ不支持的SQL功能。例如，这包括窗口函数，但也要确保动态SQL在大量SQL方言上工作，而不考虑小的语法细节。

如本文所示，您可以构建自己的库，以从其他 SQL 构建基块构造可重用的 SQL 元素，以动态创建单查询分页元数据计算，而无需执行额外的数据库往返。OFFSET

词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：足浴店投资人：赞助沧州雄狮没花多少钱，意在支持家乡球队足浴店投资人：赞助沧州雄狮没花多少钱，意在支持家乡球队

下一篇：小托马斯重新获得机会但太阳是适合他的球队吗？小托马斯重新获得机会但太阳是适合他的球队吗？

在 SQL 中计算分页元数据，无需额外的往返

我们需要什么元数据？

分步说明

如何在jOOQ中做到这一点？

结论

相关内容

热门资讯