oracle 分析函数第二,Oracle分析函数(二)

╰半夏微凉° 2022-10-21 04:56 313阅读 0赞

Oracle分析函数(二)

1.AVG

功能描述：用于计算一个组和数据窗口内表达式的平均值。

SAMPLE：下面的例子中列c\_mavg计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；

SELECT manager\_id, last\_name, hire\_date, salary,

AVG(salary) OVER (PARTITION BY manager\_id ORDER BY hire\_date

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)AS c\_mavg

FROM employees;

MANAGER\_ID LAST\_NAME HIRE\_DATE SALARY C\_MAVG

\---------- ------------------------- --------- ---------- ----------

100 Kochhar 21-SEP-89 17000 17000

100 De Haan 13-JAN-93 17000 15000

100 Raphaely 07-DEC-94 11000 11966.6667

100 Kaufling 01-MAY-95 7900 10633.3333

100 Hartstein 17-FEB-96 13000 9633.33333

100 Weiss 18-JUL-96 8000 11666.6667

100 Russell 01-OCT-96 14000 11833.3333

2.COUNT

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

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

Markle 2200 107 3 2

Philtanker 2200 107 3 2

Landry 2400 107 5 8

Gee 2400 107 5 8

Colmenares 2500 107 11 10

Patel 2500 107 11 10

3.DENSE\_RANK

功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号(认为null时相等的)。密集的序列返回的时没有间隔的数

SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号(注意与RANK函数的区别)

SELECT d.department\_id , e.last\_name, e.salary, DENSE\_RANK()

OVER (PARTITION BY e.department\_id ORDER BY e.salary) as drank

FROM employees e, departments d

WHERE e.department\_id = d.department\_id

AND d.department\_id IN ('60', '90');

DEPARTMENT\_ID LAST\_NAME SALARY DRANK

\------------- ------------------------- ---------- ----------

60 Lorentz 4200 1

60 Austin 4800 2

60 Pataballa 4800 2

60 Ernst 6000 3

60 Hunold 9000 4

90 Kochhar 17000 1

90 De Haan 17000 1

90 King 24000 2

RANK()与DENSE\_RANK()用法相当，但是有一个区别：DENSE\_RANK在处理相同的等级时，等级的数值不会跳过,RANK()则跳过。

RANK

SAMPLE：下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号(注意与DENSE\_RANK函数的区别)

SELECT d.department\_id , e.last\_name, e.salary, RANK()

OVER (PARTITION BY e.department\_id ORDER BY e.salary) as drank

FROM employees e, departments d

WHERE e.department\_id = d.department\_id

AND d.department\_id IN ('60', '90');

DEPARTMENT\_ID LAST\_NAME SALARY DRANK

\------------- ------------------------- ---------- ----------

60 Lorentz 4200 1

60 Austin 4800 2

60 Pataballa 4800 2

60 Ernst 6000 4

60 Hunold 9000 5

90 Kochhar 17000 1

90 De Haan 17000 1

90 King 24000 3

4.FIRST

功能描述：从DENSE\_RANK返回的集合中取出排在最前面的一个值的行(可能多行，因为值可能相等)，因此完整的语法需要在开始处加上一个集合函数以从中取出记录SAMPLE.

下面例子中DENSE\_RANK按部门分区，再按佣金commission\_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值.

SELECT last\_name, department\_id, salary,

MIN(salary) KEEP (DENSE\_RANK FIRST ORDER BY commission\_pct)

OVER (PARTITION BY department\_id) "Worst",

MAX(salary) KEEP (DENSE\_RANK LAST ORDER BY commission\_pct)

OVER (PARTITION BY department\_id) "Best"

FROM employees

WHERE department\_id in (20,80)

ORDER BY department\_id, salary;

LAST\_NAME DEPARTMENT\_ID SALARY Worst Best

\------------------------- ------------- ---------- ---------- ----------

Fay 20 6000 6000 13000

