《14从案例中感悟SQL等价改写优化思路之2.pdf》由会员分享,可在线阅读,更多相关《14从案例中感悟SQL等价改写优化思路之2.pdf(38页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区基于案例学SQL优化第14周从案例中感悟从案例中感悟SQL等等价改写优化思路之价改写优化思路之2基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区法律声明【声明】本视频和幻灯片为炼数成金网络课程的教学资料,所有资料只能在课程内使用,不得在课程以外范围散播,违者将可能被追究法律和经济责任。课程详情访问炼数成金培训网站http:/基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区当前课程进度基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区高级SQL之分析函数基于案例学SQL优化讲师 梁敬彬D
2、ATAGURU专业数据分析社区特别之处基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区特别之处VS1.表记录10条,返回10条;2.order by 的有无还还影响了结果1.表记录10条,返回3条;2.这里的order by就是排序的意思基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区语法概述基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区分析函数总体语法Analytic-Function(,)over()SUM(SUM(salsal)OVER(PARTITION BY)OVER(PARTITION BY dept_iddept_id ORDER
3、 BY ORDER BY hire_datehire_date ROWSROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING)SUM(SUM(salsal)OVER(PARTITION BY)OVER(PARTITION BY dept_iddept_id ORDER BY ORDER BY salsal RANGERANGE BETWEEN UNBOUNDED PRECEDING AND BETWEEN UNBOUNDED PRECEDING AND 2500
4、FOLLOWING2500 FOLLOWING)基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区1.统计函数(MAX MIN AVG SUM COUNT等)2.排序函数(ROW_NUMBER RANK DENSE_RNAK FIRST FIRST_VALUE LAST LAST_VALUE LAG LEAD 等)3.数据分布函数(NTILE CUME_DIST PERCENT_RANK PERCENTILE_CON PERCENTILE_DISC RATIO_TO_REPORT 等)4.数学分析函数(CORR COVAR_POP COVAR_SAMP STDDEV STDDEV
5、_SAMP VAR_POP VAR_SAMP VARIANCE 等)总体语法第1部分 Analytic-Function对于大部分人员来说,用的最多的是第1和第2类,不过第3类中的RATIO_TO_REPORT倒是用的频率还不小)。基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区总体语法第2部分 Analytic_Clause query_partition_clause order_by_clause windows_clause 基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区Analytic_Clause之PARTITION子句1.Partition by
6、子句根据一个或多个value_expr将查询结果集分成若干组。若不使用该子句,那么函数将查询结果集的所有行当作一个组。2.在分析函数中使用query_partition_clause,应该使用语法图中上分支中的语法(不带圆括号)。model查询(位于model column clauses中)或被分隔的外部连接(位于outer_join_clause中)中使用该子句,应该使用语法图中下分支中的语法(带有圆括号)。3.在同一个查询中可以使用多个分析函数,它们可以有相同或不同的partition by键值4)若被查询的对象具有并行特性,并且分析函数中包含query_partition_clause
7、,那么函数的计算也是并行的。5)value_expr的有效值:包括常量,表列,非分析函数,函数表达式,或者前面这些元素的任意组合表达式。基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区Analytic_Clause之ORDER BY子句1.有ORDER BY的存在将添加一个默认的开窗子句!意味着从第一行到当前行;2.没有ORDER BY时,默认的窗口是全部的分区;3.在Order by 子句后可添加nulls last,如:order by comm desc nulls last 表示排序时忽略comm列为空的行;4.Sibling关键字在此处是非法的,仅与层次查询有关。基于
8、案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区Analytic_Clause之WINDOWING子句1.用于定义分析函数将在其上操作的行的集合,Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行(即:range unbounded preceding),要使用窗口,必须使用ORDER BY子句。2.row|range:这些关键字为每一行定义一个窗口,该窗口用于计算函数结果(物理或者逻辑的行的集合)。然后对窗口中的每一行应用分析函数。窗口在查询结果集或者分组中从上至下移动。
9、3.根据2个标准可以建立窗口:数据值的范围(逻辑偏移量-range)或与当前行的行偏移量(物理单位-rows)4.只有指定order_by_clause后才能指定windowing_clause。有些range子句定义的窗口范围只能在order_by_clause中指定 一个排序表达式。基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区Analytic_Clause之WINDOWING子句5.betweenand:用来指定窗口的起点和终点。第一个表达式(位于and之前)定义起点,第二个表达式(位于and之后)定义终点。若不使用between而仅指定一个终点,那么oracle认为它
10、是起点,终点默一认为当前行。6.unbounded preceding:指明窗口开始于分组的第一行。它只用于指定起点而不能用于指定终点7.unbounded following:指明窗口结束于分组的最后一行。它只用于指定终点而不能用于指定起点8.current row 用作起点:指定窗口开始于当前行或者当前值(依赖于是否分别指定row或者range)。在这种情况下终点不能为value_expre preceding。用作终点:指定窗口结束于当前行或者当前值(依赖于是否分别指定row或者range)。在这种情况下起点不能为value_exprfollowing。9.range或者row中的val
11、ue_expr preceding或者value_expr following:若value_expr FOLLOWING是起点,那么终点必须为:value_expr following。若value_expr PRECEDING是终点,那么起点必须是:value_expr precding。基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区PARTITION BY ORDER BY ROWSVsRANGE AnalyticFunction UNBOUNDED PRECEDINGFOLLOWINGCURRENT ROW分析函数语法各关键字基于案例学SQL优化讲师 梁敬彬DATAG
12、URU专业数据分析社区高级SQL之分析函数基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区分析函数学习详解基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解rows脚本:分析函数学习之_开窗分解1_rows.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解rows基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解rows基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解rows基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习
13、详解之开窗分解range脚本:分析函数学习之_开窗分解2_range.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解range脚本:分析函数学习之_开窗分解2_range.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之开窗分解keep脚本:分析函数学习之_开窗分解3_keep.sql聚合函数的特殊关键字KEEP聚合函数MIN,MAX等,当使用KEEP 时和DENSE_RANK FIRST/DENSE_RANK LAST一起使用,获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可接ove
14、r()分析函数部分。Min(col2)keep(dense_rank first order by col1)保留按col1排名第一的col2的最小值。Min(col2)keep(dense_rank first order by col1)over(partition by col3)按col3分组保留按col1排名各组第一的col2的最小值。基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区学习详解之order by 说明脚本:分析函数学习之_order by说明.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区分析函数案例分享基于案例学SQL优化讲师
15、梁敬彬DATAGURU专业数据分析社区案例1:排名论次脚本:分析函数案例1_排名论次.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例2:数据去重脚本:分析函数案例2_数据去重.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例3:占比应用脚本:分析函数案例3_占比应用.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例4:连续值判定脚本:分析函数案例4_连续值判定.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例4:连续值判定基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例
16、5:高频数获取脚本:分析函数案例5_高频数获取.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例6:计费规则计算脚本:分析函数案例6_计费规则计算.sql基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区案例6:计费规则计算基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区高级SQL之分析函数基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区本周课程总结思考基于案例学SQL优化讲师 梁敬彬DATAGURU专业数据分析社区炼数成金逆向收费式网络课程Dataguru(炼数成金)是专业数据分析网站,提供教育,媒体,内容,社区,出版,数据分析业务等服务。我们的课程采用新兴的互联网教育形式,独创地发展了逆向收费式网络培训课程模式。既继承传统教育重学习氛围,重竞争压力的特点,同时又发挥互联网的威力打破时空限制,把天南地北志同道合的朋友组织在一起交流学习,使到原先孤立的学习个体组合成有组织的探索力量。并且把原先动辄成千上万的学习成本,直线下降至百元范围,造福大众。我们的目标是:低成本传播高价值知识,构架中国第一的网上知识流转阵地。关于逆向收费式网络的详情,请看我们的培训网站 http:/DATAGURU专业数据分析网站FAQ时间38