14从案例中感悟SQL等价改写优化思路之2.pdf

《14从案例中感悟SQL等价改写优化思路之2.pdf》由会员分享，可在线阅读，更多相关《14从案例中感悟SQL等价改写优化思路之2.pdf（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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