分析函数，也称为窗口函数，通常被认为仅对数据仓库SQL有用。使用分析函数的查询，基于对数据行的分组来计算总量值。与一般的聚合函数提供的分组统计类似，但主要不同点就是，分析函数为每个分组返回多行数据。不同于每个分组只得到一行数据，分析函数可以同时得到所有行的详细数据。
窗口是通过分析子句定义的，每一行限定在一个滑动窗口中。这些滑动窗口确定了用来计算当前行的数据行范围。每个窗口的大小可以由一定物理数量的数据行或者某种逻辑间隔（例如时间间隔）确定。这些函数能够比通常的聚集函数多做很多事情，例如可以跨行引用值，生成多层聚集以及对子集数据排序进行更细粒度的控制。
尽管传的SQL语句也可以用来实现在线分析查询，但通常这些语句很复杂，并且相对来说性能较差。而使用分析函数，可以避免对同一对象的重复访问，节约时间和资源。因为既可以返回明细数据，又可以返回分组后的值，分板函数可以很方便地提供累计值，滑动平均值，中心值以及汇总报表。
除了order by子句之外，分析函数在查询中是最后执行的运算。在执行分析函数之前，所有其他子句，包括where、group by以及having子句都己执行完成。因此，分析函数只能就用于选择列表或order by子句中。分析函数通常被认为适合在数据仓库或大的报表应用中使用，但是，在了解了其能力和灵活性之后，就能在任何可用的地方使用它们。
分析函数剖析
分析函数有３个基本组成部分：
１. 分区子句
２. 排序子句
３. 开窗子句
function1 (argument1,argument2,..argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])
function1 是所调用的接收０个或多个参数的分析函数。分区子句按照分区列的值对数据行进行分组。所有分区列的值相同的数据行被组合为一个数据分区。
准备测试数据
--非标准化的sales_fact表
drop table sales_fact;
create table sales_fact AS
select c.country_name country,c.country_subRegion region,p.prod_name product,
t.calendar_year year,t.calendar_week_number week,
sum(s.amount_sold) sale,
sum(s.amount_sold*
(
case
when mod(rownum,10)=0 then 1.4
when mod(rownum,5)=0 then 0.6
when mod(rownum,2)=0 then 0.9
when mod(rownum,2)=0 then 1.2
else 1
end
)
) receipts
from sh.sales s,sh.times t,sh.customers cu,sh.countries c, sh.products p
where s.time_id=t.time_id
and s.prod_id=p.prod_id
and s.cust_id=cu.cust_id
and cu.country_id=c.country_id
group by c.country_name,c.country_subregion,p.prod_name,t.calendar_year,t.calendar_week_number;
select * from sales_fact;
执行结果
SQL> --sale列的动态求和
SQL> select year,
2 week,
3 sale,
4 sum(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between unbounded preceding and current row
8 ) running_sum_ytd
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
1998 1 58.15 58.15
1998 2 29.39 87.54
1998 3 29.49 117.03
1998 4 29.49 146.52
1998 5 29.8 176.32
1998 6 58.78 235.1
1998 9 58.78 293.88
1998 10 117.76 411.64
1998 12 59.6 471.24
1998 14 58.78 530.02
1998 15 58.78 588.8
上面的例子,计算了sale列从年初开始按照产品、国家、地区、年份相组合的动态求和值。partition by sf.product, sf.country, sf.region, sf.year声明了分区列，在数据分区中，使用order by sf.week子句来指定数据行按照week列进行排序。计算sales列的动态总和值，因此分析函数必须在从年初到当前周的时间窗口中计算。这通过开窗子句rows between unbounded preceding and current row实现。sum(sale)函数在该时间窗口的数据行中计算sale列的和值。因为数据行是按照week列排序的，求和函数就会在从年初到当前周的数据集上运算。
running_sum_ytd列是分析模式下求和函数的输出。列值在新的一年时进行了重置，因为年份也是分区列，所以每年都会有一个新的分区。当开始新的一年时，窗口就滑动到下一个数据分区中，求和函数从week 1开始聚合。使用传统的SQL语句来实现这个功能将导致需要使用多次自联结或成本很高的列级子查询。
排序子句通过一列或一个表达式的值来对数据分区中的行进行排序。在分析型SQL语句中，数据行在数据分区中的位置是很重要的，这是由排序子句控制的。在数据分区内的数据行按照排序列的值排序。因为在分区子句中按照分区列的值排序，实际上最终得到的是按照分区子句和排序子句中指定的列进行排序后的结果。
与sql语句order by类似，排序可以按照升序也可以按照降序。使用nulls first或nulls last子句可以将空值放到数据分区的最上面或最下面。
开窗子句指定了分析函数进行运算的数据子集。这个窗口可以是动态，它有一个很恰当的名字---滑动窗口。可以使用窗口说明子句来指定滑动窗口的上下边界条件。
窗口说明子句的语法：
[rows | range] between <start expr> and [end expr]
whereas
<start expr> is [unbounded preceding | current row | n preceding | n following]
<end expr> is [unbounded following | current row | n preceding | n following]
关键字preceding指定了窗口的上边界条件，following或current row子句指定了窗口的下边界条件。滑动窗口提供了简便的复杂矩阵计算能力。例如，可以使用子句rows between unbounded preceding and current row对sales列动态求和。在这个例子中，窗口最上面的一行是当前分区中的第一行而窗口最下面的一行是当前数据行。
并不是所有分析函数都支持开窗语句。
分析函数不能进行嵌套。但可以将所包含的SQL语句放在内嵌视图中，然后在视图之外使用分析函数实现嵌套效果。分析函数也可以用在多层嵌套内嵌视图中。
分析函数列表
分析函数列表
聚合函数
聚合函数可以在分析模式或传统的非分析模式下来执行运算。非分析模式下的聚合函数将结果集削减少为较少的数据行。然而，在分析模式下，聚合函数并不减少输出结果行数。聚合函数能够在一行中同时取聚合的和非聚合的列。分析模式的聚合函数可以不需要任何自联结就可以聚合不同层级的数据。
分析函数在写需要在不同层级上对数据进行聚合的复杂查询报表时是非常有用的。考虑某个产品的消费者市场分析报告，调查最受欢迎的广告主管，这就需要在多个层次例如年龄、性别、商店、行政区、地区以及国家上对销售数据进行聚合。分析模式下的聚合函数可以轻易地且很有效地实现这个市场分析报告。与非分析的实现方法相对，分析函数可以很显著地提高SQL语句的清晰度和性能。
跨越整个分区的聚合函数
SQL> --sale列的最大值
SQL> select year,
2 week,
3 sale,
4 max(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between unbounded preceding and unbounded following
8 ) max_sale
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE MAX_SALE
---------- ---------- ---------- ----------
1998 1 58.15 172.56
1998 2 29.39 172.56
1998 3 29.49 172.56
1998 4 29.49 172.56
1998 5 29.8 172.56
1998 6 58.78 172.56
1998 9 58.78 172.56
1998 10 117.76 172.56
1998 12 59.6 172.56
1998 14 58.78 172.56
1998 15 58.78 172.56
在一些情况下，可能需要在某个给定分区的所有数据行上应用分析函数。例如在一整年中计算sale列的最大值将需要包含数据分区中每一行数据的窗口。rows between unbounded preceding and unbounded following提定将max函数应用于数据分区的每一行上。unbounded following子句指定窗口大小为包含数据分区中的所有行。
细粒度窗口声明
SQL> --５周时间跨度窗口sale列的最大值
SQL> select year,
2 week,
3 sale,
4 max(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between 2 preceding and 2 following
8 ) max_weeks_5
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE MAX_WEEKS_5
---------- ---------- ---------- -----------
1998 1 58.15 58.15
1998 2 29.39 58.15
1998 3 29.49 58.15
1998 4 29.49 58.78
1998 5 29.8 58.78
1998 6 58.78 117.76
1998 9 58.78 117.76
1998 10 117.76 117.76
1998 12 59.6 117.76
1998 14 58.78 117.76
1998 15 58.78 117.56
YEAR WEEK SALE MAX_WEEKS_5
---------- ---------- ---------- -----------
1998 17 58.78 117.56
1998 18 117.56 117.56
1998 19 58.98 117.56
1998 21 59.6 117.56
1998 23 117.56 117.56
1998 26 117.56 117.56
1998 27 57.52 117.56
1998 28 57.72 117.56
1998 29 57.72 115.44
1998 34 115.44 115.84
1998 35 57.52 115.84
窗口的声明也可以更加细化。假设想要计算本周之前两周到本周之后两周共５周的时间窗口内sale列的最大值，可以使用子句rows between 2 preceding and 2 following来实现这一点。
默认窗口声明
默认的窗口子句是rows between unbounded preceding and current row。如果你没有显示声明窗口，就将会使用默认窗口。显式声明这个子句是避免模棱两可的好办法。
lead和lag
lag能够访问结果集中前面的行，lead函数支持访问结果集中后面的行。
在零售行业中，同店销售额是一个计算得到的矩阵，用来衡量一个门店的业绩，通常用销售数据与去年同季度情况进行比较。在标准化数据模型中，这个矩阵的计算需要访问另一行，因为当前和前一年的销售数据在sale列中是存在不同的数据行的。使用lead和lag函数强大的跨行引用能力，可以很轻松地来计算出这个矩阵。
另一个例子是需要访问前一行或后一行数据的百分比增减计算。这个计算也可以使用lead和lag函数非常优雅地实现。
分析型sql中的数据是按照分区列的值进行分区的。获取前一行的值是与位置相关的运算，数据分区中各行的顺序对于维护逻辑上的一致性是很重要的。在一个数据分区内部，数据行通过order by子句排序以控制某一行在结果集中的位置。
lag语法：
lag (expression, offset, default) over (partition-clause order-by-clause)`
lead和lag函数不支持开窗子句。这两个函数仅支持partition by子句和order by子句。
SQL> --lag函数
SQL> col product format A30
SQL> col country format A10
SQL> col region format A10
SQL> col year format 9999
SQL> col week format 99
SQL> col sale format 99999.99
SQL> col receipts format 99999.99
SQL> set lines 120 pages 100
SQL>
SQL> select year,
2 week,
3 sale,
4 lag(sale, 1, sale) over(
5 partition by sf.product, sf.country, sf.region
6 order by sf.year,sf.week
7 ) prior_wk_sales
8 from sales_fact sf
9 where sf.country in ('Australia')
10 and sf.product = 'Xtend Memory'
11 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- --------- --------------
1998 1 58.15 58.15
1998 2 29.39 58.15
1998 3 29.49 29.39
1998 4 29.49 29.49
1998 5 29.80 29.49
1998 6 58.78 29.8
1998 9 58.78 58.78
1998 10 117.76 58.78
1998 12 59.60 117.76
...
lag函数中的第３个参数指定了默认值，这是可选的。如果分析函数引用了不存在的行，则会返回空值。这是默认行为，可以在第３个参数中指定一个其它返回值来修改。
理解数据行的位移
通过指定不同的位移可以用访问一个数据分区中的怕有行。
SQL> select year,
2 week,
3 sale,
4 lag(sale, 10, sale) over(
5 partition by sf.product, sf.country, sf.region
6 order by sf.year,sf.week
7 ) prior_wk_sales_10
8 from sales_fact sf
9 where sf.country in ('Australia')
10 and sf.product = 'Xtend Memory'
11 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE PRIOR_WK_SALES_10
----- ---- --------- -----------------
1998 1 58.15 58.15
1998 2 29.39 29.39
1998 3 29.49 29.49
1998 4 29.49 29.49
1998 5 29.80 29.8
1998 6 58.78 58.78
1998 9 58.78 58.78
1998 10 117.76 117.76
1998 12 59.60 59.6
1998 14 58.78 58.78
1998 15 58.78 58.15
1998 17 58.78 29.39
1998 18 117.56 29.49
1998 19 58.98 29.49
1998 21 59.60 29.8
1998 23 117.56 58.78
1998 26 117.56 58.78
1998 27 57.52 117.76
1998 28 57.72 59.6
1998 29 57.72 58.78
1998 34 115.44 58.78
1998 35 57.52 58.78
1998 38 115.84 117.56
1998 39 115.84 58.98
1998 40 57.52 59.6
1998 41 58.32 117.56
1998 42 115.84 117.56
1998 43 57.52 57.52
1998 44 57.52 57.72
上面的例子中，lag函数使用了位移量10访问往前第10行的数据。
lead函数
lead函数与lag函数类似，只是它可以访问排序后的结果集中当前值后面的数据行。
--lead函数
select sf.year,sf.week,sf.sale,
lead(sale,1,sale) over(
partition by sf.product,sf.country,sf.region
order by sf.year,sf.week
) prior_wk_sales
from sales_fact sf
where sf.country in ('Australia') and sf.product='Xtend Memory'
order by sf.product,sf.country,sf.year,sf.week;
partition by子句可以用来指定不同中的分区边界，而order by子句可以改变分区内的排序顺序。有效地选择分区和排序列可以访问一个结果集中的任一行。
first_value和last_value
first_value和last_value常用在计算排过序的结果集中的最大值和最小值。
生成某个产品在一定市场领域的销售额最高商店的报表是这些分析函数最经典的应用。
first_value语法：
first_value(expression) over (partition-clause order-by-clause windowing-clause)
SQL> --first_value函数
SQL> select year,week,sale,
2 first_value(sale) over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) top_sale_value,
7 first_value(week) over(
8 partition by sf.product,sf.country,sf.region,sf.year
9 order by sf.sale desc
10 rows between unbounded preceding and unbounded following
11 ) top_sale_week
12 from sales_fact sf
13 where sf.country in ('Australia') and sf.product = 'Xtend Memory'
14 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE TOP_SALE_VALUE TOP_SALE_WEEK
---------- ---------- ---------- -------------- -------------
1998 1 58.15 172.56 48
1998 2 29.39 172.56 48
1998 3 29.49 172.56 48
1998 4 29.49 172.56 48
1998 5 29.8 172.56 48
1998 6 58.78 172.56 48
1998 9 58.78 172.56 48
1998 10 117.76 172.56 48
1998 12 59.6 172.56 48
1998 14 58.78 172.56 48
1998 15 58.78 172.56 48
YEAR WEEK SALE TOP_SALE_VALUE TOP_SALE_WEEK
---------- ---------- ---------- -------------- -------------
1998 17 58.78 172.56 48
1998 18 117.56 172.56 48
1998 19 58.98 172.56 48
1998 21 59.6 172.56 48
1998 23 117.56 172.56 48
1998 26 117.56 172.56 48
1998 27 57.52 172.56 48
1998 28 57.72 172.56 48
1998 29 57.72 172.56 48
1998 34 115.44 172.56 48
1998 35 57.52 172.56 48
partition by sf.product,sf.country,sf.region,sf.year子句使用指定的这些列来对数据进行分区。order by sf.sale desc子句将数据行按照sale列值的降序排列。
窗口的上下边界条件通过子句rows between unbounded preceding and unbounded following指定。在product,country,region,year的层级上来获取最大的销售值，从而窗中就包含一个数据分区中的所有行。
实际操作中，数据是按照product,country,region,year和sale列排序的。sale列的排序是降序的。由于声明了按照sale列的值降序排列，每个数据分区中第一行数据都将具有最大的值。因此，first_value(sale)子句获取数据分区中最大的sale列值。
SQL> --last_value函数
SQL> select year,week,sale,
2 last_value(sale) over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) low_sale
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product = 'Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE LOW_SALE
---------- ---------- ---------- ----------
1998 1 58.15 28.76
1998 2 29.39 28.76
1998 3 29.49 28.76
1998 4 29.49 28.76
1998 5 29.8 28.76
1998 6 58.78 28.76
1998 9 58.78 28.76
1998 10 117.76 28.76
1998 12 59.6 28.76
1998 14 58.78 28.76
1998 15 58.78 28.76
YEAR WEEK SALE LOW_SALE
---------- ---------- ---------- ----------
1998 17 58.78 28.76
1998 18 117.56 28.76
1998 19 58.98 28.76
1998 21 59.6 28.76
1998 23 117.56 28.76
1998 26 117.56 28.76
1998 27 57.52 28.76
1998 28 57.72 28.76
1998 29 57.72 28.76
1998 34 115.44 28.76
1998 35 57.52 28.76
类似的，可以使用last_value函数计算最小值或最大值。last_value函数在数据行窗口中获取最后一行的列值。如上面要计算sale列的最小值,那么可以使用last_value(sale)子句及order by sf.sale desc子句的组合来排序。order by sf.sale desc按降序排，last_value(sale)获取最后一行，那么最后一行的值将会最小值。
可以有效地控制来使用窗口声明的粒度生成复杂报表。例如，rows between 10 preceding and 10 following子句指定了在一个21行数据的窗口中求最大值或最小值。
空值通过[respect nulls | ignore nulls]子句处理。respect nulls子句是默认值。默认情况下，如果第一行中的列值是空值，first_value函数就会返回空值。如果指定了ignore nulls子句，则first_value函数将会返回在窗口中第一个列值不为空的行的值。
其它分析函数
nth_value
first_value和last_value函数能够获取排过序的结果集中的第一行或最后一行数据，但用它们获取任意行的数据还不是很直接。
nth_value，可以获取排过序的结果集中的任意一行，而不仅是第一行或最后一行。
first_value = nth_value(column_name,1)
nth_value支持开窗子句，开窗子句实现了动态滑动窗口的能力。因此，可以写高效的简单查询来回答复杂的问题。如，对于某个产品哪家店的销售在12周的时间内第2高？
nth_value函数语法如下：
nth_value (measure, n) [ from first | from last ] [ respect nulls | ignore nulls ]
over (partitioning-clause order-by-clause windowing-clause)
--nth_value函数
select sf.year,sf.week,sf.sale,
nth_value(sf.sale,2) over (
partition by sf.product,sf.country,sf.region,sf.year
order by sf.sale desc
rows between unbounded preceding and unbounded following
) sale_2nd_top
from sales_fact sf
where sf.country in ('Australia') and sf.product='Xtend Memory'
order by sf.product,sf.country,sf.year,sf.week;
sql语句在sf.product,sf.country,sf.region,sf.year层级上取week列的值和sale列值第二高的值。因为数据是按照sale列降序进行排列的，结果集中的第２行就是sale列中第２高的值。partition by sf.product,sf.country,sf.region,sf.year声明了分区列。
本函数要在oracle 11gR2版本以上才能运行。
rank
rank函数以数值形式返回一个数据行在排序后的结果集中的位置。如果数据行是按某一列进行排序的，则这一行在窗口中的位置就反映了该值在窗口内数据行中的排名。在排名并列的情况下，具有同样值的行将具有同样的排名而接下来的排名就会被跳过，从而在排名值上留下空隙。这意味着某两行可能具有同一排名，排名也不一定是连续的。
rank对于计算最上面或最下面n行是非常有用的，如查找销售量在前１０位的同就是零售业数据仓库中一个典型的查询。如果需要写查询来计算某个结果集中最上面或最下面n个元素的值，就可以使用rank或dense_rank函数。
rank函数的语法：
rank() over (partition-clause order-by-clause)
SQL> --rank函数的使用：销售额前１０位的周
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 rank() over (
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.sale desc
6 ) sales_rank
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week
10 ) where sales_rank<=10
11 order by 1,4;
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 2
1998 18 117.56 3
1998 23 117.56 3
1998 26 117.56 3
1998 38 115.84 6
1998 42 115.84 6
1998 39 115.84 6
1998 34 115.44 9
1998 52 86.38 10
1999 17 148.12 1
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1999 47 147.78 2
1999 15 135.1 3
1999 44 130.72 4
1999 42 120.59 5
1999 25 107.44 6
1999 22 107.44 6
1999 34 105.8 8
1999 37 105.8 8
1999 8 103.11 10
2000 46 246.74 1
2000 21 187.48 2
计算按照sf.product,sf.country,sf.region,sf.year的列值来计算的销售额前10行的值。partition by sf.product,sf.country,sf.region,sf.year声明了分区列，order by sf.sale desc子句声明了在数据分区中的数据行按sale列的值降序排列。rank函数用来计算数据行在分区中的排名。这个sql语句被包在一个内嵌视图中，然后应用sales_rank<=10谓语取出sale列值在前10位的周。
同时还要注意，开窗子句在rank函数中不适用，rank函数是应用在数据分区中的所有行上的。
rank函数为并列排名分配了同样的排名值。
dense_rank
dense_rank是rank函数的变体。rank和dense_rank函数的区别在于当存在并列的时候dense_rank函数不会跳过排名值。dense_rank对于查找结果集中顶部、底部或中间n行的数据是非常有用的。
SQL> --dense_rank函数
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 dense_rank() over (
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.sale desc
6 ) sales_rank
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week
10 ) where sales_rank<=10
11 order by 1,4;
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 2
1998 18 117.56 3
1998 23 117.56 3
1998 26 117.56 3
1998 38 115.84 4
1998 39 115.84 4
1998 42 115.84 4
1998 34 115.44 5
1998 52 86.38 6
1998 21 59.6 7
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 12 59.6 7
1998 19 58.98 8
1998 17 58.78 9
1998 15 58.78 9
1998 14 58.78 9
1998 6 58.78 9
1998 9 58.78 9
1998 51 58.32 10
1998 41 58.32 10
1999 17 148.12 1
1999 47 147.78 2
dense_rank函数在排名值需要连续的时候民是很有用的。例如，在一个班级的学生花名册上排名前10的学生就不能被跳过。另一方面，rank函数在排名值不需要连续时是有很用的。
dense_rank函数中空值的排序位置可以通过nulls first或nulls last子句控制。对于升序排列，nulls last是默认值，对于降序排列，nulls first是默认值。
使用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-clause)
注意，dene_rank函数是如何作为特定聚集函数(min,max,sum,avg,count,variance或stddev)修饰符的。keep关键字可以使语法更清晰，并且还可以限定聚集函数表明只返回聚集函数的第一个或最后一个值。当应用在这种场景下时，dense_rank表明oracle权在具有最小first或最大last dense_rank值的数据行上实现聚集。
在first｜last keep函数中使用dense_rank函数
SQL> get E:\bjc2016\study\pln.sql
1 --set serveroutput off;
2 select xplan.*
3 from (select max(sql_id) keep
4 (dense_rank last order by last_active_time) sql_id,
5 max(child_number) keep
6 (dense_rank last order by last_active_time) child_number
7 FROM V$SQL
8 WHERE UPPER(SQL_TEXT) LIKE '%&1%'
9 and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
10 ) sqlinfo,
11* table(dbms_xplan.display_cursor(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan;
在这个sql中，想要返回的是v$sql视图中最近执行的满足sql文本中包含字符串&1的sql语句的sql_id和child_number。dense_rank函数确保返回的是通过last_active_time得出的最后一条执行的语句。如果没有这个函数，就需要在内嵌视图中使用last_value函数，并对这个视图返回的数据集按照sql_id和child_number分组，执行max聚集运算。也就是说，必须增加一个额外的步骤。这种方法确实是一种更简短且高效的得到所需结果的方法。
row_number
row_number函数为有序结果庥中的每一行分配唯一的行编号。如果声明了分区子句，则为每一行分配一个基于其在该有序分区中位置的唯一编号。如果没有声明分区子句，则为结果集中的每一行分配唯一编号。
row_number函数可用于获取顶部、底部或中间n行数据的查询，与rank和dense_rank函数类似。尽管rank,dense_rank和row_number函数具有类似的功能，在它们之间还是有很微妙的区别，其中一个区别就是row_number函数不支持开窗子句。
row_number语法：
row_number() over (partition-clause order-by-clause)
row_number函数是非确定性函数，如果数据分区中具有相同的值，row_number函数的值是不确定的。相反地，rank和dense_rank函数是确定性函数，重复执行查询也会返回一致的数据。
SQL> select sf.year,sf.week,sf.sale,
2 row_number() over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 ) sales_rn,
6 rank() over(
7 partition by sf.product,sf.country,sf.region,sf.year
8 order by sf.sale desc
9 ) sales_rank
10 from sales_fact sf
11 where sf.country in ('Australia') and sf.product='Xtend Memory'
12 order by sf.product,sf.country,sf.year,sales_rank;
YEAR WEEK SALE SALES_RN SALES_RANK
---------- ---------- ---------- ---------- ----------
1998 48 172.56 1 1
1998 10 117.76 2 2
1998 26 117.56 4 3
1998 18 117.56 3 3
1998 23 117.56 5 3
1998 39 115.84 6 6
1998 38 115.84 7 6
1998 42 115.84 8 6
1998 34 115.44 9 9
1998 52 86.38 10 10
1998 12 59.6 11 11
YEAR WEEK SALE SALES_RN SALES_RANK
---------- ---------- ---------- ---------- ----------
1998 21 59.6 12 11
1998 19 58.98 13 13
1998 17 58.78 15 14
1998 14 58.78 16 14
1998 9 58.78 17 14
1998 15 58.78 14 14
1998 6 58.78 18 14
1998 41 58.32 19 19
1998 51 58.32 20 19
1998 1 58.15 21 21
1998 47 57.72 22 22
ratio_to_report
分析函数ratio_to_report计算数据分区中某个值与和值的比率。如果没有声明分区子句，这个函数将会计算一个值与整个结果集中和值的比率。这个分析函数可以用于在不同层级上计算比率，它不需要进行自联结。
ratio_to_report可以计算报表中某个值占总值的百分比。例如，考虑某个零售连锁店中某种产品的销售报表。每家门店都对该产品的销售总额做出了贡献，并且知道每家门店的销售额占总销售额的百分比有助于市场趋势分析。
SQL> select sf.year,sf.week,sf.sale,
2 trunc(100*
3 ratio_to_report(sale) over(partition by sf.product,sf.country,sf.region,sf.year),2) sales_yr,
4 trunc(100*
5 ratio_to_report(sale) over(partition by sf.product,sf.country,sf.region),2) sales_prod
6 from sales_fact sf
7 where sf.country in ('Australia') and sf.product='Xtend Memory'
8 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
1998 1 58.15 2.26 .43
1998 2 29.39 1.14 .21
1998 3 29.49 1.15 .22
1998 4 29.49 1.15 .22
1998 5 29.8 1.16 .22
1998 6 58.78 2.29 .43
1998 9 58.78 2.29 .43
1998 10 117.76 4.59 .88
1998 12 59.6 2.32 .44
1998 14 58.78 2.29 .43
1998 15 58.78 2.29 .43
YEAR WEEK SALE SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
1998 17 58.78 2.29 .43
1998 18 117.56 4.58 .87
1998 19 58.98 2.3 .44
1998 21 59.6 2.32 .44
1998 23 117.56 4.58 .87
1998 26 117.56 4.58 .87
1998 27 57.52 2.24 .43
1998 28 57.72 2.25 .43
1998 29 57.72 2.25 .43
1998 34 115.44 4.5 .86
1998 35 57.52 2.24 .43
sql语句计算了两个比率，sales_yr是在sf.product,sf.country,sf.region,sf.year层级上进行计算的比率，而sales_prod是在sf.product,sf.country,sf.region层级上来计算的。ratio_to_report函数返回一个比率并且被乘以100来算出百分比。
trunc(x[,y])
【功能】返回x按精度y截取后的值
【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。
【返回】数字
【示例】
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;
返回：5555.66 5500 5555
【相近】round(x[,y])
返回截取后的值，用法同trunc(x[,y]),只是要做四舍五入
percent_rank
percent_rank函数以0到１之间的分数形返回某个值在数据分区中的排名。percent_rank的计算公式为(rank-1)/(n-1)其中如果声明了分区子句n就是分区中的数据行数，如果没有声明分区子句n就是结果集中所有的数据库行数。percent_rank函数可以计算某个值在结果集中按百分比所处的相对位置。
这个排名可以计算为相对于当前数据分区或相对于整个结果集。例如，计算一家零售连锁门店的销售额在某个地区或区域中所处的名次可以找出表现最好或最差的门店。
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 100*percent_rank() over(
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sale desc
6 ) pr
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 ) where pr<50
10 order by year,sale desc;
YEAR WEEK SALE PR
---------- ---------- ---------- ----------
1998 48 172.56 0
1998 10 117.76 2.85714286
1998 26 117.56 5.71428571
1998 18 117.56 5.71428571
1998 23 117.56 5.71428571
1998 39 115.84 14.2857143
1998 38 115.84 14.2857143
1998 42 115.84 14.2857143
1998 34 115.44 22.8571429
1998 52 86.38 25.7142857
1998 12 59.6 28.5714286
使用percent_rank函数来计算销售额前50位的百分比。partition by sf.product,sf.country,sf.region,sf.year order by sale desc子句在由分区列product,country,region和year定义的数据分区上计算sale列值的百分比。数据行按照sale列的降序排列。为了得出百分比，计算结果乘以了100。
percentile_cont
percentile_cont函数可以计算内插值。例如每个地区或城市中等收入家庭的收入。percentile_count函数接收一个０到１之间的几率值并返回与声明了排序的percent_rank函数计算值相等的内插值百分比。事实上，percentile_cont函数是percent_rank函数的反函数，与percent_rank函数的输出结合起来看到可以更容易地来理解percentile_cont函数。
如计算一个城市或地区中等收入家庭的收入值，中位值是percent_rank为０.５。percentile_cont(0.5)子句将会返回中位值，因为percentile_cont函数计算值percent_rank为０.５。实际上，median函数是percentile_cont函数的一个默认值为０.５的特例。这个函数忽略空值。同时也不支持开窗子句。
percentile_cont语法：
percentile_cont(expr) within group(sort-clause) over (partition-clause over-by-clause)
percentile_cont函数的语法与到目前为止所讨论的分析函数的语法稍有不同。新的子句within group (order by sale desc)取代了之前的order by子句，但在功能上与声明order by子句是一样的。
SQL> select sf.year,sf.week,sf.sale,
2 percentile_cont(0.5) within group
3 (order by sale desc)
4 over( partition by sf.product,sf.country,sf.region,sf.year) pc,
5 percent_rank() over (
6 partition by sf.product,sf.country,sf.region,sf.year
7 order by sf.sale desc
8 ) pr
9 from sales_fact sf
10 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE PC PR
---------- ---------- ---------- ---------- ----------
1998 48 172.56 58.55 0
1998 10 117.76 58.55 .028571429
1998 18 117.56 58.55 .057142857
1998 23 117.56 58.55 .057142857
1998 26 117.56 58.55 .057142857
1998 39 115.84 58.55 .142857143
1998 42 115.84 58.55 .142857143
1998 38 115.84 58.55 .142857143
1998 34 115.44 58.55 .228571429
1998 52 86.38 58.55 .257142857
1998 21 59.6 58.55 .285714286
percentile_disc
percentile_disc函数在功能上类似percentile_cont函数，只是percentile_cont函数使用了连续分布模型，而percentile_disc函数使用了离散分布模型。
SQL> select sf.year,sf.week,sf.sale,
2 percentile_disc(0.5) within group (order by sf.sale desc)
3 over (partition by sf.product,sf.country,sf.region,sf.year) pd_desc,
4 percentile_disc(0.5) within group (order by sf.sale)
5 over (partition by sf.product,sf.country,sf.region,sf.year) pd_asc,
6 percent_rank() over (
7 partition by sf.product,sf.country,sf.region,sf.year
8 order by sf.sale desc
9 ) pr
10 from sales_fact sf
11 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE PD_DESC PD_ASC PR
---------- ---------- ---------- ---------- ---------- ----------
1998 48 172.56 58.78 58.32 0
1998 10 117.76 58.78 58.32 .028571429
1998 18 117.56 58.78 58.32 .057142857
1998 23 117.56 58.78 58.32 .057142857
1998 26 117.56 58.78 58.32 .057142857
1998 39 115.84 58.78 58.32 .142857143
1998 42 115.84 58.78 58.32 .142857143
1998 38 115.84 58.78 58.32 .142857143
1998 34 115.44 58.78 58.32 .228571429
1998 52 86.38 58.78 58.32 .257142857
1998 21 59.6 58.78 58.32 .285714286
ntile
ntile函数对一个数据分区中的有序结果集进行划分，将其分组为各个桶，并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如，如果想移除异常值，正常以外的值，可以将它们分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。oracle数据库统计信息收集包也使用ntile函数计算直方图信息边界。在统计学术语中，ntile函数用于创建等宽直方图信息。
SQL> select sf.year,sf.week,sf.sale,
2 ntile(10) over (
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 ) group#
6 from sales_fact sf
7 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE GROUP#
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 1
1998 18 117.56 1
1998 26 117.56 1
1998 23 117.56 2
1998 39 115.84 2
1998 38 115.84 2
1998 42 115.84 2
1998 34 115.44 3
1998 52 86.38 3
1998 12 59.6 3
ntile(10)子句将一个数据分区分为10个桶，数据行按sale列的降序排序。ntile函数将数据行划分为多个桶，每个桶中行的数目相等。由于数据行是按照sale列降序排列的，组编号罗小的数据行sale列的值更大。这一技术可以很容易地剔除异常数据。
stddev
stddev函数可以用来在一个数据分区中的某些数据行上计算标准差，或者如果没有声明分区子句则在整个结果集上计算标准偏差。这个函数为分区子句所指定的数据分区计算标准偏差，定义为方差的平方根。如果没有声明分区子句，就将在结果集中的所有数据行上计算标准偏差。
SQL> select sf.year,sf.week,sf.sale,
2 stddev(sale) over (
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) stddv
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.year,sf.week;
YEAR WEEK SALE STDDV
---------- ---------- ---------- ----------
1998 1 58.15 33.5281435
1998 2 29.39 33.5281435
1998 3 29.49 33.5281435
1998 4 29.49 33.5281435
1998 5 29.8 33.5281435
1998 6 58.78 33.5281435
1998 9 58.78 33.5281435
1998 10 117.76 33.5281435
1998 12 59.6 33.5281435
1998 14 58.78 33.5281435
1998 15 58.78 33.5281435
stddev(sale) 子句在数据分区上计算sale列的标准偏差。分区子句partition by sf.product,sf.country,sf.region,sf.year声明了分区列。开窗子句rows between unbounded preceding and unbounded following声明了数据窗口为数据分区中的所有行。从本质上来说，这个sql语句在数据分区的所有行上计算sale列的标准偏差。
恰当地声明分区子句和开窗子句，可以在不同的粗粒度水平或细粒度水平上来计算标准偏差。还有很多其它的统计函数可以用来计算统计矩阵，如stddev_samp计算累积采样标准差，stddev_pop计算总标准偏差等。
listagg
listagg函数能够将来自多个行中的列值转化为列表格式。例如，如果你要把部门所有员工的名字连起来，那可以使用这个函数将所有名字放到一个列表中。
listagg (string, separator) within group (order-by-clause) over (partition-by-clause)
select listagg(country,',')
within group (order by country desc)
from (
select distinct country from sales_fact
order by country
);
listagg函数的一个局限就是，它的结果强制为varchar2数据类型的最大长度。从12c开始，varchar2数据类型的最大长度从4000字节增加到32767节字。
--listagg结果字符串的长度限制
select length(acol) from (
select listagg(object_name) within group (order by null) acol
from all_objects where rownum<359);
select length(acol) from (
select listagg(object_name) within group (order by null) acol
from all_objects where rownum<359);
性能调优
分析函数所提供的高效率通常使用它们成为重写性能不佳的查询的有效工具。但是相应的，你有时候也需要对分析函数进行调试。对于这一点来说，我们必须要了解分析函数和执行计划，分析和谓语以及索引策略方面你需要知道一些很有用的事实。
SQL> create or replace view max_5_weeks_vm as
2 select sf.country,sf.product,sf.region,sf.year,sf.week,sf.sale,
3 max(sf.sale) over(
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.year,sf.week
6 rows between 2 preceding and 2 following
7 ) max_weeks_5
8 from sales_fact sf;
视图已创建。
SQL> set autotrace traceonly
SQL> select v.year,v.week,v.sale,v.max_weeks_5 from max_5_weeks_vm v
2 where v.country in ('Australia') and v.product='Xtend Memory'
3 and v.region='Australia' and v.year=2000 and v.week<14
4 order by v.year,v.week;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 3282346358
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 174 | 311 (1)| 00:00:04 |
|* 1 | VIEW | MAX_5_WEEKS_VM | 3 | 174 | 311 (1)| 00:00:04 |
| 2 | WINDOW SORT | | 3 | 198 | 311 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| SALES_FACT | 3 | 198 | 310 (1)| 00:00:04 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V"."WEEK"<14)
3 - filter("SF"."PRODUCT"='Xtend Memory' AND "SF"."COUNTRY"='Australia'
AND "SF"."REGION"='Australia' AND "SF"."YEAR"=2000)
执行计划
这个sql语句执行计划，分４步，从第４步开始执行，直到第一步。
１. sales_fact表使用全表扫描访问路径进行访问。
２. product,country,region和year列上的筛选谓词被用来筛选出所需的数据行。
３. 在第３步中所筛选出来的数据行上就用分析函数。
４. 在这些分析函数执行完后用用week列上的谓词。
基于成本的优化器不会为分析函数分配或计算成本，sql语句的成本计算并未考虑分析函数的成本。
谓词
谓词应该尽可能性早地应用于表上来减少结果集以获得更好的性能。数据行必须尽早进行筛选，从而可以在相对较少的数据行上应用分析函数。在执行分析函数时谓词安全性是需要考虑的很重要的一个方面，因为并不是所有的谓词都能够在分析函数之前应用。
上面定义了 max_5_weeks_vm视图并通过一个含有country,product,region,year和week列上谓词的SQL语句来访问该视图。执行计划显示在第３步中应用了下面的筛选谓词：
filter("SF"."PRODUCT"='Xtend Memory' AND "SF"."COUNTRY"='Australia' AND "SF"."REGION"='Australia' AND "SF"."YEAR"=2000)，然而filter("V"."WEEK"<14)并没有在第３步中应用，而只是在第一步中应用了，表明这个谓词是在第２步的窗口排序步骤中执行完分析函数以后应用的。除了这个week列上的谓词以外，其他谓词都被推进了视图中。那些谓词的筛选也都在分析函数执行之前进行。
分区列上的谓词在执行分析函数之前应用，因为一般来说，分区列上的谓词可以很安全地推入到视图中，但分析函数语法中order by子句中的列不能被安全地前推，因为跨行引用需要访问同一分区中的其它数据行，即使这些数据行并不在最终的结果集中返回。
索引
SQL> create index sales_fact_cp on sales_fact(country,product);
索引已创建。
SQL> select v.year,v.week,v.sale,v.max_weeks_5 from max_5_weeks_vm v
2 where v.country in ('Australia') and v.product='Xtend Memory'
3 and v.region='Australia' and v.year=2000 and v.week<14
4 order by v.year,v.week;
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 1538189358
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 232 | 107 (1)| 00:00:02 |
|* 1 | VIEW | MAX_5_WEEKS_VM | 4 | 232 | 107 (1)| 00:00:02 |
| 2 | WINDOW SORT | | 4 | 264 | 107 (1)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| SALES_FACT | 4 | 264 | 106 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | SALES_FACT_CP | 118 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V"."WEEK"<14)
3 - filter("SF"."REGION"='Australia' AND "SF"."YEAR"=2000)
4 - access("SF"."COUNTRY"='Australia' AND "SF"."PRODUCT"='Xtend Memory')
好的索引选择策略是与表访问步骤中的谓词相匹配的，分区列上的谓词被前推到视图中，并且这些谓词在执行分析函数之前应用。因此，如果SQL语句使用这些谓词的话，可能对分区列进行索引是更好的方法。
上面的例子中，在country和product列上增加了一个新索引，执行计划第４步显示使用了基于索引的访问。谓词信息部分显示所有４个分区列上的谓词都在执行分析函数之前的第４步和第３步中应用。但week列上的谓词直到执行计划中的第１步才进行了应用。因此，将week列加入索引是没有用的，因为直到分析函数执行完成后才会应用这一列上的谓词。
高级话题
１. 动态分析语句
２. 分析函数的嵌套
３. 并行
４. PGA大小
动态SQL
关于分析sql语句的一个普遍问题是可否在分区或排序列上使用绑定变量，答案是不可以，可果想要灵活地动态修改或排序列，需要使用动态sql语句。
如果只是动态调整分区列，那么可以创建一个存储过程包来获取存储过程中的逻辑。
create or replace procedure analytic_dynamic_prc(part_col_string varchar2,
v_country varchar2,
v_product varchar2) is
type numtab is table of number(18, 2) index by binary_integer;
l_year numtab;
l_week numtab;
l_sale numtab;
l_rank numtab;
l_sql_string varchar2(512);
begin
l_sql_string := '
select * from (
select year,week,sale,
rank() over(
partition by ' || part_col_string || '
order by sale desc
) sales_rank
from sales_fact=
where county in (' || chr(39) || v_country || chr(39) || ')
and product= ' || chr(39) || v_product || chr(39) || '
order by product,country,year,week
) where sales_rank <= 10
order by 1,4';
execute immediate l_sql_string bulk collect
into l_year, l_week, l_sale, l_rank;
for i in 1 .. l_year.count loop
dbms_output.put_line(l_year(i) || ' |' || l_week(i) || ' |' ||
l_sale(i) || ' |' || l_rank(i));
end loop;
end;
/
SQL> create or replace procedure analytic_dynamic_prc(part_col_string varchar2,
2 v_country varchar2,
3 v_product varchar2) is
4 type numtab is table of number(18, 2) index by binary_integer;
5 l_year numtab;
6 l_week numtab;
7 l_sale numtab;
8 l_rank numtab;
9 l_sql_string varchar2(512);
10
11 begin
12 l_sql_string := '
13 select * from (
14 select year,week,sale,
15 rank() over(
16 partition by ' || part_col_string || '
17 order by sale desc
18 ) sales_rank
19 from sales_fact
20 where country in (' || chr(39) || v_country || chr(39) || ')
21 and product= ' || chr(39) || v_product || chr(39) || '
22 order by product,country,year,week
23 ) where sales_rank <= 10
24 order by 1,4';
25 execute immediate l_sql_string bulk collect
26 into l_year, l_week, l_sale, l_rank;
27 for i in 1 .. l_year.count loop
28 dbms_output.put_line(l_year(i) || ' |' || l_week(i) || ' |' ||
29 l_sale(i) || ' |' || l_rank(i));
30 end loop;
31 end;
32 /
过程已创建。
SQL> set serveroutpu on
SQL> exec analytic_dynamic_prc ('product,country,region','Australia','Xtend Memory');
1998 |48 |172.56 |9
2000 |46 |246.74 |3
2000 |21 |187.48 |5
2000 |43 |179.12 |7
2000 |34 |178.52 |8
2001 |16 |278.44 |1
2001 |4 |256.7 |2
2001 |21 |233.7 |4
2001 |48 |182.96 |6
2001 |30 |162.91 |10
2001 |14 |162.91 |10
PL/SQL 过程已成功完成。
SQL> exec analytic_dynamic_prc ('product,country,region,year','Australia','Xtend Memory');
1998 |48 |172.56 |1
1998 |10 |117.76 |2
1998 |18 |117.56 |3
1998 |23 |117.56 |3
1998 |26 |117.56 |3
1998 |38 |115.84 |6
1998 |42 |115.84 |6
1998 |39 |115.84 |6
1998 |34 |115.44 |9
1998 |52 |86.38 |10
1999 |17 |148.12 |1
1999 |47 |147.78 |2
1999 |15 |135.1 |3
PL/SQL 过程已成功完成。
嵌套分析函数
分析函数不能进行嵌套，但可以使用子查询实现嵌套的效果。
内层子查询给出出现sale列最大值的year和week列的值，外层查询中的lag函数取出去年sale列的最大值
SQL> select year, week, top_sale_year,
2 lag(top_sale_year) over (order by year desc) prev_top_sale_yer
3 from (
4 select distinct
5 first_value(year) over(
6 partition by product, country,region,year
7 order by sale desc
8 rows between unbounded preceding and unbounded following
9 ) year,
10 first_value(week) over(
11 partition by product, country,region,year
12 order by sale desc
13 rows between unbounded preceding and unbounded following
14 ) week,
15 first_value(sale) over(
16 partition by product, country,region,year
17 order by sale desc
18 rows between unbounded preceding and unbounded following
19 ) top_sale_year
20 from sales_fact
21 where country in('Australia') and product='Xtend Memory'
22 )
23 order by year,week;
YEAR WEEK TOP_SALE_YEAR PREV_TOP_SALE_YER
---------- ---------- ------------- -----------------
1998 48 172.56 148.12
1999 17 148.12 246.74
2000 46 246.74 278.44
2001 16 278.44
lag和first_value函数的分区子句是不同的，分析函数first_value用来在由分区列product,country,region及year指定的分区上计算sale列的最大值，而lag是获取仅声明了order by year desc排序子句的前一年sale列的第一行。
并行
通过在sql语句中声明parallel提示或在对象级设置并行度，分析函数也可是并行的。如果你有大量的数据需要通过分析函数处理，并行是一个很好的选择。使用多层级嵌套的SQL语句也可以从并行中受益。
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 118 | 4602 | 110 (4)| 00:00:02 |
| 1 | SORT ORDER BY | | 118 | 4602 | 110 (4)| 00:00:02 |
| 2 | WINDOW SORT | | 118 | 4602 | 110 (4)| 00:00:02 |
| 3 | VIEW | | 118 | 4602 | 108 (2)| 00:00:02 |
| 4 | HASH UNIQUE | | 118 | 7552 | 108 (2)| 00:00:02 |
| 5 | WINDOW SORT | | 118 | 7552 | 108 (2)| 00:00:02 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES_FACT | 118 | 7552 | 106 (0)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | SALES_FACT_CP | 118 | | 3 (0)| 00:00:01 |
上面是嵌套分析函数中SQL查询使用并行的执行计划。在这个计算中有两个window运算，因为SQL语句中嵌套了lag和first_value分析函数。
在并行活动PQ slaves之间数据行的最优分布对于维护功能的正确性是很关键的，这由oracle数据库自动进行处理。
PGA大小
大多数与分析函数相关的运算都是在进程的程序共享区PGA中执行的，为了得到最优的性能，有个足够大的内存区以避免程序使用硬盘执行分析函数是很重要的。
数据库初始化参数PGA_AGGREGATE_TARGET(PGAT)控制着PGA的最大大小。默认情况下，一个串行进程最大可以分配到PGA的PGAT值的５%，对于并行进程，最大限制为30%。将PGAT保持在一个较高的值对于提高分析函数的性能是很重要的。
总结：
可以使用分析函数简化复杂的sql语句，分析函数为我们提供了一种全新的思维方式，能句与分区和开窗子句相结合从而简化复杂的SQL语句，可以解决很多性能问题。
网友评论