欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle SQL高级编程——分析函数(窗口函数)全面讲解

程序员文章站 2022-10-19 22:29:55
概述 分析函数是以一定的方法在一个与当前行相关的结果子集中进行计算,也称为窗口函数。 一般结构为 Function(arg1 , arg2 ……)...

概述

分析函数是以一定的方法在一个与当前行相关的结果子集中进行计算,也称为窗口函数。
一般结构为
Function(arg1 , arg2 ……) over(partition by clause order by clause windowing clause )

Windowing clause : rows | range between start_expr and end_expr
Start_expr is unbounded preceding | current row | n preceding | n following
End_expr is unbounded following | current row | n preceding | n following
不是所有的分析函数都支持开窗子句。

创建测试表

SH@ prod> create table sales_fact  as 
  2  select country_name country , country_subregion region , prod_name product , calendar_year year , calendar_week_number week , 
  3  sum(amount_sold) sale , sum(amount_sold*
  4  (case when mod(rownum , 10 ) = 0 then 1.4 
  5  when mod(rownum , 5)= 0 then 0.6
  6  when mod(rownum , 2)= 0 then 0.9
  7  when mod(rownum , 2)=1 then 1.2 
  8  else 1 end ) ) receipts 
  9  from sales , times , customers , countries , products 
 10  where sales.time_id = times.time_id and 
 11  sales.prod_id = products.prod_id and
 12  sales.cust_id = customers.cust_id and
 13  customers.country_id = countries.country_id 
 14  group by country_name , country_subregion , prod_name , calendar_year , calendar_week_number ;

Table created.

把聚合函数当作分析函数使用

分析函数列只是一列数值,每一行对应一个值,对于查询的其它方面没有任何影响。

从以下查询可以得出以下几点:
1.over分区条件中的列可以不在select列表中,但是必须在数据源中。
2.over排序条件中的列可以不在select列表中,但是必须在数据源中。
3.over排序条件是对所在分区中的数据进行排序,与select语句中的排序无关。但是会影响到分析函数的结果。
4.over中的开窗条件的范围一般仅限于分区本身。rows between unbounded preceding and current row表示从分区的最开始到当前行。
5.分析函数的数据来自结果集(施加了where条件之后的)。

下面的查询中的分析列表示该年从开始到该周的销售累计。

SH@ prod> select year , week , sale , 
  2  sum(sale) over( partition by region , year  
  3  order by week 
  4  rows between unbounded preceding and current row ) running_sum_ytd 
  5  from sales_fact 
  6  where country in ('Australia') and product='Xtend Memory' and week < 10 
  7  order by year , 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
      1999          1      53.52           53.52
      1999          3       94.6          148.12
      1999          4       40.5          188.62
      1999          5      80.01          268.63
      1999          6       40.5          309.13
      1999          8     103.11          412.24
      1999          9      53.34          465.58
      2000          1       46.7            46.7
      2000          3      93.41          140.11
      2000          4      46.54          186.65
      2000          5       46.7          233.35
      2000          7       70.8          304.15
      2000          8      46.54          350.69
      2001          1      92.26           92.26
      2001          2     118.38          210.64
      2001          3      47.24          257.88
      2001          4      256.7          514.58
      2001          5      93.44          608.02
      2001          6      22.44          630.46
      2001          7      69.96          700.42

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2001          8      46.06          746.48
      2001          9      92.67          839.15

29 rows selected.

结果与上面相同,只是排序不同方式,分析列看起来就没有规律了。

SH@ prod> select year , week , sale , 
  2  sum(sale) over( partition by region , year  
  3  order by week 
  4  rows between unbounded preceding and current row ) running_sum_ytd 
  5  from sales_fact 
  6  where country in ('Australia') and product='Xtend Memory' and week < 10 
  7  order by year , sale ;

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      1998          2      29.39           87.54
      1998          4      29.49          146.52
      1998          3      29.49          117.03
      1998          5       29.8          176.32
      1998          1      58.15           58.15
      1998          6      58.78           235.1
      1998          9      58.78          293.88
      1999          4       40.5          188.62
      1999          6       40.5          309.13
      1999          9      53.34          465.58
      1999          1      53.52           53.52
      1999          5      80.01          268.63
      1999          3       94.6          148.12
      1999          8     103.11          412.24
      2000          4      46.54          186.65
      2000          8      46.54          350.69
      2000          1       46.7            46.7
      2000          5       46.7          233.35
      2000          7       70.8          304.15
      2000          3      93.41          140.11
      2001          6      22.44          630.46
      2001          8      46.06          746.48
      2001          3      47.24          257.88
      2001          7      69.96          700.42
      2001          1      92.26           92.26
      2001          9      92.67          839.15
      2001          5      93.44          608.02

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2001          2     118.38          210.64
      2001          4      256.7          514.58

29 rows selected.

分区中的排序选取不恰当,则分析列结果没有什么意义了。分区开窗排序的选取与分析列的结果密切相关。

