关于分析函数的介绍
🔹分析函数根据一组分级行源来计算聚合值。它们与聚合函数的不同之处在于，它们每个组返回多个行。行组称为窗口，由analytic_子句定义。对于每一行，定义一个滑动窗口的行。该窗口确定用于执行当前行的计算的行范围。窗口大小可以基于物理行数或逻辑间隔，比如时间范围，上一行下一行内容数据检索等等......
🔹分析函数是查询中执行的最后一组操作，ORDER BY子句除外。在处理分析函数之前，完成所有连接和所有WHERE、GROUP BY和HAVING子句。因此，分析函数只能出现在select list或ORDER BY子句中。
🔹解析函数有0到3个参数。参数可以是任何数字数据类型，也可以是非数字数据类型，可以隐式地转换为数字数据类型。Oracle确定数值优先级最高的参数，并隐式地将其余参数转换为该数据类型。返回类型也是该数据类型，除非个别函数另有说明。
关于OVER子句
🔹使用OVER analytic_clause表示函数对查询结果集进行操作。该子句是在FROM、WHERE、GROUP BY和HAVING子句之
后计算的。我们可以在select list或ORDER BY子句中使用此子句指定分析函数。要基于分析函数过滤查询的结果，需要将这些函数嵌套到父查询中，然后过滤嵌套子查询的结果。
🔹注意同志们，我们不能通过在analytic_clause的任何部分中指定任何分析函数来嵌套分析函数。但是，我们可以在子查询中指定一个分析函数，并在其上计算另一个分析函数。
🔹我们可以使用用户定义的分析函数以及内置的分析函数指定overanalytic_clause
关于分析函数相关注意事项
🔹使用PARTITIONBY子句根据一个或多个value_expr将查询结果集划分为组。如果省略此子句，则该函数将查询结果集的所有行视为单个组。
🔹要在分析函数中使用query_partition_子句，请使用语法的上部分支(没有括号)。要在模型查询(在model_column_子句中)或分区外部连接(在outer_join_子句中)中使用此子句，需要使用语法的较低分支(带括号)。
🔹我们可以在同一个查询中指定多个解析函数，每个解析函数具有相同或不同的键分区。
🔹如果被查询的对象具有parallel属性，如果使用query_partition_子句指定一个分析函数，那么函数计算也是并行的。
🔹value_expr的有效值是常量、列、非解析函数、函数表达式或涉及其中任何一个的表达式。
🔹使用order_by_子句指定如何在分区中对数据进行排序。对于所有分析函数，可以对分区中的值按多个键排序，每个键由value_expr定义，每个键由一个排序序列限定。
🔹在每个函数中，可以指定多个排序表达式。在使用对值进行排序的函数时，这样做尤其有用，因为第二个表达式可以解决第一个表达式的相同值之间的关系。
主角登场ROWS | RANGE
▪这些关键字为每一行定义一个窗口(一组物理或逻辑行)，用于计算函数结果。然后将该函数应用于窗口中的所有行。窗口在查询结果集或分区中上下移动。
▪ROWS以物理单元(行)指定窗口RANGE将窗口指定为逻辑偏移量。
关于BETWEEN...AND
🔺使用BETWEEN...AND…子句指定窗口的起点和终点。第一个表达式(在AND之前)定义起点，第二个表达式(在AND之后)定义终点。
🔺如果省略BETWEEN并仅指定一个端点，那么Oracle将其视为起点，而终点默认为当前行。
关于UNBOUNDED PRECEDING
🔺指定UNBOUNDED before以指示窗口从PARTITION的第一行开始。这是起点规范，不能用作终点规范。
关于UNBOUNDED FOLLOWI
🔺指定UNBOUNDED FOLLOWING以指示窗口在PARTITION的最后一行结束。这是端点规范，不能用作起点规范。
关于CURRENT ROW
🔺作为起点，CURRENT ROW指定窗口从当前行或值开始(这取决于我们是否分别指定了行或范围)。在这种情况下，端点不能在前。
🔺作为端点，CURRENT ROW指定窗口在当前行或值处结束(这取决于我们是否分别指定了行或范围)。在这种情况下，起点不能value_expr。
关于PRECEDING or valueexpr FOLLOWI
🔺如果后面的value_expr是起点，那么后面的终点必须是value_expr。
🔺如果前面的value_expr是终点，那么起点必须是前面的value_expr。
🔺如果指定ROWS，那么value_expr是一个物理偏移量。它必须是常量或表达式，并且必须计算为正数。如果value_expr是起点的一部分，那么它必须在终点之前计算一行。
🔺如果指定的是RANGE,value_expr是一个逻辑偏移量。它必须是一个常量或表达式，计算结果为正数值或区间文字。我们只能在order_by_子句中指定一个表达式。如果value_expr的值是一个数值，那么expr的顺序必须是一个数字或日期数据类型。如果value_expr的值是一个区间值，那么expr的顺序必须是日期数据类型。如果我们完全省略了windowing_clause，那么默认值是前一行和当前行之间的无边界范围。
阶段性总结
▪今天我们主要聊常见的分析函数类型，例如SUM()、MAX()、MIN()、COUNT()、AVG()等聚集函数以及LEAD()、LAG()行
比较函数等。
▪OVER 关键字，表示前面的函数是分析函数，不是普通的集合函数。
▪分析子句OVER关键字后面括号内的内容。
◾分析子句又由下面三部分组成：
🔘PARTITIONBY分组子句，表示分析函数的计算范围，不同的组互不相干。
🔘ORDER BY排序子句，表示分组后，组内的排序方式。
🔘ROWS/RANGE 窗口子句，是在分组(PARTITIONBY)后，组内的子分组（也称窗口），是分析函数的计算范围窗口，而不是PARTITION。
实战案例
结合实际的工作原理和分析函数概念知识，我们来具体看一下ROWS 与RANGE 窗口子句的用法，并分析结果返回数据：  ♥窗口子句必须和ORDER BY子句同时使用，且如果指定了ORDER BY子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW ♥截图中的案例，结果集中的defult_sum等于range_unbound_sum，如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE 窗口的计算。 ♥RANGE 是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如截图案例中range_sum（即range 1 preceing and 2 following）例的分析结果。 | |||
细节剖析 ♥id字段从1-9，那么当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5，那么最终的取值范围就是1 1 3 ♥当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3，那么最终的取值范围就是id为3 ♥当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33，那么最终取值范围为6 6 6 7 8 ♥ROWS 是物理窗口，即根据ORDER BY子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），如上例中rows_sum例结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果。 ♥当id=1（第一个1时）时，前一行没数，后二行分别是1和3，sum=1+1+3=5 ♥当id=3时，前一行id=1,后二行id都为6，则sum=1+3+6+6=16 | |||
ROWS综合分析结果展示  具体代码如下显示  🔹窗口范围为该分区的第一行和该分区的最后一行  🔹窗口范围为该分区的第一行和当前行  🔹窗口范围为该分区的第一行和当前行的前一行，统计的是第一行和当前行的前一行的薪资累计  🔹窗口范围为该分区的第一行和当前行的后一行，统计的是第一行和当前行的后一行的薪资累计  🔹窗口范围为当前行和该分区的最后一行，统计的是大于等于当前记录HIREDATE之后的所有薪资   🔹窗口范围只是当前行，所以与当前行薪资一样  🔹窗口范围为当前行和当前行的后一行，统计的是当前行和后一行的薪资累计  🔹窗口范围为当前行的前一行和该分区的最后一行，统计的是当前行的前一行和该分区最后一行的薪资累计  🔹窗口范围为当前行的前一行和当前行，统计的是当前行的前一行和当前行的薪资累计  🔹窗口范围为该分区的当前行前value_expr1到当前行前value_expr2的累计，本例为本行前2行和前1行的累计 🔹强调value_expr1 >= value_expr2  🔹窗口范围为该分区的本行前value_expr1到本行后value_expr2的累计，本例为本行前1行和后2行的累计  🔹窗口范围为本行后一行和本分区最后一行，统计的是本行后一行和本区最后一行的薪资累计  🔹窗口范围为该分区的本行后value_expr1行到本行后value_expr2的累计，本例为本行后1行到后2行的累计 🔹强调value_expr1 <= value_expr2  🔹窗口范围为该分区的第一行，结束行默认为本行，同sum_2  🔹窗口范围仅为当前行，所以与本行薪资一样，同sum_6  🔹窗口范围为该分区的第一行和本行前一行，统计的是本行前一行和本行的薪资累计  | |||
RANGE综合分析结果展示  具体代码如下显示  🔹窗口范围为该分区的第一行和该分区的最后一行，在非条件表达式中等同于ROWS  🔹窗口范围为该分区的第一行和本行，在非条件表达式中等同于ROWS  🔹窗口范围为该分区内小于本记录hiredate-365天的所有的薪资累计  🔹窗口范围为该分区内小于本记录hiredate+365天的所有的薪资累计  🔹窗口范围为本行和该分区的最后一行，统计的是大于等于本记录hiredate之后的所有薪资，在非条件表达式中等同于ROWS  🔹窗口范围只是本行，所以与本行薪资一样   🔹窗口范围为该分区内本记录起和小于本记录hiredate-365天的所有的薪资累计  🔹窗口范围为该分区内本记录起和小于本记录hiredate+365天的所有的薪资累计  🔹窗口范围为该分区内大于本记录hiredate-365天，并且截止到当前记录的所有的薪资累计  🔹窗口范围为该分区的本行current_value-value_expr1到本行current_value-value_expr2之间的累计 🔹强调value_expr1>=value_expr2  🔹窗口范围为该分区的本行current_value-value_expr1到本行current_value+value_expr2之间的累计  🔹窗口范围为该分区内大于本记录hiredate+365天的所有的薪资累计  🔹窗口范围为该分区的本行current_value+value_expr1到本行current_value+value_expr2的累计 强调value_expr1<=value_expr2  🔹窗口范围为该分区的第一行，结束行默认为本行，与sum_SAL_part_order，sum_2等同，在非条件表达式中等同于ROWS  🔹窗口范围为仅为当前行，所有与本行薪资一样，与sum_6一样，在非条件表达式的中等同于ROWS  🔹窗口范围为该分区内大于本记录hiredate-365天，并且截止到当前记录的所有的薪资累计，与sum_9等价   ♥例如我们要按照部门编号，统计当天及其前30天以及后30天的薪资累计，就可以通过如下脚本：  🔹统计当天及前30天的薪资累计   🔹统计当天及后30天的薪资累计  ♥划分子句并不是限制解析函数范围的唯一方法。当使用“前一个无界之间的行”子句时，将对行进行排序并定义一个窗口。  ♥在每一行中，返回当前行之前的最高工资和返回后的最高工资。ORDER BY子句在这里不是用于排序，而是用于指定窗口。  ♥使用RANGE的解析函数是deterministic的，对于这两种情况，返回相同的值，而对于每一行，返回唯一的值。行当前行恰好指向一行;范围当前行指向排序键等于当前行的所有行。当使用没有窗口子句的ORDER BY时，隐式窗口是支持窗口子句分析函数的前一行和当前行之间的无界范围。  | |||
记得注重细节 ♥查询使用前面的第2行窗口子句将当前行的工资值与前面两行工资值相加。  ♥窗口子句提供数据的滑动视图或锚定视图，这取决于传递给它的参数。仅使用ORDER BY子句的查询它从分区的第一行(或顶部)开始，以正在处理的当前行结束。那么，如何根据租用日期对分区进行排序，并使用范围窗口子句。  ♥上面的截图查询演示了RANGE子句的工作方式。查询的分区按HIREDATE排序。查询然后指定以下窗口子句: ♥range 90 preceding 给我们提供当前行工资值的摘要，以及HIREDATE值位于当前行的HIREDATE值之前90天内的所有前一行的工资值 ♥以下为关于间隔时间的分析函数用法：  | |||
今天为大家介绍了分析函数的range和rows的工作原理，注意事项，用法及其日常案例说明，通过本次学习我们可以通过range和rows取到同一组不同行的结果集，这是一个很方便的功能，在面对BI报表开发时候是非常有用的。
如果您觉得我们的文章不错，有所帮助，请在公众号留言或者加入600团队，后期陆续我们会推出OraclePL/SQL开发类型的相关文章及其线上视频分享。
总结
缘分，我在这里等你
