商赢网站建设,wordpress读取mysql,wordpress管理页面地址,简易的网站系列文章目录
【SQL开发实战技巧】系列#xff08;一#xff09;:关于SQL不得不说的那些事 【SQL开发实战技巧】系列#xff08;二#xff09;#xff1a;简单单表查询 【SQL开发实战技巧】系列#xff08;三#xff09;#xff1a;SQL排序的那些事 【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查询结果集分页比较好二、分析函数的使用限制及Merge用对场景对性能提升非常大三、隔行抽样总结前言
本篇文章讲解的主要内容是怎样对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 可能有朋友会感觉我这么嵌套的有点麻烦我列举一下你们想的
为什么不直接在内层应用条件WHERE rownum10呢
下面对比一下rownum的结果。
SQL select rownum rnn,aa.*2 from (3 select rownum as rn ,sal, ename from emp where deptno20 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的顺序不一样,要想得到正确的顺序就要先排序后取序号。
为什么不直接用rownum10 and rownum6,而要分开写呢
下面来看一下。 SQL select * from emp where rownum6 and rownum 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------SQL 如前面博客所述,因为rownum是一个伪列,需要取出数据后,rownum才会有值,在执行where rownum6时,因为始终没取前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 rn10;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*100row_number()over(partition by floor_nbr order by floor_nbr));
update hotel set room_nbr (floor_nbr*100row_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*100row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr3 from hotel4 )5 set room_nbrnew_room_nbr;
update (
select rowid as rid,room_nbr,(floor_nbr*100row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
from hotel
)
set room_nbrnew_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.ROWIDBB.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做分页的比较多但是在有些场景这个效率真的挺低。我身边很多人都会认为分析函数比传统写法简单并且效率高无论什么场景都一股脑用分析函数但是很多时候都会出现效率低下的问题究其原因类似第二种案例也建议大家在实际开发过程中注意下。最后介绍的这个隔行抽样是某网友问我的一个某国企的面试题也放上来啦。