SH@ prod> select year , week , sale , 
  2  sum(sale) over( partition by  region , year  
  3  order by sale
  4  rows between unbounded preceding and current row ) running_sum_ytd 
  5  from sales_fact 
  6  where country in ('Australia') and product='Xtend Memory' and week < 10 
  7  order by  year , week ;

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      1998          1      58.15          176.32
      1998          2      29.39           29.39
      1998          3      29.49           88.37
      1998          4      29.49           58.88
      1998          5       29.8          118.17
      1998          6      58.78           235.1
      1998          9      58.78          293.88
      1999          1      53.52          187.86
      1999          3       94.6          362.47
      1999          4       40.5            40.5
      1999          5      80.01          267.87
      1999          6       40.5              81
      1999          8     103.11          465.58
      1999          9      53.34          134.34
      2000          1       46.7          186.48
      2000          3      93.41          350.69
      2000          4      46.54           46.54
      2000          5       46.7          139.78
      2000          7       70.8          257.28
      2000          8      46.54           93.08
      2001          1      92.26          277.96
      2001          2     118.38          582.45
      2001          3      47.24          115.74
      2001          4      256.7          839.15
      2001          5      93.44          464.07
      2001          6      22.44           22.44
      2001          7      69.96           185.7

      YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
      2001          8      46.06            68.5
      2001          9      92.67          370.63

29 rows selected.

分析函数的执行计划

虽然有分析函数还是只需要一次全表扫描,但是需要排序。
WINDOW SORT是分析函数的典型特征。

SH@ prod> explain plan for 
  2  select year , week , sale , 
  3  sum(sale) over( partition by  region , year  
  4  order by sale
  5  rows between unbounded preceding and current row ) running_sum_ytd 
  6  from sales_fact 
  7  where country in ('Australia') and product='Xtend Memory' and week < 10 
  8  order by  year , week ;

Explained.

SH@ prod> select * from table(dbms_xplan.display()) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 173857439

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    18 |  1890 |   311   (1)| 00:00:04 |
|   1 |  SORT ORDER BY      |            |    18 |  1890 |   311   (1)| 00:00:04 |
|   2 |   WINDOW SORT       |            |    18 |  1890 |   311   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| SALES_FACT |    18 |  1890 |   309   (1)| 00:00:04 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND
              "WEEK"<10)

Note
-----
   - dynamic sampling used for this statement (level=2)   说明该表还没有统计信息。

20 rows selected.

不加分析列,只是少了一步window sort。

SH@ prod> explain plan for 
  2  select year , week , sale 
  3  from sales_fact 
  4  where country in ('Australia') and product='Xtend Memory' and week < 10 
  5  order by  year , week ;

Explained.

SH@ prod> select * from table(dbms_xplan.display()) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1978576542

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    18 |  1584 |   310   (1)| 00:00:04 |
|   1 |  SORT ORDER BY     |            |    18 |  1584 |   310   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| SALES_FACT |    18 |  1584 |   309   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND
              "WEEK"<10)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

如何使窗口充满整个分区

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year 
  2  order by week 
  3  rows between unbounded preceding and unbounded following )
  4  max_sale
  5  from sales_fact 
  6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  7  order by product , country , year , week ;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      1998          1      58.15      58.78
      1998          2      29.39      58.78
      1998          3      29.49      58.78
      1998          4      29.49      58.78
      1998          5       29.8      58.78
      1998          6      58.78      58.78
      1998          9      58.78      58.78
      1999          1      53.52     103.11
      1999          3       94.6     103.11
      1999          4       40.5     103.11
      1999          5      80.01     103.11
      1999          6       40.5     103.11
      1999          8     103.11     103.11
      1999          9      53.34     103.11
      2000          1       46.7      93.41
      2000          3      93.41      93.41
      2000          4      46.54      93.41
      2000          5       46.7      93.41
      2000          7       70.8      93.41
      2000          8      46.54      93.41
      2001          1      92.26      256.7
      2001          2     118.38      256.7
      2001          3      47.24      256.7
      2001          4      256.7      256.7
      2001          5      93.44      256.7
      2001          6      22.44      256.7
      2001          7      69.96      256.7

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2001          8      46.06      256.7
      2001          9      92.67      256.7

29 rows selected.

两个边界都滑动的窗口

下面语句的窗口是往前两周,加往后两周,加当前周,一共五周。(到达边界时窗口会自动缩小)

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year 
  2  order by week 
  3  rows between 2 preceding and 2 following )
  4  max_sale
  5  from sales_fact 
  6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  7  order by product , country , year , week ;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      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      58.78
      1998          9      58.78      58.78
      1999          1      53.52       94.6
      1999          3       94.6       94.6
      1999          4       40.5       94.6
      1999          5      80.01     103.11
      1999          6       40.5     103.11
      1999          8     103.11     103.11
      1999          9      53.34     103.11
      2000          1       46.7      93.41
      2000          3      93.41      93.41
      2000          4      46.54      93.41
      2000          5       46.7      93.41
      2000          7       70.8       70.8
      2000          8      46.54       70.8  这里只所以是70.8因为窗口缩小了。
      2001          1      92.26     118.38
      2001          2     118.38      256.7
      2001          3      47.24      256.7
      2001          4      256.7      256.7
      2001          5      93.44      256.7
      2001          6      22.44      256.7
      2001          7      69.96      93.44

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2001          8      46.06      92.67
      2001          9      92.67      92.67

29 rows selected.

默认窗口是什么?

一看便知。