Hartstein 20 13000 6000 13000

Kumar 80 6100 6100 14000

Banda 80 6200 6100 14000

Johnson 80 6200 6100 14000

Ande 80 6400 6100 14000

Lee 80 6800 6100 14000

Tuvault 80 7000 6100 14000

Sewall 80 7000 6100 14000

Marvins 80 7200 6100 14000

Bates 80 7300 6100 14000

5.FIRST\_VALUE

功能描述：返回组中数据窗口的第一个值。

SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字

SELECT department\_id, last\_name, salary, FIRST\_VALUE(last\_name)

OVER (PARTITION BY department\_id ORDER BY salary ASC ) AS lowest\_sal

FROM employees

WHERE department\_id in(20,30);

DEPARTMENT\_ID LAST\_NAME SALARY LOWEST\_SAL

\------------- ------------------------- ---------- --------------

20 Fay 6000 Fay

20 Hartstein 13000 Fay

30 Colmenares 2500 Colmenares

30 Himuro 2600 Colmenares

30 Tobias 2800 Colmenares

30 Baida 2900 Colmenares

30 Khoo 3100 Colmenares

30 Raphaely 11000 Colmenares

6.LAST

功能描述：从DENSE\_RANK返回的集合中取出排在最后面的一个值的行(可能多行，因为值可能相等)，因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SAMPLE：下面例子中DENSE\_RANK按部门分区，再按佣金commission\_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值

SELECT last\_name, department\_id, salary,

MIN(salary) KEEP (DENSE\_RANK FIRST ORDER BY commission\_pct)

OVER (PARTITION BY department\_id) "Worst",

MAX(salary) KEEP (DENSE\_RANK LAST ORDER BY commission\_pct)

OVER (PARTITION BY department\_id) "Best"

FROM employees

WHERE department\_id in (20,80)

ORDER BY department\_id, salary;

LAST\_NAME DEPARTMENT\_ID SALARY Worst Best

\------------------------- ------------- ---------- ---------- ----------

Fay 20 6000 6000 13000

Hartstein 20 13000 6000 13000

Kumar 80 6100 6100 14000

Banda 80 6200 6100 14000

Johnson 80 6200 6100 14000

Ande 80 6400 6100 14000

Lee 80 6800 6100 14000

Tuvault 80 7000 6100 14000

Sewall 80 7000 6100 14000

Marvins 80 7200 6100 14000

Bates 80 7300 6100 14000

7.LAST\_VALUE

功能描述：返回组中数据窗口的最后一个值。

SAMPLE：下面例子计算按部门分区按薪水排序的数据窗口的最后一个值对应的名字，如果薪水的最后一个值有多个，则从多个对应的名字中取缺省排序的最后一个名字

SELECT department\_id, last\_name, salary, LAST\_VALUE(last\_name)

OVER(PARTITION BY department\_id ORDER BY salary) AS highest\_sal

FROM employees

WHERE department\_id in(20,30);

DEPARTMENT\_ID LAST\_NAME SALARY HIGHEST\_SAL

\------------- ------------------------- ---------- ------------

20 Fay 6000 Fay

20 Hartstein 13000 Hartstein

30 Colmenares 2500 Colmenares

30 Himuro 2600 Himuro

30 Tobias 2800 Tobias

30 Baida 2900 Baida

30 Khoo 3100 Khoo

30 Raphaely 11000 Raphaely

8.LAG

功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值(默认返回的是组中第一行)，其相反的函数是LEAD

SAMPLE：下面的例子中列prev\_sal返回按hire\_date排序的前1行的salary值

SELECT last\_name, hire\_date, salary,

LAG(salary, 1, 0) OVER (ORDER BY hire\_date) AS prev\_sal

FROM employees

WHERE job\_id = 'PU\_CLERK';

LAST\_NAME HIRE\_DATE SALARY PREV\_SAL

\------------------------- ---------- ---------- ----------

Khoo 18-5月 -95 3100 0

