DB2 V9.5 ROWNUMBER/RANK 学习笔记

zhangzongjun · 发表于 2008-11-20 12:03

昨天发现对 ROWNUMBER/RANK 函数使用不熟练，
特别加强了一下，
以下通过各个实例，来强化对 ROWNUMBER/RANK 函数
用法的理解，
关键部分已用蓝色标出，
特放上来与大家分享

ROWNUMBER / ROW_NUMBER
RANK / DENSE_RANK

测试环境：
DB2 V9.5 SAMPLE

求：员工的收入排名（收入大于30000的）
1. 按名称排
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
   RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
   FROM EMPLOYEE WHERE SALARY+BONUS > 30000
   ORDER BY LASTNAME
也可以按收入排
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
   DENSE_RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
   FROM EMPLOYEE WHERE SALARY+BONUS > 30000
   ORDER BY RANK_SALARY

SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
   DENSE_RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
   FROM EMPLOYEE WHERE SALARY+BONUS > 30000
   ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)

2. 求按部门员工的平均收入的排名
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
   RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
   FROM EMPLOYEE
   GROUP BY WORKDEPT
   ORDER BY RANK_AVG_SAL

3. 求每个部门内部按教育程度的排名
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL,
   DENSE_RANK() OVER
      (PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
   FROM EMPLOYEE
   ORDER BY WORKDEPT, RANK_EDLEVEL

4. 为查询提供结果提供ROWNUMBER
SELECT LASTNAME, SALARY,
      ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME

5. 求收入最高的前5名员工
SELECT
EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
FROM (
SELECT EMPNO, LASTNAME, FIRSTNME,
      SALARY+BONUS AS TOTAL_SALARY,
      RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE
) AS RANKED_EMPLOYEE
WHERE RANK_SALARY < 6
ORDER BY RANK_SALARY

6. 求每个部内的员工与本部门内工资最相近的员工之间的工资差
SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY,
LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT
      ORDER BY SALARY) - SALARY
         AS DELTA_SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT, SALARY
或者：
SELECT EMPNO, WORKDEPT, LASTNAME, FIRSTNME, JOB, SALARY,
SALARY - LEAD(SALARY, 1) OVER (PARTITION BY WORKDEPT
      ORDER BY SALARY DESC)
         AS DELTA_SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT ASC, SALARY DESC

参考：
LEAD 函数语法：
lead-function

|--LEAD--(--expression------------------------------------------>
>--+-------------------------------------------------------------+--)--|
'-,--offset--+----------------------------------------------+-'
            '-,--default-value--+------------------------+-'
                                 '-,--+-'RESPECT NULLS'-+-'
                                       '-'IGNORE NULLS'--'

说明：
The LEAD function returns the expression value for the row at offset rows after the current row. The offset must be a positive integer (SQLSTATE 42815). An offset value of 0 means the current row. If a window-partition-clause is specified, offset means offset rows after the current row and within the current partition. If offset is not specified, the value 1 is used. If default-value (which can be an expression) is specified, it will be returned if the offset goes beyond the scope of the current partition. Otherwise, the null value is returned. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all rows are null, default-value (or the null value if default-value was not specified) is returned.

7. 计算出每个员工的薪资与相同工作的、最早入职的员工的薪资的差
SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY,
FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
      ORDER BY HIREDATE) AS FIRST_SALARY,
SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
      ORDER BY HIREDATE) AS DELTA_SALARY
FROM EMPLOYEE
ORDER BY JOB, HIREDATE

参考：
first-value-function：

|--FIRST_VALUE--(--expression--+------------------------+--)----|
                           '-,--+-'RESPECT NULLS'-+-'
                                 '-'IGNORE NULLS'--'

last-value-function：

|--LAST_VALUE--(--expression--+------------------------+--)-----|
                           '-,--+-'RESPECT NULLS'-+-'
                                 '-'IGNORE NULLS'--'

说明：
The FIRST_VALUE function returns the expression value for the first row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, FIRST_VALUE returns the null value.

The LAST_VALUE function returns the expression value for the last row in an OLAP window. If 'IGNORE NULLS' is specified, all rows where the expression value for the row is the null value are not considered in the calculation. If 'IGNORE NULLS' is specified and all values in the OLAP window are null, LAST_VALUE returns the null value.

The data type of the result of FIRST_VALUE, LAG, LAST_VALUE, and LEAD is the data type of the expression. The result can be null.