原创

Oracle分析函数(窗口函数)

分析函数由3个基本部分组成:分区子句、排序子句以及开窗子句(并不是所有分析函数都支持开窗子句)。
function1 (argument1, argument2,...argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])
排序子句通过一列或一个表达式的值来对数据分区中的行进行排序。在分析型SQL语句中。数据行在数据分区中的位置由排序子句控制。
开窗子句指定了分析函数进行运算的数据子集。这个窗口可以是动态,它有一个很恰当的名字-滑动窗口。可以使用窗口说明子句来指定滑动窗口的上下边界条件。窗口说明子句的语法如下:
[rows | range] between and

whereas
is [unbounded preceding | current row | n preceding | n following]
is [unbounded following | current row | n preceding | n following]
preceding指定了窗口的上边界条件,following或current row指定了窗口的下边界条件。
eg: 可以使用子句rows between unbounded preceding and current row,如果没有显示声明,这是默认的窗口。
计算本周前两周和后两周总共5周的时间窗口内sale列的最大值
max(sale) over ( partition by AA,BB order by week rows between 2 preceding and 2 following) max_5_weeks

分析函数不能嵌套,但可以包含在内嵌式图中,再在视图之外使用分析函数实现嵌套效果。

分析函数列表:

  • lag 访问一个分区或结果集中之前的一行(不支持开窗子句)
  • lead 访问一个分区或结果集中之后的一行(不支持开窗子句)
  • first_value 访问一个分区或结果集中第一行
  • last_value 访问一个分区或结果集中最后一行
  • nth_value 访问一个分区或结果集中的任意一行
  • rank 将数据行值按照排序后的顺序进行排名,在有并列值的情况下排名值将被跳过(不支持开窗子句)
  • dense_rank 将数据行值按照排序后的顺序进行排名,在有并列值的情况下排名值不会被跳过(不支持开窗子句)
  • row_number 对行进行排序并为每一行增加一个唯一编号。这是一个非确定性函数(不支持开窗子句)
  • ratio_to_report 计算报告中值的比例(不支持开窗子句)
  • percent_rank 将计算得到的排名值标准化为0到1之间的值(不支持开窗子句)
  • percentile_cont 取出与指定的排名百分比相匹配的值。是percent_rank函数的反函数(不支持开窗子句)
  • percentile_dist 取出与指定的排名百分比相匹配的值。采用谨慎分布模型(不支持开窗子句)
  • ntile 将数据行分组为单元(不支持开窗子句)
  • stddev 用来在一个数据分区(整个结果集)中的某些数据行上计算标准偏差(方差的平方根)
  • listagg 将来自不同行的列值转化为列表格式(不支持开窗子句)
    聚合函数可以在分析模式或传统的非分析模式下来执行运算。分析模式下的聚合函数可以不需要任何自联结就可以聚合不同层级的数据。

lag函数语法(lead函数与之类似):
lag (expression, offset, default) over (partition-cluase, order-by-clause)
default默认值表示当前一行不存在时,返回当前行的值或其他值
offset位移量表示访问往前第n行的数据。
eg:取出前一周的sale值 lag(sale, 1, sale) over(partition by AA,BB order by week) prior_week_sale

first_value和last_value函数通常用在计算排过序的结果集中的最大值和最小值。
first_value语法:
first_value (expression) over (partition-cluase order-by-clause windowing-cluase)
空值通过[respect nulls | ignore nulls]子句处理。respect nulls是默认值,表示第一行或最后一行是空值,就返回空值。ignore nulls则返回在窗口中第一个或倒数第一个不为空的行的值。
select first_value(cns.country_id ignore nulls) over (partition by cns.country_name order by cns.region_id rows between 10 preceding and 10 following) from hr.countries cns ;or
select first_value(cns.country_id) ignore nulls over (partition by cns.country_name order by cns.region_id rows between 10 preceding and 10 following) from hr.countries cns ;

nth_value函数(first_value和last_value函数的通用版本)语法:
nth_value (measure, n) [from first | from last] [respect nulls | ignore nulls]
over (partition-cluase order-by-clause windowing-cluase)
第一个参数是列名,第二个参数为窗口位移量。from first和respect nulls是默认值。

