【SQL开发实战技巧】系列（二十二）：数仓报表场景(上) 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景(上) 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式

---

---

前言

本篇文章讲解的主要内容是：怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

---

一、怎样对SQL查询结果集分页比较好

工作中我们为了便于查询网页中的数据,经常会对从数据库查询到的结果集进行分页显示。
比如我现在有一个需求：
要求员工表(EMP的数据）按工资排序后一次只显示5行数据,下次再显示接下来的5行。
下面以第二页数据(6到10行）为例进行分页。
前面已讲过,要先排序,然后在外层才能生成正确的序号：

SQL> with t as/*先排序*/2   (select sal, ename from emp where sal is not null order by sal),3  t1 as/*取得排序后的序号,并过滤掉10行以后的数据*/4   (select rownum as rn, sal, ename from t where rownum <= 10)/*根据前面生成的序号过滤掉6行以前的数据*/5  select * from t1 where rn >= 66  ;RN       SAL ENAME
---------- --------- ----------6   1300.00 MILLER7   1500.00 TURNER8   1600.00 ALLEN9   2450.00 CLARK10   2850.00 BLAKESQL> 

可能有朋友会感觉我这么嵌套的有点麻烦，我列举一下你们想的：

1. 为什么不直接在内层应用条件WHERE rownum<=10呢？

下面对比一下rownum的结果。

SQL> select rownum rnn,aa.*2  from (3  select rownum as rn ,sal, ename from emp where deptno=20 and sal is not null4  order by sal5  ) aa;RNN         RN       SAL ENAME
---------- ---------- --------- ----------1          1    800.00 SMITH2          4   1100.00 ADAMS3          2   2975.00 JONES4          5   3000.00 FORD5          3   3000.00 SCOTTSQL> 

可以看到,内层直接生成的rownum(rn)与sal的顺序不一样,要想得到正确的顺序就要先排序后取序号。

2. 为什么不直接用rownum<=10 and rownum>=6,而要分开写呢？

下面来看一下。

 SQL> select * from emp where rownum>=6 and rownum <=10;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------SQL> 

如前面博客所述,因为rownum是一个伪列,需要取出数据后,rownum才会有值,在执行where rownum>=6时,因为始终没取前10条数据出来,所以这个条件就查询不到数据,需要先在子查询中取出数据,然后外层用WHERE rn >=6来过滤。

你也可以先用row_number()生成序号,再过滤,这样就只需要嵌套一次。

SQL> 
SQL> select aa.*2  from (3  select row_number()over(order by sal) as rn ,sal, ename from emp where sal is not null4  order by sal5  ) aa6  where rn >=6 and rn<=10;RN       SAL ENAME
---------- --------- ----------6   1300.00 MILLER7   1500.00 TURNER8   1600.00 ALLEN9   2450.00 CLARK10   2850.00 BLAKESQL> 

这个语句比较简单,但因为分页语句的特殊性,在调用PLAN时可能会受到分析函数的影响,有些索引或PLAN(如：first_rows)不能用。所以,在此建议大家使用第一种分页方式,把第一种分页方式当作模板,然后套用。

二、分析函数的使用限制及Merge用对场景对性能提升非常大

现有数据如下：

CREATE  TABLE hotel(floor_nbr,room_nbr)AS
SELECT  1,100 FROM dual UNION ALL
SELECT  1,100 FROM dual UNION ALL
SELECT  2,100 FROM dual UNION ALL
SELECT  2,100 FROM dual UNION ALL
SELECT  3,100 FROM dual;

现在有个需求：将floor_nbr编号更新为类似：101、102、201、202的数据。
我们可以用row_number重新生成编号,或许马上会有读者想到UPDATE语句。让我们来执行一下。

SQL> update hotel set room_nbr =(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr));
update hotel set room_nbr =(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr))ORA-30483: window  函数在此禁用SQL> 

有人想用“可更新VIEW",语句如下：