SH@ prod> select year , week , sale , max(sale) over(partition by product , country , region , year 
  2  order by week )
  3  max_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      1998          1      58.15      58.15
      1998          2      29.39      58.15
      1998          3      29.49      58.15
      1998          4      29.49      58.15
      1998          5       29.8      58.15
      1998          6      58.78      58.78
      1998          9      58.78      58.78
      1999          1      53.52      53.52
      1999          3       94.6       94.6
      1999          4       40.5       94.6
      1999          5      80.01       94.6
      1999          6       40.5       94.6
      1999          8     103.11     103.11
      1999          9      53.34     103.11
      2000          1       46.7       46.7
      2000          3      93.41      93.41
      2000          4      46.54      93.41
      2000          5       46.7      93.41
      2000          7       70.8      93.41
      2000          8      46.54      93.41
      2001          1      92.26      92.26
      2001          2     118.38     118.38
      2001          3      47.24     118.38
      2001          4      256.7      256.7
      2001          5      93.44      256.7
      2001          6      22.44      256.7
      2001          7      69.96      256.7

      YEAR       WEEK       SALE   MAX_SALE
---------- ---------- ---------- ----------
      2001          8      46.06      256.7
      2001          9      92.67      256.7

29 rows selected.

Lead和Lag(不支持开窗的函数)

有开窗语句时会报这样的错

rows between 2 preceding and 2 following )
*
ERROR at line 3:
ORA-00907: missing right parenthesis

LEAD是求下一个,而不是前一个。在分区的下边界处,LEAD处回空值。

SH@ prod> select year , week , sale , lead(sale) over(partition by product , country , region , year 
  2  order by week  )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15       29.39
      1998          2      29.39       29.49
      1998          3      29.49       29.49
      1998          4      29.49        29.8
      1998          5       29.8       58.78
      1998          6      58.78       58.78
      1998          9      58.78
      1999          1      53.52        94.6
      1999          3       94.6        40.5
      1999          4       40.5       80.01
      1999          5      80.01        40.5
      1999          6       40.5      103.11
      1999          8     103.11       53.34
      1999          9      53.34
      2000          1       46.7       93.41
      2000          3      93.41       46.54
      2000          4      46.54        46.7
      2000          5       46.7        70.8
      2000          7       70.8       46.54
      2000          8      46.54
      2001          1      92.26      118.38
      2001          2     118.38       47.24
      2001          3      47.24       256.7
      2001          4      256.7       93.44
      2001          5      93.44       22.44
      2001          6      22.44       69.96
      2001          7      69.96       46.06

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       92.67
      2001          9      92.67

29 rows selected.

LAG求上一个,也就是前一个。在分区的上边界处返回空值。

SH@ prod> select year , week , sale , lag(sale) over(partition by product , country , region , year 
  2  order by week  )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15
      1998          2      29.39       58.15
      1998          3      29.49       29.39
      1998          4      29.49       29.49
      1998          5       29.8       29.49
      1998          6      58.78        29.8
      1998          9      58.78       58.78
      1999          1      53.52
      1999          3       94.6       53.52
      1999          4       40.5        94.6
      1999          5      80.01        40.5
      1999          6       40.5       80.01
      1999          8     103.11        40.5
      1999          9      53.34      103.11
      2000          1       46.7
      2000          3      93.41        46.7
      2000          4      46.54       93.41
      2000          5       46.7       46.54
      2000          7       70.8        46.7
      2000          8      46.54        70.8
      2001          1      92.26
      2001          2     118.38       92.26
      2001          3      47.24      118.38
      2001          4      256.7       47.24
      2001          5      93.44       256.7
      2001          6      22.44       93.44
      2001          7      69.96       22.44

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       69.96
      2001          9      92.67       46.06

29 rows selected.

复杂的Lead和Lag

Lead和lag函数的第一参数为返回的列,第二参数为相隔行数(非负),第三个参数为不存在时的默认值(可以指定为当前行的值)。

SH@ prod> select year , week , sale , lag(sale , 2 , 0 ) over(partition by product , country , region , year 
  2  order by week  )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15           0
      1998          2      29.39           0
      1998          3      29.49       58.15
      1998          4      29.49       29.39
      1998          5       29.8       29.49
      1998          6      58.78       29.49
      1998          9      58.78        29.8
      1999          1      53.52           0
      1999          3       94.6           0
      1999          4       40.5       53.52
      1999          5      80.01        94.6
      1999          6       40.5        40.5
      1999          8     103.11       80.01
      1999          9      53.34        40.5
      2000          1       46.7           0
      2000          3      93.41           0
      2000          4      46.54        46.7
      2000          5       46.7       93.41
      2000          7       70.8       46.54
      2000          8      46.54        46.7
      2001          1      92.26           0
      2001          2     118.38           0
      2001          3      47.24       92.26
      2001          4      256.7      118.38
      2001          5      93.44       47.24
      2001          6      22.44       256.7
      2001          7      69.96       93.44

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       22.44
      2001          9      92.67       69.96

29 rows selected.

将默认值指定为当前行的值。

SH@ prod> select year , week , sale , lag(sale , 2 , sale ) over(partition by product , country , region , year 
  2  order by week  )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15       58.15
      1998          2      29.39       29.39
      1998          3      29.49       58.15
      1998          4      29.49       29.39
      1998          5       29.8       29.49
      1998          6      58.78       29.49
      1998          9      58.78        29.8
      1999          1      53.52       53.52
      1999          3       94.6        94.6
      1999          4       40.5       53.52
      1999          5      80.01        94.6
      1999          6       40.5        40.5
      1999          8     103.11       80.01
      1999          9      53.34        40.5
      2000          1       46.7        46.7
      2000          3      93.41       93.41
      2000          4      46.54        46.7
      2000          5       46.7       93.41
      2000          7       70.8       46.54
      2000          8      46.54        46.7
      2001          1      92.26       92.26
      2001          2     118.38      118.38
      2001          3      47.24       92.26
      2001          4      256.7      118.38
      2001          5      93.44       47.24
      2001          6      22.44       256.7
      2001          7      69.96       93.44

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       22.44
      2001          9      92.67       69.96

