分析函数进阶（二）

最新推荐文章于 2022-06-05 15:11:13 发布
似剑非攻_墨眉无锋
最新推荐文章于 2022-06-05 15:11:13 发布
阅读量529
点赞数 1
分类专栏： oracle
oracle 专栏收录该内容
15 篇文章 0 订阅
订阅专栏
分析函数（二）

二、窗口函数

--移动窗口详解
analytic_function([ arguments ])
   OVER (analytic_clause)

 where analytic_clause =
     [ query_partition_clause ]
     [ order_by_clause [ windowing_clause ] ]

and query_partition_clause =
    PARTITION BY
      { value_expr[, value_expr ]...
      }

 and windowing_clause =
     { ROWS | RANGE }
     { BETWEEN
       { UNBOUNDED PRECEDING
     | CURRENT ROW
     | value_expr { PRECEDING | FOLLOWING }
     } 
     AND
     { UNBOUNDED FOLLOWING
     | CURRENT ROW
     | value_expr { PRECEDING | FOLLOWING }
     }
	}
   | { UNBOUNDED PRECEDING
     | CURRENT ROW
     | value_expr PRECEDING
     }
   

   
--名词解释
ROWS   --指定窗口里的记录

RANGE  --窗口的逻辑偏移量

BETWEEN ... AND  --指定窗口的开始和结束点
                 --如果你省略了BETWEEN ... AND,那么只有开始点且默认当前行为结束点

UNBOUNDED PRECEDING --窗口的第一行，该值只能做开始点

UNBOUNDED FOLLOWING --窗口的最后一行，该值只能做结束点

CURRENT ROW         --当前行

支持窗口子句的聚合函数如下：（适合oracle 11.2.0.4）
AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
LISTAGG
MAX
MEDIAN
MIN
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
SYS_XMLAGG
VAR_POP
VAR_SAMP
VARIANCE
XMLAGG

注意事项：(第5点是我总结，其他为官方明文）

1.如果开始点是 value_expr FOLLOWING ，那么结束点一定是value_expr FOLLOWING

2.如果开始点是 value_expr PRECEDING ，那么结束点一定是value_expr PRECEDING

3.如果你指定了ROWS ，那么需要注意一下2点：
1）value_expr 是一个物理偏移量，它必须是常量或表达式，且计算结果必须是正数
2）如果value_expr是开始点，那么它必须是在结束点的前面，换句话说就是小于结束点。

4.如果你指定了RANGE,那么需要注意一下几点：
1）value_expr是一个逻辑偏移量，它必须是常量或能得到一个正数结果的表达式或间隔常量。
2）order by子句里名只能指定一个表达式
3）如果value_expr计算得到是一个数字，order by子句必须是数字或日期数据类型
4）如果value_expr计算得到是间隔常量，那么order by子句必须是日期数据类型
5）如果你省略了整个窗口子句，那么默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

5.当窗口大小于分组大小时,只有指定order by时是唯一排序方式，才能保证结果不变(换句话说就是order by指定的列组合起来能做主键才行)
6.如果指定了ROWS或RANGE ，那么order by必须指定。

SQL> select deptno,ename,sal,
sum(sal) over (partition by deptno  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) cul_sal 
from emp
group by deptno,ename,sal;  
sum(sal) over (partition by deptno  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) cul_sal
*
ERROR at line 2:
ORA-30485: missing ORDER BY expression in the window specification


SQL> 

SQL> select deptno,ename,sal,
sum(sal) over (partition by deptno order by deptno,ename,sal rows 1 FOLLOWING) cul_sal 
from emp
group by deptno,ename,sal;   2    3    4  
sum(sal) over (partition by deptno order by deptno,ename,sal rows 1 FOLLOWING) cul_sal
                                                                    *
ERROR at line 2:
ORA-00905: missing keyword

SQL> 

SQL> select deptno,ename,sal,
sum(sal) over (partition by deptno rows BETWEEN 1 PRECEDING AND 1 FOLLOWING ) cul_sal from emp
group by deptno,ename,sal;
sum(sal) over (partition by deptno rows BETWEEN 1 PRECEDING AND 1 FOLLOWING ) cul_sal from emp
*
ERROR at line 2:
ORA-30485: missing ORDER BY expression in the window specification


SQL> 



--累积聚合函数
select deptno,ename,sal,
sum(sal) over (partition by deptno order by deptno,ename,sal ROWS UNBOUNDED PRECEDING) cul_sal from emp
group by deptno,ename,sal;

    DEPTNO ENAME             SAL    CUL_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       2450
        10 KING             5000       7450
        10 MILLER           1300       8750
        20 ADAMS            1100       1100
        20 FORD             3000       4100
        20 JONES            2975       7075
        20 SCOTT            3000      10075
        20 SMITH             800      10875
        30 ALLEN            1600       1600
        30 BLAKE            2850       4450
        30 JAMES             950       5400
        30 MARTIN           1250       6650
        30 TURNER           1500       8150
        30 WARD             1250       9400

14 rows selected.


SQL> select deptno,ename,sal,
sum(sal) over (partition by deptno order by deptno,ename,sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) cul_sal 
--此时相当于sum()
from emp
group by deptno,ename,sal; 

    DEPTNO ENAME             SAL    CUL_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       8750
        10 KING             5000       8750
        10 MILLER           1300       8750
        20 ADAMS            1100      10875
        20 FORD             3000      10875
        20 JONES            2975      10875
        20 SCOTT            3000      10875
        20 SMITH             800      10875
        30 ALLEN            1600       9400
        30 BLAKE            2850       9400
        30 JAMES             950       9400
        30 MARTIN           1250       9400
        30 TURNER           1500       9400
        30 WARD             1250       9400

14 rows selected.

SQL> 

SQL>select deptno,ename,sal,
sum(sal) over (partition by deptno  ) cul_sal from emp
group by deptno,ename,sal;

    DEPTNO ENAME             SAL    CUL_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       8750
        10 KING             5000       8750
        10 MILLER           1300       8750
        20 ADAMS            1100      10875
        20 FORD             3000      10875
        20 JONES            2975      10875
        20 SCOTT            3000      10875
        20 SMITH             800      10875
        30 ALLEN            1600       9400
        30 BLAKE            2850       9400
        30 JAMES             950       9400
        30 MARTIN           1250       9400
        30 TURNER           1500       9400
        30 WARD             1250       9400

14 rows selected.



--value_expr例句




SQL>select deptno,ename,sal,
sum(sal) over (partition by deptno order by deptno,ename,sal  rows BETWEEN 1 PRECEDING AND 1 FOLLOWING ) cul_sal 
--分组里当前行的前后各一行加上当前行的值得总和
from emp
group by deptno,ename,sal;

    DEPTNO ENAME             SAL    CUL_SAL
---------- ---------- ---------- ----------
        10 CLARK            2450       7450    
        10 KING             5000       8750
        10 MILLER           1300       6300
        20 ADAMS            1100       4100
        20 FORD             3000       7075
        20 JONES            2975       8975
        20 SCOTT            3000       6775
        20 SMITH             800       3800
        30 ALLEN            1600       4450
        30 BLAKE            2850       5400
        30 JAMES             950       5050
        30 MARTIN           1250       3700
        30 TURNER           1500       4000
        30 WARD             1250       2750

14 rows selected.
似剑非攻_墨眉无锋
关注
1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分析函数进阶（二）

分析函数（二）二、窗口函数--移动窗口详解analytic_function([ arguments ]) OVER (analytic_clause) where analytic_clause = [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]and query_
复制链接

扫一扫
专栏目录