分析函数

分析函数提供一种简单高效的数据处理方式，采用SQL语句实现了复杂的数值统计功能。

  1. analytic_function语法

analytic_function ::=

分析函数可以取0～3个参数，参数可以是取任何数字类型或者能够隐式转换为数据类型的非数字类型。 函数的返回类型也为数字类型，除非函数有另外的说明。

analytic_clause语法

analytic_clause ::=

Analytic_clause用来指明函数操作的是一个查询结果集，即分析函数是在from、where、group by和having子句之后才开始进行计算的，因此在选择列或者order by子句中可以使用分析函数。

query_partition_clause语法

query_partition_clause ::=

Partition by子句根据一个或多个value_expr将查询结果集分成若干个组，如果不使用该子句，那么分析函数将查询结果集的所有行当成一个组。

在同一查询中可以使用多个分析函数，它们可以有相同或不同的Partition by键值。

value_expr的有效值包括常量、表列、非分析函数、函数表达式，或者前面所述这些元素的任意组合表达式。

示例：

将用户表中年龄相同的人分为一组，按照ID排序，统计ID的累计求和:

SELECT *, SUM(ID) OVER (PARTITION BY AGE ORDER BY ID) FROM T_USER;

查询结果为:

ID | AGE | SUM
----+-----+----
1 | 20 | 1
2 | 20 | 3
3 | 30 | 3
4 | 30 | 7
5 | 30 | 12
6 | 40 | 6
7 | 40 | 13
8 | 40 | 21

order_by_clause语法

order_by_clause ::=

Order by子句用以指定分组中数据的排序形式，可以使用多个键值对分组进行排序。分析函数的order by子句必须是一个表达式，sibing，position，c_alias关键字在此处是非法的，除此之外order by子句的用法和整个查询或子查询的用法相同。

asc | desc 指定排序的顺序（升序或降序），asc是默认值。

nulls first | nulls last 指定若返回行包含空值，该值应该出现在排序序列的开始还是末尾。

升序排序的默认值是nulls last，降序排序的默认值是nulls first。

windowing_clause语法

windowing_clause ::=

windowing_between ::=

windowing_bound ::=

允许使用windowing_clause的分析函数有：AVG， COUNT，FIRST VALUE，LAST VALUE，MAX，MIN，STDDEV，SUM，VARIANCE。

ROWS | RANGE关键字为每一行定义了一个窗口，该窗口用于计算分析函数的结果，窗口能够在查询结果集或分组中从上到下移动。

ROWS用于指定窗口以行为基本单位，RANGE用于指定以逻辑偏移量来构成窗口。

BETWEEN…AND子句用于指定窗口的起点和终点，第一个表达式位于and之前，用于定义起点，第二个表达式位于and之后，用于定义终点。如果不用BETWEEN而仅指定一定终点，则认为这个定义是起点，而终点为当前行。

UNBOUNDED PRECEDING用于指定窗口开始于分组的第一行，只能用于指定起点而不能用于指定终点。

UNBOUNDED FOLLOWING用于指定窗口结束于分组的最后一行，只能用于指定终点而不能用于指定起点。

CURRENT ROW用作起点时，用于指定窗口开始于当前行，此时终点不能为value_expr PRECEDING。

CURRENT ROW用作终点时，用于指定窗口结束于当前行，此时起点不能为value_exprFOLLOWING。

示例：

将用户表中年龄相同的人分为一组，按照ID排序，统计ID的前一行到后一行的累计求和:

SELECT *, SUM(ID) OVER (PARTITION BY AGE ORDER BY ID ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING) FROM T_USER;

查询结果为:

ID | AGE | SUM
----+-----+-----
1 | 20 | 3
2 | 20 | 3
3 | 30 | 7
4 | 30 | 12
5 | 30 | 9
6 | 40 | 13
7 | 40 | 21
8 | 40 | 15

将用户表中年龄相同的人分为一组，按照ID排序，统计ID的当前行到最后一行的累计求和:

SELECT *, SUM(ID) OVER (PARTITION BY AGE ORDER BY ID RANGE BETWEEN
CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T_USER;

查询结果为:

ID | AGE | SUM
----+-----+----
1 | 20 | 3
2 | 20 | 2
3 | 30 | 12
4 | 30 | 9
5 | 30 | 5
6 | 40 | 21
7 | 40 | 15
8 | 40 | 8