rank函数的语法:
rank() over (partition-cluase order-by-clause)

dense_rank可与first或last函数一起用。这两个函数都是聚合和分析函数,用来找出在按照特定规则排序后的集合中,排在第一位或最后一位的一序列值。当需要有序分组中最后一行的第一个值,而所需的值又不是排序键时,使用first和last函数,不需要再联结表本身就可以得到正确的值。语法如下:
aggregate syntax:
aggregate function keep
(dense_rank [first | last] order by expression [desc | asc] nulls [first | last])
analytic syntax:
aggregate function keep
(dense_rank [first | last] order by expression [desc | asc] nulls [first | last])
over (partition-cluase)
aggregate function包括min、max、sum、avg、count、variance、stddev
eg:

select max(sql_id) keep(dense_rank last order by last_active_time) sql_id,
               max(child_number) keep(dense_rank last order by last_active_time) child_number
          from v$sql
         where upper(sql_text) like '%T%';

在这个sql中,想要返回的是v$sql视图中最近执行的满足条件的sql语句的sql_id和child_number。

row_number函数语法:
row_number() over (partition-cluase order-by-clause)
非确定性函数。如果分区中的A、B两行具有相同的值,row_number函数的值是不确定的,可能为A、B分别返回3、4,也可能返回4、3。

ratio_to_report函数计算数据分区中某个值与和值的比率。可用于计算报表中某个值占总值的百分比。
ratio_to_report语法:
ratio_to_report (expression) over (partition-cluase)
不支持order-by-clause

percent_rank函数以0到1之间的分数形式返回某个值在数据分区中的排名,计算公式为(rank-1)/(N-1),N为数据行数。
percent_rank函数语法:
percent_rank() over (partition-cluase order-by-clause)

percentile_cont函数可以计算内插值。接收一个0到1之间的几率值并返回声明了排序的percent_rank函数计算值相等的内插值百分比。percentile_cont函数获取与参数的percent_rank相匹配的列值。例如:percentile_cont(0.25)子句获取percent_rank为0.25的值(假设这两个函数的排序顺序相匹配)。
percentile_cont函数语法:
percentile_cont(expr) within group (sort-cluase) over (partition-cluase)
排列顺序通过within group (sort-cluase)子句来定义。如果没有值来精确匹配expr,percentile_cont函数将会使用最近的值来算出一个插值(取离expr前后最近的两个列值的平均值---因为这个函数假设值是连续分布的)。

percentile_dist函数在功能上类似percentile_cont函数,只是percentile_dist函数使用离散分布模型,而percentile_cont函数使用连续分布模型。
当没有值与指定的percent_rank精确匹配时,percentile_cont会计算两个离得近的值的平均值。在升序排列的情况下,percentile_dist只取比所传递的参数percent_rank值更大的值。在降序排列时,percentile_dist只取比所传递的参数percent_rank值更小的值。
percentile_dist函数语法:
percentile_dist(expr) within group (sort-cluase) over (partition-cluase)

ntile函数对一个数据分区中的有序结果集进行划分,将其分组为各个桶,并为每一个小组分配一个唯一的组编号。在统计学术语中,ntile函数用于创建等宽直方图信息。桶的数目将为做参数传递给该分析函数。例如,ntile(100)将会将数据行分组为100个桶,并为每个桶分配唯一编号。
ntile函数语法:
ntile(n) over (partition-cluase order-by-clause)

stddev函数可以用来在一个数据分区(整个结果集)中的某些数据行上计算标准偏差(方差的平方根)。stddev_samp计算累计采样标准方差,stddev_pop计算总体标准偏差。
stddev函数语法:
stddev(expression) over (partition-cluase order-by-clause windowing-cluase)

listagg函数将来自多个行中的列值转化为列表格式。
listagg函数语法:
listagg(string, separator) within group (order-by-clause) over (partition-cluase)
string是要进行连接的字符串或列名。separator是分隔符。

分析函数也支持并行。

正文到此结束
该篇文章的评论功能已被站长关闭