29 rows selected.

LEAD与LAG关于数据缺口的问题

LAG(sale , 10 ) 这表示与它相隔10行的数据,可是我想访问的10周前的数据。如果中间数据有缺口会出现严重的问题。

FIRST_VALUE和LAST_VALUE

这两个函数都可以与order by条件配合得到最大值和最小值。
First_value返回窗口中的第一个值。Ignore nulls表示忽略空值,如果第一个是空值返回第二个。

SH@ prod> select year , week , sale , first_value(sale ignore nulls) over(partition by product , country , region , year 
  2  order by week  
  3  rows between unbounded preceding and unbounded following )
  4  former_sale
  5  from sales_fact 
  6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  7  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15       58.15
      1998          2      29.39       58.15
      1998          3      29.49       58.15
      1998          4      29.49       58.15
      1998          5       29.8       58.15
      1998          6      58.78       58.15
      1998          9      58.78       58.15
      1999          1      53.52       53.52
      1999          3       94.6       53.52
      1999          4       40.5       53.52
      1999          5      80.01       53.52
      1999          6       40.5       53.52
      1999          8     103.11       53.52
      1999          9      53.34       53.52
      2000          1       46.7        46.7
      2000          3      93.41        46.7
      2000          4      46.54        46.7
      2000          5       46.7        46.7
      2000          7       70.8        46.7
      2000          8      46.54        46.7
      2001          1      92.26       92.26
      2001          2     118.38       92.26
      2001          3      47.24       92.26
      2001          4      256.7       92.26
      2001          5      93.44       92.26
      2001          6      22.44       92.26
      2001          7      69.96       92.26

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       92.26
      2001          9      92.67       92.26

29 rows selected. 

Last_value返回窗口中的最后一个值。Respect nulls表示识别空值,如果最后一个是空值也将其返回。

SH@ prod> select year , week , sale , last_value(sale respect nulls) over(partition by product , country , region , year 
  2  order by week  
  3  rows between unbounded preceding and unbounded following )
  4  former_sale
  5  from sales_fact 
  6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  7  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15       58.78
      1998          2      29.39       58.78
      1998          3      29.49       58.78
      1998          4      29.49       58.78
      1998          5       29.8       58.78
      1998          6      58.78       58.78
      1998          9      58.78       58.78
      1999          1      53.52       53.34
      1999          3       94.6       53.34
      1999          4       40.5       53.34
      1999          5      80.01       53.34
      1999          6       40.5       53.34
      1999          8     103.11       53.34
      1999          9      53.34       53.34
      2000          1       46.7       46.54
      2000          3      93.41       46.54
      2000          4      46.54       46.54
      2000          5       46.7       46.54
      2000          7       70.8       46.54
      2000          8      46.54       46.54
      2001          1      92.26       92.67
      2001          2     118.38       92.67
      2001          3      47.24       92.67
      2001          4      256.7       92.67
      2001          5      93.44       92.67
      2001          6      22.44       92.67
      2001          7      69.96       92.67

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       92.67
      2001          9      92.67       92.67

29 rows selected.

NTH_VALUE访问分区别的任意指定行

FIRST_VALUE相当于NTH_VALUE(sale , 1 )或者NTH_VALUE(sale , 1 )from first respect nulls。
可以与排序配合求第几大,第几小。

SH@ prod> select year , week , sale , nth_value(sale , 1 ) from last ignore nulls over(partition by product , country , region , year 
  2  order by week  
  3  rows between unbounded preceding and unbounded following )
  4  former_sale
  5  from sales_fact 
  6  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  7  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15       58.78
      1998          2      29.39       58.78
      1998          3      29.49       58.78
      1998          4      29.49       58.78
      1998          5       29.8       58.78
      1998          6      58.78       58.78
      1998          9      58.78       58.78
      1999          1      53.52       53.34
      1999          3       94.6       53.34
      1999          4       40.5       53.34
      1999          5      80.01       53.34
      1999          6       40.5       53.34
      1999          8     103.11       53.34
      1999          9      53.34       53.34
      2000          1       46.7       46.54
      2000          3      93.41       46.54
      2000          4      46.54       46.54
      2000          5       46.7       46.54
      2000          7       70.8       46.54
      2000          8      46.54       46.54
      2001          1      92.26       92.67
      2001          2     118.38       92.67
      2001          3      47.24       92.67
      2001          4      256.7       92.67
      2001          5      93.44       92.67
      2001          6      22.44       92.67
      2001          7      69.96       92.67

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06       92.67
      2001          9      92.67       92.67

29 rows selected.

RANK函数(不能开窗,作用于整个分区)

必须有排序条件,rank就是根据order by条件中的列来定排名的。
RANK函数的排名中,如果出现并列,排名将不连续。
如:1 2(2) 4 5 6 7 8 9 。 如果有两个第二名,那么第三名就不存在了。
请注意空值,在排序子句中可以使用NULLS LAST来把空值放在最后面。

