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

从案例中感悟SQL等价改写优化思路之2 基于案例学SQL优化 第07讲 高级SQL之分析函数 特别之处 特别之处 VS 1.表记录10条，返回10条； 2. order by 的有无还还影响了结果 表记录10条，返回3条； 这里的order by就是排序的意思 语法概述 分析函数总体语法 Analytic-Function（Argument，Argument，…）?over（???????Query-Partition-Clause???????Order-by-Clause???????Windowing-Clause） SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND 2500 FOLLOWING) 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_SAMP VAR_POP VAR_SAMP VARIANCE 等） 总体语法第1部分 Analytic-Function 对于大部分人员来说，用的最多的是第1和第2类，不过第3类中的RATIO_TO_REPORT倒是用的频率还不小）。 总体语法第2部分 Analytic_Clause [?query_partition_clause?]?[ order_by_clause [ windows_clause ]?] Analytic_Clause之PARTITION子句 1. Partition by子句根据一个或多个value_expr将查询结果集分成若干组。若不使用该子句，那么函数将查询结果集的所有行当作一个组。 2. 在分析函数中使用query_partition_clause，应该使用语法图中上分支中的语法(不带圆括号）。model查询(位于model column clauses中）或被分隔的外部连接（位于outer_join_clause中）中使用该子句，应该使用语法图中下分支中的语法（带有圆括号）。 3.在同一个查询中可以使用多个分析函数，它们可以有相同或不同的partition by键值4)?若被查询的对象具有并行特性，并且分析函数中包含query_partition_clause，那么函数的计算也是并行的。5)?value_expr的有效值：包括常量，表列，非分析函数，函数表达式，或者前面这些元素的任意组合表达式。 Analytic_Clause之ORDER BY子句 1. 有ORDER BY的存在将添加一个默认的开窗子句！意味着从第一行到当前行； 2. 没有ORDER BY时,默认的窗口是全部的分区 ； 3. 在Order by 子句后可添加nulls last,如:order by comm desc nulls last 表示排序时忽略comm列为空的行; 4. Sibling关键字在此处是非法的，仅与层次查询有关。 Analytic_Clause之WINDOWING子句 1. 用于定义分析函数将在其上操作的行的集合，Windowing子句给出了一个定义变化或固定的数据窗口的方法，分析函数将对这些数据进行操作默认的窗口是一个固定的窗口，仅仅在一组的第一行开始，一直继续到当前行（即：range unbounded preceding），要使用窗口，必须使用ORDER BY子句。 2. row | range：这些关键字为每一行定义一个窗口，该窗口用于计算函数结果（物理或者逻辑的行的集合）。然后对窗口中的每一行应用分析函数。窗口在查询结果集或者分组中从上至下移动。 3. 根据2个标准可以建立窗口：数据值的范围（逻辑偏移量--range）或与当前行的行偏移量（物理单位--rows） 4. 只有指定order_by_clause后才能指定windowing_clause。有些range子句定义的窗口范围只能在order_by_clause