SQL 窗口函数实现高效分页查询的案例分析

SQL 各部分的逻辑执行顺序 注意到窗口函数的求值仅仅位于ORDER BY之前,而位于 SQL 的绝大部分之后。本文重点给大家介绍SQL 窗口函数实现高效分页查询功能,通过案例分析给大家介绍的很详细,感兴趣的朋友跟随小编一起看看吧

SQL 窗口函数实现高效分页查询的案例分析

不闻不若闻之,闻之不若见之,见之不若知之,知之不若行之。学至于行之而止矣。——荀子

大家好!我是只谈技术不剪发的 tony 老师。

在使用 sql 语句实现分页查询时,我们需要知道一些额外的参数信息,例如查询返回的总行数、当前所在的页数、最后一页的页数等。在传统的实现方法中我们需要执行额外的查询语句获得这些信息,本文介绍一种只需要一个查询语句就可以返回所有数据的方法,也就是通过 sql 窗口函数实现高效的分页查询功能。

本文使用的示例表和数据可以这里下载。

传统方法实现分页查询

在 sql 中实现分页查询的传统方法就是利用标准的 offset … fetch 语句或者许多数据库支持的 limit … offset 语句,例如:

?

1

2

3

4

5

6

7

8

9

10

11

— oracle、sql server、postgresql

select emp_name, sex, email

from employee

order by emp_id

offset 10 rows fetch next 10 rows only;

— mysql、postgresql、sqlite

select emp_name, sex, email

from employee

order by emp_id

limit 10 offset 10;

以上语句非常容易理解,返回的是第 2 页中的 10 条记录。但是问题在于我们如何知道总共包含多少页数据(或者总的记录数),显然在此之前我们需要执行另一个查询:

?

1

2

3

4

5

6

select count(*)

from employee;

count(*)|

——–+

25|

有了总的记录数 25 之后,我们可以计算出数据总共有 3 页,每页 10 条。

这种方法要求我们每次进行分页查询时都需要执行 2 个查询语句,使用起来不是很方便。下面我们介绍更加高效的窗口函数分页查询。

关于分页查询的实现,offset 分页对于大量数据的分页可能存在性能问题,另一种方法就是采用键集分页(keyset pagination)。

窗口函数实现分页查询

首先让我们考虑一下使用 offset 分页查询时需要哪些参数:

  • total_rows,总记录数;
  • current_page,当前所在页码;
  • max_page_size,每一页最多显示的记录数,例如 10、20、50;
  • actual_page_size,当前页实际包含的记录数;
  • row_nbr,每条记录的实际偏移量;
  • last_page,当前页是否是最后一页。

每一页最多显示的记录数(max_page_size)是我们传递给数据库的参数,其他则是查询返回的结果,我们可以通过下面的查询语句实现所有的功能:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

— oracle、sql server、postgresql

with e as ( — 初始查询

select emp_id, emp_name, sex, email

from employee

),

t as (

select emp_id, emp_name, sex, email,

count(*) over () as total_rows, — 总记录数

row_number () over (order by e.emp_id) as row_nbr — 偏移量,order by和初始查询相同

from e

order by e.emp_id — 排序

offset 10 rows — 分页

fetch next 10 rows only

)

select

emp_id, emp_name, sex, email,

count(*) over () as actual_page_size, — 当前页实际记录数

case max(row_nbr) over ()

when total_rows then 'y'

else 'n'

end as last_page, — 是否最后一页

total_rows, — 总记录数

row_nbr, — 每一条数据的偏移量

((row_nbr – 1) / 10) + 1 as current_page — 当前所在页码

from t

order by emp_id;

— mysql、postgresql、sqlite

with e as ( — 初始查询

select emp_id, emp_name, sex, email

from employee

),

t as (

select emp_id, emp_name, sex, email,

count(*) over () as total_rows, — 总记录数

row_number () over (order by e.emp_id) as row_nbr — 偏移量,order by和初始查询相同

from e

order by e.emp_id — 排序

limit 10

offset 10 rows — 分页

)

select

emp_id, emp_name, sex, email,

count(*) over () as actual_page_size, — 当前页实际记录数

case max(row_nbr) over ()

when total_rows then 'y'

else 'n'

end as last_page, — 是否最后一页

total_rows, — 总记录数

row_nbr, — 每一条数据的偏移量

((row_nbr – 1) / 10) + 1 as current_page — 当前所在页码

from t

order by emp_id;

首先,我们定义了通用表表达式 e,它是返回数据的初始查询,可以增加其他的过滤条件。

然后,我们基于 e 定义了另一个通用表表达式 t,在定义中进行了排序和分页,并且利用窗口函数 count(*) 计算总的记录数,利用窗口函数 row_number () 计算每条数据的偏移量(行号)。

接下来,我们基于 t 返回了更多的参数,利用窗口函数 count(*) 返回了当前页的实际记录数,通过窗口函数 max(row_nbr) 返回的当前页最大偏移量和总记录数的比较判断是否最后一页,以及当前所在的页码。

?

1

2

3

4

5

6

7

8

9

10

11

12

emp_id|emp_name|sex|email |actual_page_size|last_page|total_rows|row_nbr|current_page|

——+——–+—+——————-+—————-+———+———-+——-+————+

11|关平 |男 |guanping@shuguo.com| 10|n | 27| 11| 2|

12|赵氏 |女 |zhaoshi@shuguo.com | 10|n | 27| 12| 2|

13|关兴 |男 |guanxing@shuguo.com| 10|n | 27| 13| 2|

14|张苞 |男 |zhangbao@shuguo.com| 10|n | 27| 14| 2|

15|赵统 |男 |zhaotong@shuguo.com| 10|n | 27| 15| 2|

16|周仓 |男 |zhoucang@shuguo.com| 10|n | 27| 16| 2|

17|马岱 |男 |madai@shuguo.com | 10|n | 27| 17| 2|

18|法正 |男 |fazheng@shuguo.com | 10|n | 27| 18| 2|

19|庞统 |男 |pangtong@shuguo.com| 10|n | 27| 19| 2|

20|蒋琬 |男 |jiangwan@shuguo.com| 10|n | 27| 20| 2|

关于窗口函数的介绍可以参考这篇文章。

总结

本文介绍了如何利用窗口函数在一个语句中返回分页查询的结果和所需的全部参数,这种方法比传统的分页查询实现更加简洁高效。

原文链接:https://blog.csdn.net/horses/article/details/117014662

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/20698/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月11日 上午11:23
下一篇 2022年9月12日 下午12:12
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!