SH@ prod> select year , week , sale , rank() over(partition by product , country , region , year 
  2  order by sale )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15           5   没有3
      1998          2      29.39           1
      1998          3      29.49           2
      1998          4      29.49           2
      1998          5       29.8           4
      1998          6      58.78           6
      1998          9      58.78           6
      1999          1      53.52           4
      1999          3       94.6           6
      1999          4       40.5           1
      1999          5      80.01           5
      1999          6       40.5           1
      1999          8     103.11           7
      1999          9      53.34           3
      2000          1       46.7           3
      2000          3      93.41           6
      2000          4      46.54           1
      2000          5       46.7           3
      2000          7       70.8           5
      2000          8      46.54           1
      2001          1      92.26           5
      2001          2     118.38           8
      2001          3      47.24           3
      2001          4      256.7           9
      2001          5      93.44           7
      2001          6      22.44           1
      2001          7      69.96           4

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06           2
      2001          9      92.67           6

29 rows selected.

DENSE_RANK(与RANK的区别在于排名一是连续的)

SH@ prod> select year , week , sale , dense_rank() over(partition by product , country , region , year 
  2  order by sale )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , week ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          1      58.15           4  第三名是存在的
      1998          2      29.39           1
      1998          3      29.49           2
      1998          4      29.49           2
      1998          5       29.8           3
      1998          6      58.78           5
      1998          9      58.78           5
      1999          1      53.52           3
      1999          3       94.6           5
      1999          4       40.5           1
      1999          5      80.01           4
      1999          6       40.5           1
      1999          8     103.11           6
      1999          9      53.34           2
      2000          1       46.7           2
      2000          3      93.41           4
      2000          4      46.54           1
      2000          5       46.7           2
      2000          7       70.8           3
      2000          8      46.54           1
      2001          1      92.26           5
      2001          2     118.38           8
      2001          3      47.24           3
      2001          4      256.7           9
      2001          5      93.44           7
      2001          6      22.44           1
      2001          7      69.96           4

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          8      46.06           2
      2001          9      92.67           6

29 rows selected.

ROW_NUMBER(不支持开窗,不确定性函数)

为分区中的每一行指定一个递增的编号,如果排序的列的值相同,谁先谁后是随机的。

SH@ prod> select year , week , sale , row_number() over(partition by product , country , region , year 
  2  order by sale )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , sale ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          2      29.39           1
      1998          4      29.49           2
      1998          3      29.49           3
      1998          5       29.8           4
      1998          1      58.15           5
      1998          6      58.78           6
      1998          9      58.78           7
      1999          4       40.5           1
      1999          6       40.5           2
      1999          9      53.34           3
      1999          1      53.52           4
      1999          5      80.01           5
      1999          3       94.6           6
      1999          8     103.11           7
      2000          4      46.54           1
      2000          8      46.54           2
      2000          5       46.7           3
      2000          1       46.7           4
      2000          7       70.8           5
      2000          3      93.41           6
      2001          6      22.44           1
      2001          8      46.06           2
      2001          3      47.24           3
      2001          7      69.96           4
      2001          1      92.26           5
      2001          9      92.67           6
      2001          5      93.44           7

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          2     118.38           8
      2001          4      256.7           9

29 rows selected.

Ratio_to_report(当前行的值与分区总和的比值)

这个函数不支持排序和开窗。
求各周的销量在每年中的比例以及在整个产品销量中的比例。

SH@ prod> select year , week , sale ,
  2  trunc(100* ratio_to_report(sale) over(partition by year ) , 2) sales_yr , 
  3  trunc(100* ratio_to_report(sale) over() , 2 ) sales_prod 
  4  from sales_fact 
  5  where country in ('Australia') and product = 'Xtend Memory' and week < 10
  6  order by year , week ;

      YEAR       WEEK       SALE   SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
      1998          1      58.15      19.78       2.98
      1998          2      29.39         10        1.5
      1998          3      29.49      10.03       1.51
      1998          4      29.49      10.03       1.51
      1998          5       29.8      10.14       1.52
      1998          6      58.78         20       3.01
      1998          9      58.78         20       3.01
      1999          1      53.52      11.49       2.74
      1999          3       94.6      20.31       4.85
      1999          4       40.5       8.69       2.07
      1999          5      80.01      17.18        4.1
      1999          6       40.5       8.69       2.07
      1999          8     103.11      22.14       5.28
      1999          9      53.34      11.45       2.73
      2000          1       46.7      13.31       2.39
      2000          3      93.41      26.63       4.79
      2000          4      46.54      13.27       2.38
      2000          5       46.7      13.31       2.39
      2000          7       70.8      20.18       3.63
      2000          8      46.54      13.27       2.38
      2001          1      92.26      10.99       4.73
      2001          2     118.38       14.1       6.07
      2001          3      47.24       5.62       2.42
      2001          4      256.7      30.59      13.16
      2001          5      93.44      11.13       4.79
      2001          6      22.44       2.67       1.15
      2001          7      69.96       8.33       3.58

      YEAR       WEEK       SALE   SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
      2001          8      46.06       5.48       2.36
      2001          9      92.67      11.04       4.75

29 rows selected.

Percent_rank(排在前百分之几)

用来求当前行的排名的相对百分位置。
比如你对人说自己是第10名,别人可能觉得没什么,如果是100000中的第10名,那就是前1/10000,那就非常牛了。
这个函数与RANK的推导公式为:
PERCENT_RANK = (RANK - 1) / (N – 1) , N代表总行数。
RANK – 1代表排名大于自己的人数。
N – 1代表除自己以外的总人数。
总体的意思是除自己之外的其它中人,排名比自己高的人所占的比例。