SQL> update (2  select rowid as rid,room_nbr,(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr3  from hotel4  )5  set room_nbr=new_room_nbr;
update (
select rowid as rid,room_nbr,(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
from hotel
)
set room_nbr=new_room_nbrORA-01732: 此视图的数据操纵操作非法

这些方法都不可靠,当然,把UPDATE再嵌套一层也可以：

SQL> update hotel a2     set room_nbr =3         (select new_room_nbr4            from (select rowid as rid,5                         room_nbr,6                         (floor_nbr * 100 + row_number()7                          over(partition by floor_nbr order by floor_nbr)) as new_room_nbr8                    from hotel) bb9           where a.rowid = bb.rid);5 rows updatedSQL> select * from hotel;FLOOR_NBR   ROOM_NBR
---------- ----------1        1011        1022        2012        2023        301SQL> rollback;Rollback completeSQL> 

执行计划如下：

 Plan Hash Value  : 4147990906 -------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |       |    5 |    30 |   28 | 00:00:01 |
|   1 |   UPDATE               | HOTEL |      |       |      |          |
|   2 |    TABLE ACCESS FULL   | HOTEL |    5 |    30 |    3 | 00:00:01 |
| * 3 |    VIEW                |       |    5 |   125 |    4 | 00:00:01 |
|   4 |     WINDOW SORT        |       |    5 |    30 |    4 | 00:00:01 |
|   5 |      TABLE ACCESS FULL | HOTEL |    5 |    30 |    3 | 00:00:01 |
-------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("BB"."RID"=:B1)

这种方法虽然能成功,但由执行计划可以看到,子查询执行了5次,对HOTEL有5次全表扫描。那么如果表更大,是不是更慢？博主之前就经常遇到有同事写出类似update SQL，上线的时候可能跑一晚上也跑不出来。
另一个方法是用MERGE语句：

SQL> merge into hotel a2  using (select rowid as rid,3                room_nbr,4                (floor_nbr * 100 + row_number()5                 over(partition by floor_nbr order by floor_nbr)) as new_room_nbr6           from hotel) bb7  on (a.rowid = bb.rid)8  when matched then update set a.room_nbr = bb.new_room_nbr;5 rows mergedSQL> select * from hotel;FLOOR_NBR   ROOM_NBR
---------- ----------1        1011        1022        2012        2023        301SQL> 

执行计划如下：

 Plan Hash Value  : 1282884214 ---------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------
|   0 | MERGE STATEMENT          |       |    5 |    80 |    7 | 00:00:01 |
|   1 |   MERGE                  | HOTEL |      |       |      |          |
|   2 |    VIEW                  |       |      |       |      |          |
| * 3 |     HASH JOIN            |       |    5 |   220 |    7 | 00:00:01 |
|   4 |      VIEW                |       |    5 |   190 |    4 | 00:00:01 |
|   5 |       WINDOW SORT        |       |    5 |    30 |    4 | 00:00:01 |
|   6 |        TABLE ACCESS FULL | HOTEL |    5 |    30 |    3 | 00:00:01 |
|   7 |      TABLE ACCESS FULL   | HOTEL |    5 |    30 |    3 | 00:00:01 |
---------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("A".ROWID="BB"."RID")

通过PLAN可以看到,使用MERGE子查询只对hotel访问了一次,效率提高了很多。

三、隔行抽样

有时为了取样而不是查看所有的数据,要对数据进行抽样,前面介绍过选取随机行,这里将介绍隔行返回。
现在有下面需求：对员工表中的数据每隔一行返回一个员工。

为了实现这个目标,用求余函数mod即可,我们看一下mod的结果。

SQL> select *2    from (select mod(rn, 2) as md, aa.*3            from (select rownum as rn, a.*4                    from (select ename, empno, sal from emp order by ename) a) aa)5   where md = 0;MD         RN ENAME      EMPNO       SAL
---------- ---------- ---------- ----- ---------0          2 ALLEN       7499   1600.000          4 CLARK       7782   2450.000          6 JAMES       7900    950.000          8 KING        7839   5000.000         10 MILLER      7934   1300.000         12 SMITH       7369    800.000         14 WARD        7521   1250.007 rows selected

通过这个函数,想间隔几行返回都可以实现。

---

总结

本篇博客主要给大家介绍了：

• 分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。
• 我身边很多人都会认为分析函数比传统写法简单并且效率高，无论什么场景都一股脑用分析函数，但是很多时候都会出现效率低下的问题，究其原因类似第二种案例，也建议大家在实际开发过程中，注意下。
• 最后介绍的这个隔行抽样是某网友问我的一个某国企的面试题，也放上来啦。