Oracle over函数的学习笔记四 计数和取相对行函数

计数函数：

COUNT功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。

下面例子中使用的表来自Oracle自带的HR用户下的表，如果没有安装该用户，可以在SYS用户下运行 $ORACLE_HOME/demo/schema/human_resources/hr_main.sql来创建。少数几个例子需要访问SH用户下的表，如果没有安装该用户，可以在SYS用户下运行 $ORACLE_HOME/demo/schema/sales_history/sh_main.sql来创建。缺省是在HR用户下运行例子。

SAMPLE：下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水。例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；cnt2值相当于小于等于当前行的SALARY值的所有行数

SELECT last_name, salary, COUNT(*) OVER () AS cnt1

       ,COUNT(*) OVER (ORDER BY salary) AS cnt2

       ,COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING   AND 150 FOLLOWING) AS cnt3

 FROM employees;       

LAST_NAME             SALARY       CNT1       CNT2       CNT3

------------------------- ---------- ---------- ---------- ----------

Olson                   2100        107          1          3

Philtanker              2200        107          3          2

Markle                  2200        107          3          2

Landry                  2400        107          5          8

Gee                     2400        107          5          8

Perkins                 2500        107         11         10

Colmenares              2500        107         11         10

Patel                   2500        107         11         10

Vargas                  2500        107         11         10

……

==================================================================
利用over实现的分页功能：
--假设code1,code2为用来分页的KEY，每页显示5行数据
select code1,code2,code3,
ceil(count(*) over(partition by code1,code2 order by rownum)/5),
count(*) over(partition by code1,code2)
from ma_kbn order by code1,code2

取出相对行的内容：

利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据。

LAG
功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）. LEAD则与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行）
SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值, last_sal 返回下一行的salary值，"NextHired"返回按hire_date排序的下一行的hire_date值

SELECT last_name, hire_date, salary,

       LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal

      ,LEAD(salary,1,0) OVER (ORDER BY hire_date) AS last_sal

      ,LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NeHired"

  FROM employees

 WHERE job_id = 'PU_CLERK';

LAST_NAME       HIRE_DATE          SALARY   PREV_SAL   LAST_SAL NeHired

------------ -------------- ---------- ---------- ---------- --------------

Khoo           18-5月 -95         3100          0       2800 24-7月 -97

Tobias         24-7月 -97         2800       3100       2800 24-12月-97

Baida          24-12月-97         2800       2800       2600 15-11月-98

Himuro         15-11月-98         2600       2800       2500 10-8月 -99

Colmenares     10-8月 -99         2500       2600          0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7177735/viewspace-710101/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7177735/viewspace-710101/