SH@ prod> select year , week , sale , rank() over(partition by product , country , region , year 
  2  order by sale )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , sale ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          2      29.39           1
      1998          4      29.49           2
      1998          3      29.49           2
      1998          5       29.8           4
      1998          1      58.15           5
      1998          6      58.78           6
      1998          9      58.78           6
      1999          4       40.5           1
      1999          6       40.5           1
      1999          9      53.34           3
      1999          1      53.52           4
      1999          5      80.01           5
      1999          3       94.6           6
      1999          8     103.11           7
      2000          4      46.54           1
      2000          8      46.54           1
      2000          5       46.7           3
      2000          1       46.7           3
      2000          7       70.8           5
      2000          3      93.41           6
      2001          6      22.44           1
      2001          8      46.06           2
      2001          3      47.24           3
      2001          7      69.96           4
      2001          1      92.26           5
      2001          9      92.67           6
      2001          5      93.44           7

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          2     118.38           8
      2001          4      256.7           9

29 rows selected.

SH@ prod> select year , week , sale , 100*percent_rank() over(partition by product , country , region , year 
  2  order by sale )
  3  former_sale
  4  from sales_fact 
  5  where country in ( 'Australia') and product = 'Xtend Memory' and week < 10
  6  order by product , country , year , sale ;

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      1998          2      29.39           0
      1998          4      29.49  16.6666667
      1998          3      29.49  16.6666667
      1998          5       29.8          50
      1998          1      58.15  66.6666667
      1998          6      58.78  83.3333333
      1998          9      58.78  83.3333333
      1999          4       40.5           0
      1999          6       40.5           0
      1999          9      53.34  33.3333333
      1999          1      53.52          50
      1999          5      80.01  66.6666667
      1999          3       94.6  83.3333333
      1999          8     103.11         100
      2000          4      46.54           0
      2000          8      46.54           0
      2000          5       46.7          40
      2000          1       46.7          40
      2000          7       70.8          80
      2000          3      93.41         100
      2001          6      22.44           0
      2001          8      46.06        12.5
      2001          3      47.24          25
      2001          7      69.96        37.5
      2001          1      92.26          50
      2001          9      92.67        62.5
      2001          5      93.44          75

      YEAR       WEEK       SALE FORMER_SALE
---------- ---------- ---------- -----------
      2001          2     118.38        87.5
      2001          4      256.7         100

29 rows selected.

Percentile_cont(大体意思求排在某个百分比时所需的数值)

也可以说是,现在说这样一个值,向分区里面插入这个值,其排名在百分之N(percent_rank为N%),求这个值。
如果有一个行的percent_rank正好等于N,那么就是这个么的值。如果没有匹配的,则要计算概率最大的。

SH@ prod> select year , week , sale ,
  2  percentile_cont(0.5) within group(order by sale desc )over(partition by year) pc ,
  3  percent_rank() over( partition by year order by sale desc ) pr
  4  from sales_fact 
  5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      1998         10     117.76     43.975          0
      1998          9      58.78     43.975 .142857143
      1998          6      58.78     43.975 .142857143
      1998          1      58.15     43.975 .428571429
      1998          5       29.8     43.975 .571428571
      1998          3      29.49     43.975 .714285714
      1998          4      29.49     43.975 .714285714
      1998          2      29.39     43.975          1
      1999          8     103.11      62.76          0
      1999          3       94.6      62.76 .142857143
      1999          5      80.01      62.76 .285714286
      1999         10         72      62.76 .428571429
      1999          1      53.52      62.76 .571428571
      1999          9      53.34      62.76 .714285714
      1999          6       40.5      62.76 .857142857
      1999          4       40.5      62.76 .857142857
      2000          3      93.41       46.7          0
      2000          7       70.8       46.7         .2
      2000          5       46.7       46.7         .4
      2000          1       46.7       46.7         .4
      2000          4      46.54       46.7         .8
      2000          8      46.54       46.7         .8
      2001          4      256.7      81.11          0
      2001          2     118.38      81.11 .111111111
      2001          5      93.44      81.11 .222222222
      2001          9      92.67      81.11 .333333333
      2001          1      92.26      81.11 .444444444

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      2001          7      69.96      81.11 .555555556
      2001         10      69.05      81.11 .666666667
      2001          3      47.24      81.11 .777777778
      2001          8      46.06      81.11 .888888889
      2001          6      22.44      81.11          1

32 rows selected.

Percentile_disc(功能与Percentile_cont大体相同)

区别在于这个函数取到的值一定是在这个分区的行中的。
如果没有匹配的,Percentile_disc会按照排序取上一个。

