在 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

相关内容

热门资讯

蜻蜓订购APP贵金属订购非法期...   蜻蜓订购APP以"投资平台"名义对外宣传,主打贵金属期货(订购)交易。该平台一方面强调自身"正规...
悟空订购APP非法期货交易欺骗...   近期,有投资者反映,“悟空订购APP”等平台以白银、贵金属订购为名,宣传所谓“高收益”“低门槛”...
小赢淘金APP期货投资骗局,平...   “小赢淘金”之流,最擅长的就是用“注册送现金”、“充值返券”的糖衣炮弹来引流。别天真地以为那是福...
八方淘金APP非法期货交易欺骗...   警惕!八方生财、每日淘金、小赢淘金、悟空订购、蜻蜓订购、斗金订购等APP均涉嫌非法期货交易!  ...
智易购APP贵金属订购交易骗局...   很多人看到“3元起投”“充值送福利”“操作简单”“随时提现”这类宣传,很容易把智易购APP当成普...
喜玛银庄APP商品订购投资骗局...   喜玛银庄APP白银、铂金“现货订购”骗局警示:无资质平台涉嫌非法期货交易,投资者爆仓后如何维权?...
吉鑫乐享APP炒贵金属订购交易...   近期,多名投资者反映在“吉鑫乐享APP”进行贵金属投资时遭遇严重亏损,甚至血本无归。看似正规的“...
易掘金APP虚假期货投资骗局,...   “易掘金”的APP它打着“高收益、灵活投资”的旗号,宣称通过“商品订购”就能轻松赚钱,吸引了不少...
在大吉订购APP订购交易亏损被...   近期,不少投资者反映在“大吉订购App”上投资白银、铂金等商品遭遇严重亏损。该平台宣称提供现货订...
铂银壹号APP商品订购交易亏损...   许多中小投资者误以为网上的“商品现货订购”是合规合法的理财渠道,殊不知这背后隐藏着巨大的合规风险...
兴鑫淘金APP贵金属订购交易亏...   白银、贵金属或其他商品类投资,除国家批准的正规期货交易场所及持牌期货公司外,市面上大量第三方交易...
铂银掘金APP无资质的违规平台...   铂银掘金等平台的投资者,往往是被“注册送现金”“充值返现券”“首单免亏”等广告话术吸引,进而下载...
八方淘金APP订购交易不合规,...   “八方淘金APP”“铂银掘金APP”“铂银壹号APP”等软件被指以“现货订购”为名,实际采用高杠...
在指尖订购APP订购商品亏损被...   指尖订购APP这类平台,表面是“订购交易”,实则是披着电商外衣的非法期货陷阱。绝大多数投资者被蒙...
在犀贝易购APP做商品订购交易...   市面上打着“现货投资”“贵金属交易”旗号的APP并不少,但真正合规、受监管的平台并不多。犀贝易购...
易达淘金APP虚假商品订购交易...   易达淘金APP存在严重的虚假宣传与违规交易行为。该平台打着“商品订购”的旗号,实则通过不合规的交...
大浪淘金APP白银订购亏损被骗...   大浪淘金APP最隐蔽的套路,在于其完全封闭的内盘交易系统。表面上,它宣称与国际贵金属市场接轨;实...
一品好购APP上订购白银出现亏...   一品好购APP原名“一品订购”,运营方为上海宝智未来信息技术有限公司。近期不少投资者反映在该平台...
九州订购APP贵金属订购投资骗...   九州订购APP正通过抖.音、快手、今日头条等平台大量投放投资广告,用“5元起投”“充值返现”“0...
坚锅淘金APP交易软件并不正规...   当前,市面上大量所谓的“现货商城订购”交易软件,早已偏离了正常的实物商品买卖,演变为高杠杆、高手...