【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开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
文章目录
本篇文章讲解的主要内容是：怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
下面介绍一个数据组合去重的问题。数据环境模拟如下：
drop table test purge;
create table test(id,t1,t2,t3) as
select '1','1','3','2' from dual union all
select '2','1','3','2' from dual union all
select '3','3','2','1' from dual union all
select '4','4','2','1' from dual;
上述测试表中前三列tl、t2、t3的数据组合是重复的（都是1、2、3),要求用查询语句找出这些重复的数据,并只保留一行。我们可以用以下步骤达到需求。
1、把tl、t2、t3这三列用列转行合并为一列。
SQL> select * from test
2 unpivot(b2 for b3 in (t1,t2,t3));
ID B3 B2
-- -- --
1 T1 1
1 T2 3
1 T3 2
2 T1 1
2 T2 3
2 T3 2
3 T1 3
3 T2 2
3 T3 1
4 T1 4
4 T2 2
4 T3 1
12 rows selected
unpivot的具体用法将在后面介绍。
2、通过listagg函数对各组字符排序并合并。
SQL> with t as
2 (select * from test unpivot(b2 for b3 in(t1, t2, t3)))
3 select id, listagg(b2, ',') within group(order by b2) as nb2
4 from t
5 group by id;
ID NB2
-- --------------------------------------------------------------------------------
1 1,2,3
2 1,2,3
3 1,2,3
4 1,2,4
3、执行常用的去重语句。
SQL> with t as
2 (select * from test unpivot(b2 for b3 in(t1, t2, t3))),
3 t1 as (
4 select id, listagg(b2, ',') within group(order by b2) as nb2
5 from t
6 group by id
7 )
8 select t1.*,row_number()over(partition by nb2 order by id) as rn
9 from t1;
ID NB2 RN
-- -------------------------------------------------------------------------------- ----------
1 1,2,3 1
2 1,2,3 2
3 1,2,3 3
4 1,2,4 1
SQL>
如上所示,如果要去掉重复的组合数据,只需要保留RN=1的行即可。
构建数据如下：
drop table test purge;
create table test as select * from dba_objects;
create index idx_test_object_id on test(object_id);
begin
dbms_stats.gather_table_stats(ownname =>'ZYD' ,tabname => 'TEST',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,cascade => TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
end;
要求返回最大or最小的object_id及对应的object_name,在有分析函数以前,可以用下面的查询：
SQL> select/*zyd*/ object_name,object_id
2 from test
3 where object_id in(
4 select max(object_id) from test
5 UNION ALL
6 select min(object_id) from test
7 );
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
C_OBJ# 2
TEST 1578856
需要对员工表扫描三次。但用如下分析函数只需要对员工表扫描一次：
SQL> select/*zyd*/ object_name, object_id
2 from (select object_name,
3 object_id,
4 min(object_id) over() min_id,
5 max(object_id) over() max_id
6 from test) x
7 where object_id in (min_id, max_id);
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
C_OBJ# 2
TEST 1578856
如果大家形成惯性思维,认为分析函数的效率最高,那就错了。我们通过autotrace看下PLAN(可以多执行几次）。
第一个语句的执行计划,如下图所示：
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> alter session set current_schema=zyd;
Session altered.
Elapsed: 00:00:00.01
SQL> select/*zyd*/ object_name,object_id
from test
where object_id in(
select max(object_id) from test
UNION ALL
select min(object_id) from test
); 2 3 4 5 6 7
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2072175425
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 86 | 8 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 86 | 8 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 2 | 26 | 4 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 10 | 4 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 5 | | |
| 9 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_TEST_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 30 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("OBJECT_ID"="MAX(OBJECT_ID)")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
685 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
可以看到,第一个语句虽然访问了三次,但三次都是用的索引,所以效率并不低。第二个语句执行计划如下图所示：
SQL> select/*zyd*/ object_name, object_id
from (select object_name,
object_id,
min(object_id) over() min_id,
max(object_id) over() max_id
from test) x
where object_id in (min_id, max_id); 2 3 4 5 6 7
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 1093040662
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 115K| 11M| 540 (1)| 00:00:01 |
|* 1 | VIEW | | 115K| 11M| 540 (1)| 00:00:01 |
| 2 | WINDOW BUFFER | | 115K| 3375K| 540 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 115K| 3375K| 540 (1)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"="MIN_ID" OR "OBJECT_ID"="MAX_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1947 consistent gets
0 physical reads
0 redo size
685 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
从执行计划可以看出来，走的是全表扫描，因为用的是分析函数,效率反而更低。所以,除语句的改写外,大家还要学会分析PLAN。
本篇文章主要介绍的两个方面，第一个方面曾经有好几个网友和同事问我，第二个问题真的是很多同行的通病，认为分析函数是万金油，一股脑用。实际解决问题中，优化过很多同类问题的sql，这里给大家再次用案例做了分享，感谢观看！
我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div
总的来说，我对ruby还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru
我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t
我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚
Rackup通过Rack的默认处理程序成功运行任何Rack应用程序。例如:classRackAppdefcall(environment)['200',{'Content-Type'=>'text/html'},["Helloworld"]]endendrunRackApp.new但是当最后一行更改为使用Rack的内置CGI处理程序时，rackup给出“NoMethodErrorat/undefinedmethod`call'fornil:NilClass”:Rack::Handler::CGI.runRackApp.newRack的其他内置处理程序也提出了同样的反对意见。例如Rack
在选择我想要运行操作的频率时，唯一的选项是“每天”、“每小时”和“每10分钟”。谢谢!我想为我的Rails3.1应用程序运行调度程序。 最佳答案 这不是一个优雅的解决方案，但您可以安排它每天运行，并在实际开始工作之前检查日期是否为当月的第一天。 关于ruby-如何每月在Heroku运行一次Scheduler插件？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/8692687/
我有一个对象has_many应呈现为xml的子对象。这不是问题。我的问题是我创建了一个Hash包含此数据，就像解析器需要它一样。但是rails自动将整个文件包含在.........我需要摆脱type="array"和我该如何处理？我没有在文档中找到任何内容。 最佳答案 我遇到了同样的问题；这是我的XML:我在用这个:entries.to_xml将散列数据转换为XML，但这会将条目的数据包装到中所以我修改了:entries.to_xml(root:"Contacts")但这仍然将转换后的XML包装在“联系人”中，将我的XML代码修改为
我有一大串格式化数据(例如JSON)，我想使用Psychinruby同时保留格式转储到YAML。基本上，我希望JSON使用literalstyle出现在YAML中:---json:|{"page":1,"results":["item","another"],"total_pages":0}但是，当我使用YAML.dump时，它不使用文字样式。我得到这样的东西:---json:!"{\n\"page\":1,\n\"results\":[\n\"item\",\"another\"\n],\n\"total_pages\":0\n}\n"我如何告诉Psych以想要的样式转储标量？解