SH@ prod> select year , week , sale ,
  2  percentile_disc(0.5) within group(order by sale desc )over(partition by year) pc ,
  3  percent_rank() over( partition by year order by sale desc ) pr
  4  from sales_fact 
  5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      1998         10     117.76      58.15          0
      1998          9      58.78      58.15 .142857143
      1998          6      58.78      58.15 .142857143
      1998          1      58.15      58.15 .428571429
      1998          5       29.8      58.15 .571428571
      1998          3      29.49      58.15 .714285714
      1998          4      29.49      58.15 .714285714
      1998          2      29.39      58.15          1
      1999          8     103.11         72          0
      1999          3       94.6         72 .142857143
      1999          5      80.01         72 .285714286
      1999         10         72         72 .428571429
      1999          1      53.52         72 .571428571
      1999          9      53.34         72 .714285714
      1999          6       40.5         72 .857142857
      1999          4       40.5         72 .857142857
      2000          3      93.41       46.7          0
      2000          7       70.8       46.7         .2
      2000          5       46.7       46.7         .4
      2000          1       46.7       46.7         .4
      2000          4      46.54       46.7         .8
      2000          8      46.54       46.7         .8
      2001          4      256.7      92.26          0
      2001          2     118.38      92.26 .111111111
      2001          5      93.44      92.26 .222222222
      2001          9      92.67      92.26 .333333333
      2001          1      92.26      92.26 .444444444

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      2001          7      69.96      92.26 .555555556
      2001         10      69.05      92.26 .666666667
      2001          3      47.24      92.26 .777777778
      2001          8      46.06      92.26 .888888889
      2001          6      22.44      92.26          1

32 rows selected.

SH@ prod> select year , week , sale ,
  2  percentile_cont(0.5) within group(order by sale desc )over(partition by year) pc ,
  3  percent_rank() over( partition by year order by sale desc ) pr
  4  from sales_fact 
  5  where country in ('Australia') and product = 'Xtend Memory' and week < 11 ;

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      1998         10     117.76     43.975          0
      1998          9      58.78     43.975 .142857143
      1998          6      58.78     43.975 .142857143
      1998          1      58.15     43.975 .428571429
      1998          5       29.8     43.975 .571428571
      1998          3      29.49     43.975 .714285714
      1998          4      29.49     43.975 .714285714
      1998          2      29.39     43.975          1
      1999          8     103.11      62.76          0
      1999          3       94.6      62.76 .142857143
      1999          5      80.01      62.76 .285714286
      1999         10         72      62.76 .428571429
      1999          1      53.52      62.76 .571428571
      1999          9      53.34      62.76 .714285714
      1999          6       40.5      62.76 .857142857
      1999          4       40.5      62.76 .857142857
      2000          3      93.41       46.7          0
      2000          7       70.8       46.7         .2
      2000          5       46.7       46.7         .4
      2000          1       46.7       46.7         .4
      2000          4      46.54       46.7         .8
      2000          8      46.54       46.7         .8
      2001          4      256.7      81.11          0
      2001          2     118.38      81.11 .111111111
      2001          5      93.44      81.11 .222222222
      2001          9      92.67      81.11 .333333333
      2001          1      92.26      81.11 .444444444

      YEAR       WEEK       SALE         PC         PR
---------- ---------- ---------- ---------- ----------
      2001          7      69.96      81.11 .555555556
      2001         10      69.05      81.11 .666666667
      2001          3      47.24      81.11 .777777778
      2001          8      46.06      81.11 .888888889
      2001          6      22.44      81.11          1

32 rows selected.

NTILE(类型于建立直方图,不支持开窗)

将排序后的数据均匀分配到指定个数据桶中,返回桶编号,如果不能等分,各个桶中的行数最多相差一行。
在以后的处理中可以通过去除首桶或尾去除异常值。
注意:并不是按值分配的。

SH@ prod> select year , week , sale , 
  2  ntile(10) over(order by sale ) group#
  3  from sales_fact
  4  where country in ('Australia') and product = 'Xtend Memory' and year = 1998 order by year , sale;

      YEAR       WEEK       SALE     GROUP#
---------- ---------- ---------- ----------
      1998         50      28.76          1
      1998          2      29.39          1
      1998          4      29.49          1
      1998          3      29.49          1
      1998          5       29.8          2
      1998         43      57.52          2
      1998         35      57.52          2
      1998         40      57.52          2
      1998         46      57.52          3
      1998         27      57.52          3
      1998         45      57.52          3
      1998         44      57.52          3
      1998         47      57.72          4
      1998         29      57.72          4
      1998         28      57.72          4
      1998          1      58.15          4
      1998         41      58.32          5
      1998         51      58.32          5
      1998         14      58.78          5
      1998          9      58.78          5
      1998         15      58.78          6
      1998         17      58.78          6
      1998          6      58.78          6
      1998         19      58.98          6
      1998         21       59.6          7
      1998         12       59.6          7
      1998         52      86.38          7

      YEAR       WEEK       SALE     GROUP#
---------- ---------- ---------- ----------
      1998         34     115.44          8
      1998         39     115.84          8
      1998         42     115.84          8
      1998         38     115.84          9
      1998         23     117.56          9
      1998         18     117.56          9
      1998         26     117.56         10
      1998         10     117.76         10
      1998         48     172.56         10

36 rows selected.

Stddev计算标准差(方差的平方根,支持开窗)

SH@ prod> select year , week , sale , 
  2  stddev(sale) over(
  3  partition by product , country , region , year 
  4  order by sale desc 
  5  rows between 2 preceding and 2 following ) stddv
  6  from sales_fact
  7  where country in ('Australia') and product = 'Xtend Memory' and week < 10
  8  order by year , week ;

      YEAR       WEEK       SALE      STDDV
