- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章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 分页查询时需要哪些参数:
每一页最多显示的记录数(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 。
最后此篇关于SQL 窗口函数实现高效分页查询的案例分析的文章就讲到这里了,如果你想了解更多关于SQL 窗口函数实现高效分页查询的案例分析的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
这个问题在这里已经有了答案: Oracle: merging two different queries into one, LIKE & IN (1 个回答) 8年前关闭。 我有以下代码: case
我查阅过此页面:http://dev.mysql.com/doc/refman/5.1/en/case.html以及这个,但无法获得一个简单的程序来工作...... 更新:为了明确我想要做什么:我想从
有什么办法可以优化下面的查询吗? SELECT DATE_FORMAT(a.duedate,'%d-%b-%y') AS dte, duedate, SUM(CASE WHEN (typeofnoti
我进退两难,以下 SQL 查询的结果是什么以及它是如何工作的: SELECT ... CASE WHEN (a.FIELD=1 AND b.FIELD=2) THEN 1 WHEN
问题:输入年,月,打印对应年月的日历。 示例: 问题分析: 1,首先1970年是Unix系统诞生的时间,1970年成为Unix的元年,1970年1月1号是星期四,现在大多的手机的日历功能只能显
**摘要:**介绍了Angular中依赖注入是如何查找依赖,如何配置提供商,如何用限定和过滤作用的装饰器拿到想要的实例,进一步通过N个案例分析如何结合依赖注入的知识点来解决开发编程中会遇到的问题。 本
我想拥有自动伴侣类apply case 类的构造函数来为我执行隐式转换,但无法弄清楚如何这样做。我到处搜索,我能找到的最接近的答案是 this问题(我将解释为什么它不是我在下面寻找的)。 我有一个看起
您好,我已经浏览了“多列案例”问题,但没有看到与此相同的内容,所以我想我应该问一下。 基本上我有两个我想要连接的表(都是子查询的结果)。它们具有相同的列名称。如果我加入他们的 ID 和 SELECT
我发现了一些类型推断的非直觉行为。因此,语义等效代码的工作方式不同,具体取决于编译器推断出的有关函数返回类型的信息。当您在最小单元测试中重现此案例时,或多或少会清楚发生了什么。但我担心在编写框架代码时
CREATE TABLE test ( sts_id int , [status1] int , [status2] int , [status3] int , [status4] int ) INS
我有以下声明: SELECT Dag AS Dag, CASE Jaar WHEN 2013 THEN Levering END AS '2013', CASE
我想做的是为所有高于平均时间、平均时间和低于平均时间的游乐设施获取平均tip_portion。所以返回3行。当我运行它时,它显示: ERROR: missing FROM-clause entry
我正在尝试设置一个包含以下字段的报告: 非常需要报告来显示日期、该日期内的总记录(因此我按日期分组),然后按小时计算 12 小时工作日(从上午 8 点到晚上 8 点)我需要计算记录在这些时间内出现的时
我有这个查询 SELECT users.name FROM users LEFT JOIN weapon_stats ON users.id = weapon_stats.zp_id WHERE we
我正在尝试按收视率等级获取不同视频的计数。我有下表: vid_id views 1 6 1 10 1 900 2 850 2 125000
假设我有一个如下所示的 SQL 语句: select supplier, case when platform in (5,6) then 'mobile' when p
我有一个表测试 TestNumber (int primary key) InactiveBitwise (int) 我执行以下命令: UPDATE tests SET CASE WH
我有一个像这样的表(name=expense): id amount date 1 -1687 2014-01-02 00:00:00.0 2 11000 2014-01-02 0
我有一个 multimap 定义 typedef std::pair au_pair; //vertices typedef std::pair acq_pair; //ch qlty specifi
我有一个有点像枚举的类,它的每个实例都有一个唯一的 int 值,该值从 0 开始并在每个新实例时递增。 class MyEnumLikeClass { static int NextId =
我是一名优秀的程序员,十分优秀!