Oracle over函数的学习笔记五最大最小值函数

最新推荐文章于 2024-09-14 19:55:29 发布

cuiganglan0701

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量137

点赞数

文章标签：数据库

求最大最小值函数：

FIRST

功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最高的值。

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

SELECT last_name lname, department_id depid, 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;

LNAME DEPID 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

……

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

SELECT department_id depid, last_name lname, 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);

DEPID LNAME 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

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

SELECT department_id depid, last_name lname, salary

,FIRST_VALUE(last_name) OVER (PARTITION BY department_id ORDER BY salary ASC) AS lowest_nam

,LAST_VALUE(last_name)

OVER(PARTITION BY department_id ORDER BY salary) AS highest_nam

,LAST_VALUE(salary)

OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal

FROM employees

WHERE department_id in(20,30);

DEPID LNAME SALARY LOWEST_NAM HIGHEST_NAM HIGHEST_SAL

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

20 Fay 6000 Fay Fay 6000

20 Hartstein 13000 Fay Hartstein 13000

30 Colmenares 2500 Colmenares Colmenares 2500

30 Himuro 2600 Colmenares Himuro 2600

30 Baida 2800 Colmenares Tobias 2800

30 Tobias 2800 Colmenares Tobias 2800

30 Khoo 3100 Colmenares Khoo 3100

30 Raphaely 11000 Colmenares Raphaely 11000

在同样的salary 情况下就能看出来。

****last_value()函数的默认的窗口是范围是rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前窗口组中的第一行开始向后进行比较，所以会出现上边的结果。

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

max, min函数如果不加order子句，窗口为每个partition分组内的数据记录集合，如果加了order子句，则默认窗口为rows between unbounded preceding and current row.

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

SELECT department_id depid, last_name lname, salary,
MAX(salary) OVER (PARTITION BY department_id) AS dept_max

,MIN(salary) OVER (PARTITION BY department_id) AS dept_min

FROM employees WHERE department_id in (10,20,30);

DEPID LNAME    SALARY DEPT_MAX    DEPT_MAX
----- ------ -------- ---------- --------
10    Whalen    4400   4400       4400
20   Hartstein 13000 13000      6000
20    Fay      6000   13000      6000
30    Raphaely 11000 11000      2500
30    Khoo      3100   11000      2500
30    Baida     2900   11000      2500
30    Tobias   2800   11000      2500
30    Himuro    2600   11000      2500
30    Colmenares 2500 11000      2500

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

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