---------- ---------- ---------- ----------
      1998          1      58.15 15.8453416
      1998          2      29.39 .057735027
      1998          3      29.49 .178021534
      1998          4      29.49 12.7945918
      1998          5       29.8  15.815738
      1998          6      58.78  .36373067
      1998          9      58.78 14.3880654
      1999          1      53.52  22.178931
      1999          3       94.6 21.7319902
      1999          4       40.5 7.46550065
      1999          5      80.01 22.9761992
      1999          6       40.5 7.41317746
      1999          8     103.11 11.6825953
      1999          9      53.34 16.1305511
      2000          1       46.7 21.0022332
      2000          3      93.41 23.3589605
      2000          4      46.54 .092376043
      2000          5       46.7 10.8139207
      2000          7       70.8 22.4285538
      2000          8      46.54 .092376043
      2001          1      92.26 20.3811452
      2001          2     118.38 78.5152276
      2001          3      47.24 26.5077898
      2001          4      256.7  87.947194
      2001          5      93.44  71.309193
      2001          6      22.44 13.9900965
      2001          7      69.96 22.9124643

      YEAR       WEEK       SALE      STDDV
---------- ---------- ---------- ----------
      2001          8      46.06  19.407678
      2001          9      92.67 17.1409691

29 rows selected.

Listagg(把分区中的列按照顺序拼接起来,不支持开窗)

SH@ prod> col stddv for a60
SH@ prod> select year , week , sale , 
  2  listagg(sale , ' , ')within group(order by sale desc) over(
  3  partition by product , country , region , year  ) stddv
  4  from sales_fact
  5  where country in ('Australia') and product = 'Xtend Memory' and week < 5
  6  order by year , week ;

      YEAR       WEEK       SALE STDDV
---------- ---------- ---------- ------------------------------------------------------------
      1998          1      58.15 58.15 , 29.49 , 29.49 , 29.39
      1998          2      29.39 58.15 , 29.49 , 29.49 , 29.39
      1998          3      29.49 58.15 , 29.49 , 29.49 , 29.39
      1998          4      29.49 58.15 , 29.49 , 29.49 , 29.39
      1999          1      53.52 94.6 , 53.52 , 40.5
      1999          3       94.6 94.6 , 53.52 , 40.5
      1999          4       40.5 94.6 , 53.52 , 40.5
      2000          1       46.7 93.41 , 46.7 , 46.54
      2000          3      93.41 93.41 , 46.7 , 46.54
      2000          4      46.54 93.41 , 46.7 , 46.54
      2001          1      92.26 256.7 , 118.38 , 92.26 , 47.24
      2001          2     118.38 256.7 , 118.38 , 92.26 , 47.24
      2001          3      47.24 256.7 , 118.38 , 92.26 , 47.24
      2001          4      256.7 256.7 , 118.38 , 92.26 , 47.24

14 rows selected.

分析函数对谓词前推的影响

使用了分析函数的视图,会影响视图前推,因为分析函数的结果是跨行引用得来的,如果对数据源进行的剪裁,结果可能会不一样。

SH@ prod> create or replace view max_5_weeks_vw as 
  2  select country , product , region , year , week , sale ,
  3  max(sale) over(
  4  partition by product , country , region , year order by year , week 
  5  rows between 2 preceding and 2 following ) max_weeks_5
  6  from sales_fact ;

View created.

SH@ prod> select year , week , sale , max_weeks_5 from max_5_weeks_vw 
  2  where country in ('Australia' ) and product = 'Xtend Memory' 
  3  and region = 'Australia' and year = 2000 and week < 14 
  4  order by year , week ; 

      YEAR       WEEK       SALE MAX_WEEKS_5
---------- ---------- ---------- -----------
      2000          1       46.7       93.41
      2000          3      93.41       93.41
      2000          4      46.54       93.41
      2000          5       46.7       93.41
      2000          7       70.8       93.74
      2000          8      46.54       93.74
      2000         11      93.74       117.5
      2000         12      46.54      117.67
      2000         13      117.5      117.67

9 rows selected.

SH@ prod> explain plan for 
  2  select year , week , sale , max_weeks_5 from max_5_weeks_vw 
  3  where country in ('Australia' ) and product = 'Xtend Memory' 
  4  and region = 'Australia' and year = 2000 and week < 14 
  5  order by year , week ; 

Explained.

SH@ prod> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4167461139

--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    90 |  5220 |   310   (1)| 00:00:04 |
|*  1 |  VIEW               | MAX_5_WEEKS_VW |    90 |  5220 |   310   (1)| 00:00:04 |
|   2 |   WINDOW SORT       |                |    90 |  9450 |   310   (1)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL| SALES_FACT     |    90 |  9450 |   309   (1)| 00:00:04 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("WEEK"<14)
   3 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND
              "REGION"='Australia' AND "YEAR"=2000)

Note
-----
   - dynamic sampling used for this statement (level=2)

21 rows selected.

对比没有分析函数的视图。直接将谓词推入到视图里面。

SH@ prod> create or replace view max_5_weeks_vw1 as 
  2  select country , product , region , year , week , sale 
  3  from sales_fact ;

View created.

SH@ prod> explain plan for 
  2  select year , week , sale from max_5_weeks_vw1 
  3  where country in ('Australia' ) and product = 'Xtend Memory' 
  4  and region = 'Australia' and year = 2000 and week < 14 
  5  order by year , week ;

Explained.

SH@ prod> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1978576542

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |   105 |   310   (1)| 00:00:04 |
|   1 |  SORT ORDER BY     |            |     1 |   105 |   310   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| SALES_FACT |     1 |   105 |   309   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COUNTRY"='Australia' AND "PRODUCT"='Xtend Memory' AND
              "REGION"='Australia' AND "YEAR"=2000 AND "WEEK"<14)

Note