【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是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
文章目录
本篇文章讲解的主要内容是：常用聚集函数及group by与空值的影响、详解通过执行计划看sum()over()分析函数。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
SQL> SELECT deptno,
2 AVG(sal)AS平均值,MIN(sal)AS最小值,MAX(sal)AS最大值,SUM(sal)工资合计,COUNT(*)总行数,
3 COUNT(comm)获得提成的人数,
4 AVG(comm)错误的人均提成算法,
5 AVG(coalesce(comm,0))正确的人均提成 FROM emp
6 GROUP BY deptno;
DEPTNO AS平均值 AS最小值 AS最大值 工资合计 总行数 获得提成的人数 错误的人均提成算法 正确的人均提成
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 2916.66666 1300 5000 8750 3 0 0
20 2175 800 3000 10875 5 0 0
30 1566.66666 950 2850 9400 6 4 550 366.666666
聚集函数需要注意的一点就是：聚集函数会忽略空值,这对sum等来说没什么影响,但对avg、count来说就可能会出现预料之外的结果。所以要根据需求决定是否把空值转为零。
注意,当表中没有数据时,不加group by会返回一行数据,但加了group by会没有数据返回。
建立空表：
SQL> create table emp22 as select * from emp where 1=2;
Table created
SQL> select count(*) as cnt,sum(sal) as ssal from emp22 where deptno=10;
CNT SSAL
---------- ----------
0
SQL>
有group by
SQL> select count(*) as cnt,sum(sal) as ssal from emp22 where deptno=10 group by deptno;
CNT SSAL
---------- ----------
SQL>
因此,当你在错误的地点错误地增加了group by,Oracle就会报错。没有group by时,输出正常：
SQL> declare
2 v_sal emp22.sal%type;
3 begin
4 select sum(sal) into v_sal from emp22 where deptno=10;
5 dbms_output.put_line('v_sal='||v_sal);
6 end;
7 /
v_sal=
PL/SQL procedure successfully completed
SQL>
有GROUP BY时,执行报错：
SQL> declare
2 v_sal emp22.sal%type;
3 begin
4 select sum(sal) into v_sal from emp22 where deptno=10 group by deptno;
5 dbms_output.put_line('v_sal='||v_sal);
6 end;
7 /
declare
v_sal emp22.sal%type;
begin
select sum(sal) into v_sal from emp22 where deptno=10 group by deptno;
dbms_output.put_line('v_sal='||v_sal);
end;
ORA-01403: 未找到任何数据
ORA-06512: 在 line 4
SQL>
公司为了查看用人成本,需要对员工的工资进行累加,以便查看员工人数与工资支出之间的对应关系。
首先,按进入公司的先后顺序（人员编码：empno)来累加查看。
SQL> SELECT empno AS 编号,
2 ename AS 姓名,
3 sal AS 人工成本,
4 SUM(sal) over(ORDER BY empno) AS 成本累计
5 FROM emp
6 WHERE deptno = 30
7 ORDER BY empno;
编号 姓名 人工成本 成本累计
----- ---------- --------- ----------
7499 ALLEN 1600.00 1600
7521 WARD 1250.00 2850
7654 MARTIN 1250.00 4100
7698 BLAKE 2850.00 6950
7844 TURNER 1500.00 8450
7900 JAMES 950.00 9400
6 rows selected
通过上面SQL可以看到,分析函数SUM(sal) over(ORDER BY empno)的结果是排序over(ORDER BY empno)后第一行到当前行的所有工资之和。
我们先看一下该语句的PLAN:
Plan hash value: 155210085
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | WINDOW BUFFER | | 6 | 102 | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 102 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_EMPNO | 15 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_b_tree_bitmap_plans' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_optimizer_unnest_scalar_sq' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_dsdir_usage_control' 0)
OPT_PARAM('_optimizer_adaptive_plans' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_fix_control' '8611462:0 14826303:0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (NUMBER): 30
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPTNO"=:SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "EMPNO"[NUMBER,22], "EMP".ROWID[ROWID,10], "DEPTNO"[NUMBER,22],
"ENAME"[VARCHAR2,10], "SAL"[NUMBER,22], SUM("SAL") OVER ( ORDER BY "EMPNO" RANGE
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )[22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10],
"SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]
3 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
76 rows selected.
大家请看上面ld=1的语句：
SUM(sal)over(ORDERBYempno)
转换成了如下语句：
SUM("SAL") OVER ( ORDER BY "EMPNO" RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
这个语句前面的SUM(“SAL”)容易理解,就是对sal求和。后面分为以下三部分：
ORDER BY “EMPNO”:按EMPNO排序。
RANGE:表示这是一个范围开窗。
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示区间从UNBOUNDED PRECEDING(第一行）到CURRENT ROW(当前行）。
为了形象地说明这一点,我们用listagg模拟出每一行是哪些值相加。
SQL>
SQL> SELECT empno AS 编号,
2 ename AS 姓名,
3 sal AS 人工成本,
4 SUM(sal) over(ORDER BY empno) AS 成本累计,
5 (select listagg(sal,'+') within group(order by empno) from emp e where deptno = 30and e.empno<=emp.empno) as 计算公式
6 FROM emp
7 WHERE deptno = 30
8 ORDER BY empno;
编号 姓名 人工成本 成本累计 计算公式
----- ---------- --------- ---------- --------------------------------------------------------------------------------
7499 ALLEN 1600.00 1600 1600
7521 WARD 1250.00 2850 1600+1250
7654 MARTIN 1250.00 4100 1600+1250+1250
7698 BLAKE 2850.00 6950 1600+1250+1250+2850
7844 TURNER 1500.00 8450 1600+1250+1250+2850+1500
7900 JAMES 950.00 9400 1600+1250+1250+2850+1500+950
6 rows selected
下面是分析函数简写、rows开窗、range开窗、标量方式的累加方法对比,及标量方式的解释。
SELECT empno,
sal,
SUM(sal) over(ORDER BY empno) AS 简写,
SUM(sal) over(ORDER BY empno rows BETWEEN unbounded preceding AND CURRENT ROW) AS row开窗,
SUM(sal) over(ORDER BY empno RANGE BETWEEN unbounded preceding AND CURRENT ROW) AS range开窗,
(SELECT SUM(sal) FROM emp b WHERE b.empno <= a.empno) AS 标量,
'(SELECT SUM(sal)FROM emp b WHERE b.empno<=' || a.empno || ')' AS 标量解释
FROM emp a
WHERE deptno = 30
ORDER BY 1;
在这个案例中,简写、ROW开窗、RANGE开窗、标量几列写法等价。
在没有分析函数的时候,计算累加经常要用这个示例中标量的方式,因为使用标量需要两次访问emp表,会比较慢,是做优化时被改写的目标。
最后一列"标量解释"是每行的计算方式说明,取出来单独执行就是每行的值。
需要注意,本章中各示例语句最后的排序子句只是为了方便大家观察,与分析函数的结果无关.
本章主要是介绍一下常用分析函数在有无空值的情况下，group by写法的差异，以及通过一个简单的累加需求的执行计划，看分析函数到底是怎么改写的！！！
很好奇，就使用rubyonrails自动化单元测试而言，你们正在做什么？您是否创建了一个脚本来在cron中运行rake作业并将结果邮寄给您？git中的预提交Hook？只是手动调用？我完全理解测试，但想知道在错误发生之前捕获错误的最佳实践是什么。让我们理所当然地认为测试本身是完美无缺的，并且可以正常工作。下一步是什么以确保他们在正确的时间将可能有害的结果传达给您？ 最佳答案 不确定您到底想听什么，但是有几个级别的自动代码库控制:在处理某项功能时，您可以使用类似autotest的内容获得关于哪些有效，哪些无效的即时反馈。要确保您的提
关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。
在控制台中反复尝试之后，我想到了这种方法，可以按发生日期对类似activerecord的(Mongoid)对象进行分组。我不确定这是完成此任务的最佳方法，但它确实有效。有没有人有更好的建议，或者这是一个很好的方法？#eventsisanarrayofactiverecord-likeobjectsthatincludeatimeattributeevents.map{|event|#converteventsarrayintoanarrayofhasheswiththedayofthemonthandtheevent{:number=>event.time.day,:event=>ev
我正在编写一个包含C扩展的gem。通常当我写一个gem时，我会遵循TDD的过程，我会写一个失败的规范，然后处理代码直到它通过，等等......在“ext/mygem/mygem.c”中我的C扩展和在gemspec的“扩展”中配置的有效extconf.rb，如何运行我的规范并仍然加载我的C扩展？当我更改C代码时，我需要采取哪些步骤来重新编译代码？这可能是个愚蠢的问题，但是从我的gem的开发源代码树中输入“bundleinstall”不会构建任何native扩展。当我手动运行rubyext/mygem/extconf.rb时，我确实得到了一个Makefile(在整个项目的根目录中)，然后当
这里是Ruby新手。完成一些练习后碰壁了。练习:计算一系列成绩的字母等级创建一个方法get_grade来接受测试分数数组。数组中的每个分数应介于0和100之间，其中100是最大分数。计算平均分并将字母等级作为字符串返回，即“A”、“B”、“C”、“D”、“E”或“F”。我一直返回错误:avg.rb:1:syntaxerror,unexpectedtLBRACK,expecting')'defget_grade([100,90,80])^avg.rb:1:syntaxerror,unexpected')',expecting$end这是我目前所拥有的。我想坚持使用下面的方法或.join，
这是一道面试题，我没有答对，但还是很好奇怎么解。你有N个人的大家庭，分别是1,2,3,...,N岁。你想给你的大家庭拍张照片。所有的家庭成员都排成一排。“我是家里的friend，建议家庭成员安排如下:”1岁的家庭成员坐在这一排的最左边。每两个坐在一起的家庭成员的年龄相差不得超过2岁。输入:整数N，1≤N≤55。输出:摄影师可以拍摄的照片数量。示例->输入:4，输出:4符合条件的数组:[1,2,3,4][1,2,4,3][1,3,2,4][1,3,4,2]另一个例子:输入:5输出:6符合条件的数组:[1,2,3,4,5][1,2,3,5,4][1,2,4,3,5][1,2,4,5,3][
我已经构建了一些serverspec代码来在多个主机上运行一组测试。问题是当任何测试失败时，测试会在当前主机停止。即使测试失败，我也希望它继续在所有主机上运行。Rakefile:namespace:specdotask:all=>hosts.map{|h|'spec:'+h.split('.')[0]}hosts.eachdo|host|begindesc"Runserverspecto#{host}"RSpec::Core::RakeTask.new(host)do|t|ENV['TARGET_HOST']=hostt.pattern="spec/cfengine3/*_spec.r
我们的git存储库中目前有一个Gemfile。但是，有一个gem我只在我的环境中本地使用(我的团队不使用它)。为了使用它，我必须将它添加到我们的Gemfile中，但每次我checkout到我们的master/dev主分支时，由于与跟踪的gemfile冲突，我必须删除它。我想要的是类似Gemfile.local的东西，它将继承从Gemfile导入的gems，但也允许在那里导入新的gems以供使用只有我的机器。此文件将在.gitignore中被忽略。这可能吗？ 最佳答案 设置BUNDLE_GEMFILE环境变量:BUNDLE_GEMFI
这似乎非常适得其反，因为太多的gem会在window上破裂。我一直在处理很多mysql和ruby-mysqlgem问题(gem本身发生段错误，一个名为UnixSocket的类显然在Windows机器上不能正常工作，等等)。我只是在浪费时间吗？我应该转向不同的脚本语言吗？ 最佳答案 我在Windows上使用Ruby的经验很少，但是当我开始使用Ruby时，我是在Windows上，我的总体印象是它不是Windows原生系统。因此，在主要使用Windows多年之后，开始使用Ruby促使我切换回原来的系统Unix，这次是Linux。Rub
在应用开发中，有时候我们需要获取系统的设备信息，用于数据上报和行为分析。那在鸿蒙系统中，我们应该怎么去获取设备的系统信息呢，比如说获取手机的系统版本号、手机的制造商、手机型号等数据。1、获取方式这里分为两种情况，一种是设备信息的获取，一种是系统信息的获取。1.1、获取设备信息获取设备信息，鸿蒙的SDK包为我们提供了DeviceInfo类，通过该类的一些静态方法，可以获取设备信息，DeviceInfo类的包路径为：ohos.system.DeviceInfo.具体的方法如下：ModifierandTypeMethodDescriptionstatic StringgetAbiList()Obt