我的书签
添加书签
移除书签简介
分析函数（某些数据库下也叫做窗口函数）与聚集函数类似，计算总是基于一组行的集合，不同的是，聚集函数一组只能返回一行，而窗口函数每组可以返回多行，组内每一行都是基于窗口的逻辑计算的结果。分析函数可以显著优化需要self-join的查询。
功能支持
目前OceanBase支持以下分析函数：
SUM
MIN
MAX
COUNT
AVG
GROUP_CONCAT（ob特有）
ROW_NUMBER
RANK
DENSE_RANK
PERCENT_RANK
CUME_DIST
FIRST_VALUE
LAST_VALUE
NTH_VALUE
NTILE
LEAD
LAG
- RATIO_TO_REPORT
- STDDEV
- VARIANCE
- STDDEV_SAMP
- STDDEV_POP
- LISTAGG
其中oracle支持ob还没有的窗口函数还有：
- CORR
- COVAR_POP
- COVAR_SAMP
- MEDIAN
- PERCENTILE_CONT
- PERCENTILE_DISC
- VAR_POP
- VAR_SAMP
- REGR_(Linear Regression) Functions
窗口函数语法
“窗口”也称为FRAME，OceanBase同时支持ROWS与RANGE两种FRAME语义，前者是基于物理行偏移的窗口，后者则是基于逻辑值偏移的窗口。
analytic_function的语法为analytic_function([ arguments ]) OVER (analytic_clause)
analytic_clause的语法为[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
query_partition_clause的语法为PARTITION BY { expr[, expr ]… | ( expr[, expr ]… ) }
order_by_clause的语法为ORDER [ SIBLINGS ] BY{ expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ][ NULLS FIRST | NULLS LAST ]]…
窗口语法
windowing_clause的语法为
{ ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW| value_expr PRECEDING}}
window_clause的语法与oracle的语法兼容。
分析函数解释
以下逐个介绍分析函数的语法支持，功能及和oracle数据库的异同：
sum/min/max/count/avg
sum/min/max/count/avg的语法为AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
sum的语法为SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
min的语法为MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
max的语法为MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
count的语法为COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
以上带聚集函数的窗口函数，sum返回expr的和，min/max返回expr的最小值/最大值，count返回是窗口中查询的行数，avg返回的是expr的平均值。
对于count函数，从不返回null，如果指定了expr，即返回expr不为null的统计个数，如果指定count(*)返回所有行的统计数目。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, sum(salary) over(partition by job_id) totol_s, min(salary) over(partition by job_id) min_s, max(salary) over(partition by job_id) max_s, count(*) over(partition by job_id) count_s from exployees;+------------+---------+-------+-------+---------+| LAST_NAME | TOTOL_S | MIN_S | MAX_S | COUNT_S |+------------+---------+-------+-------+---------+| jim | 2000 | 2000 | 2000 | 1 || mike | 36000 | 11000 | 13000 | 3 || lily | 36000 | 11000 | 13000 | 3 || tom | 36000 | 11000 | 13000 | 3 |+------------+---------+-------+-------+---------+
以上五个函数，ob还没有完全做到和oracle语法上兼容。
对于distinct关键字，sum/count/avg，如果指定了distinct关键字，就只能指定analytic_clause中的query_partition_clause了。order_by_clause和windowing_clause是不被允许出现的。这点ob还没有支持，ob仍然可以在这些函数的distinct的同时写order_by_clause和windowing_clause。
现在order_by_clause中的nulls first和nulls last，现在还不支持。
analytic_clause的支持参照之前分析窗口语法分析。
注意方差函数在聚集函数中的用法和窗口函数略有不同，详细见聚集函数中的方差函数语法。
nth_value/first_value/last_value
nth_value的语法为NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)
从名称可以看出，nth_value是第几个值的意思，方向由[ FROM { FIRST | LAST } ]确定，默认为from first，含有是否忽略null值的标志。其窗口为统一的analytic_clause。这里n应该是正数，如果n是null，函数将返回错误，如果n大于窗口内所有的行数，此函数将返回null。
first_value/last_value的语法为FIRST_VALUE { (expr) [ {RESPECT | IGNORE} NULLS ] | (expr [ {RESPECT | IGNORE} NULLS ])} OVER (analytic_clause)
从名称上可以看出，first_value和last_value是从第一个开始计数或者是从最后一个开始计数。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, first_value(salary) over(partition by job_id) totol_s, last_value(salary) over(partition by job_id) min_s, max(salary) over(partition by job_id) max_s from exployees;+------------+---------+-------+-------+| LAST_NAME | TOTOL_S | MIN_S | MAX_S |+------------+---------+-------+-------+| jim | 2000 | 2000 | 2000 || mike | 12000 | 11000 | 13000 || lily | 12000 | 11000 | 13000 || tom | 12000 | 11000 | 13000 |+------------+---------+-------+-------+
以上三个函数，和oracle是兼容的，这三个函数没有对应的聚集函数。
lead/lag
lag和lead含义为可以在一次查询中取出当前行的同一个字段的前面第N行的数据，这种操作可以使用相同表的自连接来实现，但lag/lead窗口函数有更高的效率。语法如下：
lag/lead { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )} OVER ([ query_partition_clause ] order_by_clause)
其中，value_expr是要做比对的字段，offset是value_expr的偏移量，default参数的默认值为空值null，即如果在lag/lead没有显示的设置default值的情况下，返回值为空值null。例如：对lag来说，当前行为4，offset值为6，这时候所要找的数据就是第-2行，不存在此行即返回default的值。
[ { RESPECT | IGNORE } NULLS ]的语法为是否考虑null值，默认为RESPECT，考虑null值。
注意lead/lag两个函数后必须跟order_by_clause，数据应该在一个列上排序之后才能有前多少行后多少行的概念。query_partition_clause是可选的，如果没有query_partition_clause的话，就是全局的数据。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, lead(salary) over(order by salary) lead, lag(salary) over(order by salary) lag from exployees;+------------+-------+-------+| LAST_NAME | LEAD | LAG |+------------+-------+-------+| jim | 11000 | NULL || tom | 12000 | 2000 || mike | 13000 | 11000 || lily | NULL | 12000 |+------------+-------+-------+
以上两个函数，和oracle是兼容的，注意value_expr和lag的结果都是可以对null做忽略或者不忽略的。lead/lag两个函数没有对应的聚集函数。
stddev/variance/stddev_samp/stddev_pop
variance的语法为VARIANCE([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]返回的是expr的方差，expr可能是数值类型或者可以转换成数值类型的类型，方差的类型和输入的值的类型相同。
stddev的语法为STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]，它的语法和variance是一样的，stddev的含义返回的是expr的标准差，参数类型方面和variance的相同。
stddev_samp的语法为STDDEV_SAMP(expr) [ OVER (analytic_clause) ]
stddev_pop的语法为STDDEV_POP(expr) [ OVER (analytic_clause) ]
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, stddev(salary) over(order by salary) std, variance(salary) over(order by salary) var, stddev_pop(salary) over() std_pop, stddev_samp(salary) over() from exployees;+------------+-------------------------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+| LAST_NAME | STD | VAR | STD_POP | STDDEV_SAMP(SALARY)OVER() |+------------+-------------------------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+| jim | 0 | 0 | 4387.482193696061030203194153708154780438 | 5066.228051190221210556248068679881679227 || tom | 6363.961030678927719607599258943641353564 | 40500000 | 4387.482193696061030203194153708154780438 | 5066.228051190221210556248068679881679227 || mike | 5507.570547286102020605808770872073470916 | 30333333.333333333333333333333333333333 | 4387.482193696061030203194153708154780438 | 5066.228051190221210556248068679881679227 || lily | 5066.228051190221210556248068679881679227 | 25666666.666666666666666666666666666667 | 4387.482193696061030203194153708154780438 | 5066.228051190221210556248068679881679227 |+------------+-------------------------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+
以上4个函数，ob还没有完全做到和oracle语法上兼容。
对于distinct关键字，variance/stddev函数，如果指定了distinct关键字，就只能指定analytic_clause中的query_partition_clause了。order_by_clause和windowing_clause是不被允许出现的。这点ob还没有支持，ob仍然可以在这些函数的distinct的同时写order_by_clause和windowing_clause。stddev_pop/stddev_samp函数，则不被允许指定distinct/all关键字。这点ob也还没有支持。
现在order_by_clause中的nulls first和nulls last，现在还不支持。
analytic_clause的支持参照之前分析窗口语法分析。
注意方差函数在聚集函数中的用法和窗口函数略有不同，详细见聚集函数中的方差函数语法。
ntile
ntile的语法为NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)，ntile函数将分区中已经排序的行划分为大小尽可能相同的指定数量的分组，它的语法中query_partition_clause可选。并返回给每行组号。expr如果是null也会返回null。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, ntile(10) over(partition by job_id order by salary) ntl from exployees;+------------+------+| LAST_NAME | NTL |+------------+------+| jim | 1 || tom | 1 || mike | 2 || lily | 3 |+------------+------+
ob中的ntile函数与oracle兼容，注意ntile的窗口中是有order_by_clause的。没有ntile相应的聚集函数。
row_number
row_number的语法为ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)，它的语法中query_partition_clause可选，row_number在order by之后的expr进行排序的情况下每行一个编号，前提是排序。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, row_number() over(partition by job_id order by salary) ntl from exployees;+------------+------+| LAST_NAME | NTL |+------------+------+| jim | 1 || tom | 1 || mike | 2 || lily | 3 |+------------+------+
ob中的row_number函数与oracle兼容，注意row_number的窗口中是有order_by_clause的。没有row_number相应的聚集函数。
rank/dense_rank/percent_rank
rank的语法为RANK( ) OVER ([ query_partition_clause ] order_by_clause)，作为一个分析函数，rank计算每一行数据在某列上的排序，该列由order_by_clause中的列决定。如下按照salary排序可以看出员工的收入排名。
dense_rank的语法为DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)，它的语义基本和rank函数相同，但是rank的排序中间会有‘跳过’，但是dense_rank中不会有。
percent_rank的语法为PERCENT_RANK( ) OVER ([ query_partition_clause ] order_by_clause)，它的语义基本和rank函数相同，但是percent_rank排序的是百分比，计算的是给定行的百分比。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, rank() over(partition by job_id order by salary) rank, dense_rank() over(partition by job_id order by salary) dense_rank, percent_rank() over(partition by job_id order by salary) percent_rank from exployees;+------------+------+------------+--------------+| LAST_NAME | RANK | DENSE_RANK | PERCENT_RANK |+------------+------+------------+--------------+| jim | 1 | 1 | 0 || tom | 1 | 1 | 0 || mike | 2 | 2 | .5 || lily | 3 | 3 | 1 |+------------+------+------------+--------------+
以上三个函数：ob中的rank函数与oracle兼容，注意rank的窗口中是有order_by_clause的。注意rank在聚集函数中的用法和窗口函数中略有不同，详细请见聚集函数中的rank函数语法；ob中的dense_rank函数与oracle兼容，注意dense_rank的窗口中是有order_by_clause的。注意dense_rank在聚集函数中的用法和窗口函数中略有不同，详细请见聚集函数中的dense_rank函数语法；ob中的percent_rank函数和oracle兼容，注意percent_rank的窗口中是有order_by_clause的，注意percent_rank在聚集函数中的用法和窗口函数略有不同，详细见聚集函数中的percent_rank函数语法。
cume_dist
cume_dist的语法为CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)，cume_dist计算一个值的分布，返回值为>0 <=1，作为一个分析函数，cume_dist在生序情况下计算比当前行的特定列小的数据的占比。比如如下例子是，按job_id分组并在薪水排序的情况下，每行数据在窗口内的排序列上的占比。
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, cume_dist() over(partition by job_id order by salary) rank from exployees;+------------+-------------------------------------------+| LAST_NAME | RANK |+------------+-------------------------------------------+| jim | 1 || tom | .3333333333333333333333333333333333333333 || mike | .6666666666666666666666666666666666666667 || lily | 1 |+------------+-------------------------------------------+
ob中的cume_dist与oracle兼容。注意cume_dist函数的语法中，窗口中可以含有query_partition_clause也可以含有order_by_clause。注意cume_dist在聚集函数中的用法和窗口函数中略有不同，详细请见聚集函数中的cume_dist函数语法。
ratio_to_report
ratio_to_report的语法为RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])，它计算expr的值和窗口内这个值集合sum的比率，如果expr为null，ratio_to_report的值一样为null。
值集合的sum为query_partition_clause所决定，如果省略此clause，ratio_to_report在expr所有值集合上计算sum。expr不能是ratio_to_report或者其他分析函数的嵌套，但可以是类似与聚集函数中expr的内部函数嵌套表达式。下面举例说明ratio_to_report的功能，下面的例子计算的是每个公司职员在全公司中的工资占比：
create table exployees(last_name char(10), salary decimal, job_id char(32));insert into exployees values('jim', 2000, 'cleaner');insert into exployees values('mike', 12000, 'engineering');insert into exployees values('lily', 13000, 'engineering');insert into exployees values('tom', 11000, 'engineering');OceanBase(TEST@TEST)>select last_name, salary, ratio_to_report(salary) over() as salary_ratio from exployees where job_id = 'engineering';+------------+--------+-------------------------------------------+| LAST_NAME | SALARY | SALARY_RATIO |+------------+--------+-------------------------------------------+| mike | 12000 | .3333333333333333333333333333333333333333 || lily | 13000 | .3611111111111111111111111111111111111111 || tom | 11000 | .3055555555555555555555555555555555555556 |+------------+--------+-------------------------------------------+
ob中的ratio_to_report与oracle兼容。注意ratio_to_report的语法中，窗口中不能含有order_by_clause。
需要根据oracle每个窗口函数的语法，包括每个窗口函数是否会出现windowing_clause，ordering_clause，query_partition_exprs，两个纬度首先测试一下正确性和功能。
执行计划
OceanBase分析函数的相应算子是： WINDOW FUNCTION，该算子依赖下层算子的输出按分区 + 排序键有序，所以在必要时会分配排序算子
注：
mysql> explain select row_number() over (partition by table_id order by partition_id) from __all_root_table;+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query Plan |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ====================================================|ID|OPERATOR |NAME |EST. ROWS|COST|----------------------------------------------------|0 |WINDOW FUNCTION| |1000 |2240||1 | SORT | |1000 |2049||2 | TABLE SCAN |__all_root_table|1000 |499 |====================================================Outputs & filters:-------------------------------------0 - output([T_WIN_FUN_ROW_NUMBER()]), filter(nil),win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by([__all_root_table.table_id]), order_by([__all_root_table.partition_id, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)1 - output([__all_root_table.table_id], [__all_root_table.partition_id]), filter(nil), sort_keys([__all_root_table.table_id, ASC], [__all_root_table.partition_id, ASC])2 - output([__all_root_table.table_id], [__all_root_table.partition_id]), filter(nil),access([__all_root_table.table_id], [__all_root_table.partition_id]), partitions(p0)
排序消除
如果优化阶段已经知道下层输出是有序的，则不会分配排序算子（(tenant_id，table_id)为__all_root_table主键前缀）
explain select row_number() over (partition by tenant_id, table_id order by partition_id) from __all_root_table;+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Query Plan |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ====================================================|ID|OPERATOR |NAME |EST. ROWS|COST|----------------------------------------------------|0 |WINDOW FUNCTION| |1000 |690 ||1 | TABLE SCAN |__all_root_table|1000 |499 |====================================================Outputs & filters:-------------------------------------0 - output([T_WIN_FUN_ROW_NUMBER()]), filter(nil),win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by([__all_root_table.tenant_id], [__all_root_table.table_id]), order_by([__all_root_table.partition_id, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)1 - output([__all_root_table.tenant_id], [__all_root_table.table_id], [__all_root_table.partition_id]), filter(nil),access([__all_root_table.tenant_id], [__all_root_table.table_id], [__all_root_table.partition_id]), partitions(p0)|
计算合并
当同时存在多个分析函数时，如果存在可以共享一个序的子集，子集内所有函数会在同一个算子里计算而不是拆到多个算子，这样可以有效减少内存开销和边界判断。
如下面的例子所示，row_number()与rank()两个分析函数的计算可以共用同一个排序的输出，因此，只会分配一个sort节点：
mysql> explain select row_number() over (partition by table_id order by partition_id), rank() over (partition by table_id, partition_id order by svr_ip) from __all_root_table;| ====================================================|ID|OPERATOR |NAME |EST. ROWS|COST|----------------------------------------------------|0 |WINDOW FUNCTION| |1000 |3017||1 | SORT | |1000 |2826||2 | TABLE SCAN |__all_root_table|1000 |499 |====================================================Outputs & filters:-------------------------------------0 - output([T_WIN_FUN_ROW_NUMBER()], [T_WIN_FUN_RANK()]), filter(nil),win_expr(T_WIN_FUN_RANK()), partition_by([__all_root_table.table_id], [__all_root_table.partition_id]), order_by([__all_root_table.svr_ip, ASC]), window_type(RANGE), uppe r(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by([__all_root_table.table_id]), order_by([__all_root_table.partition_id, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING ), lower(UNBOUNDED FOLLOWING)1 - output([__all_root_table.table_id], [__all_root_table.partition_id], [__all_root_table.svr_ip]), filter(nil), sort_keys([__all_root_table.table_id, ASC], [__all_root_tabl e.partition_id, ASC], [__all_root_table.svr_ip, ASC])2 - output([__all_root_table.table_id], [__all_root_table.partition_id], [__all_root_table.svr_ip]), filter(nil),access([__all_root_table.table_id], [__all_root_table.partition_id], [__all_root_table.svr_ip]), partitions(p0)
落盘
分析函数的计算也支持执行期的落盘操作，其逻辑与排序操作符的落盘逻辑一致。
窗口函数已知问题汇总
以下问题是窗口函数已知的问题汇总，测试如果测到，如有必要在原issue中补充说明，不必再提新的issue：
- https://work.aone.alibaba-inc.com/issue/22521949
部分窗口函数用了distinct关键字就不能在用orderby_clause和window_clause。
部分窗口函数执行可以计算增量窗口来提升性能，而不是每次都是partition by后的窗口内所有的行参与计算。
