oracle开窗函数到底有哪些啊，谁能一一举个例不

Question

oracle开窗函数到底有哪些啊，谁能一一举个例不，应该有好些个，可是一直找不齐Oracle

Answer 1

楼主，像这种问题就不改问，百度一下一定能找全的给你个地址，自已去看 开窗函数http://www.meirirm.com/meirirm/index.php/category/it

Answer 2

窗口函数
窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。
1）.计算累积和
下面这个例子是计算出2003年从1月到12月的累积销量。
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   SUM(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 月累积销量  --定义了窗口的起点和终点
  5  FROM all_sales
  6  WHERE year=2003
  7  GROUP BY month
  8  ORDER BY month;      月份   月总销量 月累积销量
---------- ---------- ----------
         1   95525.55   95525.55
         2   116671.6  212197.15
         3  160307.92  372505.07
         4   175998.8  548503.87
         5  154349.44  702853.31
         6  124951.36  827804.67
         7  170296.16  998100.83
         8  212735.68 1210836.51
         9  199609.68 1410446.19
        10  264480.79 1674926.98
        11  160221.98 1835148.96      月份   月总销量 月累积销量
---------- ---------- ----------
        12  137336.17 1972485.13已选择12行。
那如果是计算6月到12月的累积销量呢！！！
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   SUM(SUM(amount)) OVER
  4    (ORDER BY month ROWS UNBOUNDED PRECEDING) AS 月累积销量
  5  FROM all_sales
  6  WHERE year=2003
  7  AND month BETWEEN 6 AND 12       --6和12换为相应的月就可以了
  8  GROUP BY month
  9  ORDER BY month;      月份   月总销量 月累积销量
---------- ---------- ----------
         6  124951.36  124951.36
         7  170296.16  295247.52
         8  212735.68   507983.2
         9  199609.68  707592.88
        10  264480.79  972073.67
        11  160221.98 1132295.65
        12  137336.17 1269631.82已选择7行。
2）.计算移动平均值
计算本月与前三个月之间销量的移动平均值
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   AVG(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS 三月平均累积销量
  5  FROM all_sales
  6  WHERE year=2003
  7  GROUP BY month
  8  ORDER BY month;      月份   月总销量 三月平均累积销量
---------- ---------- ----------------
         1   95525.55         95525.55
         2   116671.6       106098.575      --前两月的平均销量
         3  160307.92       124168.357      --三月
         4   175998.8       137125.968      --本月加前三月
         5  154349.44        151831.94
         6  124951.36        153901.88
         7  170296.16        156398.94
         8  212735.68        165583.16
         9  199609.68        176898.22
        10  264480.79       211780.578
        11  160221.98       209262.033      月份   月总销量 三月平均累积销量
---------- ---------- ----------------
        12  137336.17       190412.155已选择12行。
3）.计算中心平均值
计算当前月份前、后各一个月内的销量移动平均值：
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   AVG(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 平均累积销量
  5  FROM all_sales
  6  WHERE year=2003
  7  GROUP BY month
  8  ORDER BY month;      月份   月总销量 平均累积销量
---------- ---------- ------------
         1   95525.55   106098.575
         2   116671.6   124168.357
         3  160307.92   150992.773
         4   175998.8   163552.053
         5  154349.44   151766.533
         6  124951.36   149865.653
         7  170296.16   169327.733
         8  212735.68    194213.84
         9  199609.68   225608.717
        10  264480.79    208104.15
        11  160221.98   187346.313      月份   月总销量 平均累积销量
---------- ---------- ------------
        12  137336.17   148779.075
4.FIRST-VALUE（）和LAST-VALUE（）函数的使用：
下面这个例子是用FIRST-VALUE（）和LAST-VALUE（）来获得前一个月和后一个月的销量：
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   FIRST_VALUE(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 前月销量,
  5   LAST_VALUE(SUM(amount)) OVER
  6    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 后月销量
  7  FROM all_sales
  8  WHERE year=2003
  9  GROUP BY month
 10  ORDER BY month;      月份   月总销量   前月销量   后月销量
---------- ---------- ---------- ----------
         1   95525.55   95525.55   116671.6
         2   116671.6   95525.55  160307.92
         3  160307.92   116671.6   175998.8
         4   175998.8  160307.92  154349.44
         5  154349.44   175998.8  124951.36
         6  124951.36  154349.44  170296.16
         7  170296.16  124951.36  212735.68
         8  212735.68  170296.16  199609.68
         9  199609.68  212735.68  264480.79
        10  264480.79  199609.68  160221.98
        11  160221.98  264480.79  137336.17      月份   月总销量   前月销量   后月销量
---------- ---------- ---------- ----------
        12  137336.17  160221.98  137336.17已选择12行。
还有一个例子是计算当前月与前、后各一个月的比率
SQL> SELECT
  2   month 月份,SUM(amount) AS 月总销量,
  3   SUM(amount)/FIRST_VALUE(SUM(amount)) OVER
  4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与前月比率,
  5   SUM(amount)/LAST_VALUE(SUM(amount)) OVER
  6    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与后月比率
  7  FROM all_sales
  8  WHERE year=2003
  9  GROUP BY month
 10  ORDER BY month;      月份   月总销量 当月与前月比率 当月与后月比率
---------- ---------- -------------- --------------
         1   95525.55              1     .818755807
         2   116671.6     1.22136538     .727796855
         3  160307.92     1.37400978     .910846665
         4   175998.8     1.09787963     1.14026199
         5  154349.44     .876991434     1.23527619
         6  124951.36     .809535558     .733729756
         7  170296.16     1.36289961     .800505867
         8  212735.68     1.24921008     1.06575833
         9  199609.68      .93829902     .754722791
        10  264480.79      1.3249898     1.65071478
        11  160221.98     .605798175     1.16664081      月份   月总销量 当月与前月比率 当月与后月比率
---------- ---------- -------------- --------------
        12  137336.17     .857161858              1已选择12行。

Answer 3

十一、分析函数
语法：Function名称([参数]) OVER ([partition 子句][ order 子句] [window 子句])
1、理解partition by 子句、order by 子句、window 子句
①partition by 子句：指定按照哪一个列进行分组，然后再计算
      select ename ,sal,sum(sal)over(partition by deptno) from emp
②order by子句：
      select deptno,sal,sum(sal) over(order by sal) from emp 按照sal排序后，连续求合
③window 子句（窗口子句）
⑴rows n preceding：按照sal排序后，sum(m)项的值等于sal中第m+第(m-1)+第(m-2)+...+第(m-n)值的和(对sum函数分析，其他函数类似)
          select deptno,sal,sum(sal) over(order by sal rows 2 preceding) from emp          
⑵range unbounded preceding
   不指定range 默认就是range unbounded preceding 无限大
   range窗口只对数字和日期类型有效
         select deptno,sal,sum(sal) over(order by sal ) from emp
     等价于selectdeptno,sal,sum(sal) over(order by sal range unbounded preceding ) from emp
   2、sum(….)  over(….)
3、rank（）over(…)、dense_rank()  over(…)
  原理：按照分组从上到下排列，形成一个序列，序列从1开始，往后累加。rank()和dense_rank()的唯一区别是当两行的值相同时，rank()则得到同样的序号，后面的则跳跃，如1、1、3、4、4、6、….、n；而dense_rank()则没有跳跃值.，如1、2、3、4、4、5、6、6、…、n。。over（）中必须有order by子句
   例如：计算出每个员工在自己部门中工资的排名
 select ename,sal,deptno ,rank() over(partition by deptno order by sal desc) 排名 from emp
4、rownumber（） over(…)：按照分组从上到下排列序号，如1、2、3、4、…、n
5、lag（）上一个，lead（）下一个
 select deptno, sal, lag(sal)over(partition by deptno order by sal)上一个,
lead(sal)over(partition by deptno order by sal)  下一个 from emp
6、first_value() 第一个，last_value() 最后一个
      select deptno ,sal ,
first_value(sal) over(partition by deptno  order by sal) 第一个,
last_value(sal) over(partition by deptno   ) 最后一个 
from emp