Tobias 24-7月 -97 2800 3100

Baida 24-12月-97 2900 2800

Himuro 15-11月-98 2600 2900

Colmenares 10-8月 -99 2500 2600

9.LEAD

功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值(默认返回的是组中第一行)

SAMPLE：下面的例子中每行的"NextHired"返回按hire\_date排序的下一行的hire\_date值

SELECT last\_name, hire\_date,

LEAD(hire\_date, 1) OVER (ORDER BY hire\_date) AS "NextHired"

FROM employees WHERE department\_id = 30;

LAST\_NAME HIRE\_DATE NextHired

\------------------------- --------- ---------

Raphaely 07-DEC-94 18-MAY-95

Khoo 18-MAY-95 24-JUL-97

Tobias 24-JUL-97 24-DEC-97

Baida 24-DEC-97 15-NOV-98

Himuro 15-NOV-98 10-AUG-99

Colmenares 10-AUG-99

10.SUM

功能描述：该函数计算组中表达式的累积和。

SAMPLE：下例计算同一经理下员工的薪水累积值

SELECT manager\_id, last\_name, salary,

SUM (salary) OVER (PARTITION BY manager\_id ORDER BY salary

RANGE UNBOUNDED PRECEDING) l\_csum

FROM employees

WHERE manager\_id in (101,103,108);

MANAGER\_ID LAST\_NAME SALARY L\_CSUM

\---------- ------------------------- ---------- ----------

101 Whalen 4400 4400

101 Mavris 6500 10900

101 Baer 10000 20900

101 Greenberg 12000 44900

101 Higgins 12000 44900

103 Lorentz 4200 4200

103 Austin 4800 13800

103 Pataballa 4800 13800

103 Ernst 6000 19800

108 Popp 6900 6900

108 Sciarra 7700 14600

108 Urman 7800 22400

108 Chen 8200 30600

108 Faviet 9000 39600

11.MAX

功能描述：在一个组中的数据窗口中查找表达式的最大值。

SAMPLE：下面例子中dept\_max返回当前行所在部门的最大薪水值

SELECT department\_id, last\_name, salary,

MAX(salary) OVER (PARTITION BY department\_id) AS dept\_max

FROM employees WHERE department\_id in (10,20,30);

DEPARTMENT\_ID LAST\_NAME SALARY DEPT\_MAX

\------------- ------------------------- ---------- ----------

10 Whalen 4400 4400

20 Hartstein 13000 13000

20 Fay 6000 13000

30 Raphaely 11000 11000

30 Khoo 3100 11000

30 Baida 2900 11000

30 Tobias 2800 11000

30 Himuro 2600 11000

30 Colmenares 2500 11000

12.MIN

功能描述：在一个组中的数据窗口中查找表达式的最小值。

SAMPLE：下面例子中dept\_min返回当前行所在部门的最小薪水值

SELECT department\_id, last\_name, salary,

MIN(salary) OVER (PARTITION BY department\_id) AS dept\_min

FROM employees WHERE department\_id in (10,20,30);

DEPARTMENT\_ID LAST\_NAME SALARY DEPT\_MIN

\------------- ------------------------- ---------- ----------

10 Whalen 4400 4400

20 Hartstein 13000 6000

20 Fay 6000 6000

30 Raphaely 11000 2500

30 Khoo 3100 2500

30 Baida 2900 2500

30 Tobias 2800 2500

30 Himuro 2600 2500

30 Colmenares 2500 2500

oracle 分析函数第二,Oracle分析函数(二)

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle分析函数

相关 oracle 分析函数第二,Oracle分析函数(二)

相关 Oracle分析函数使用总结

相关 Oracle分析函数——统计分析函数

相关 [Oracle]分析函数——函数用法

相关 [Oracle]分析函数——函数列表

相关【Oracle】分析函数Over()

相关 ORACLE统计分析函数

相关 oracle分析函数——over

相关 oracle分析函数——keep