Oracle analytic function-first_value and last_value

First_value返回排序结果集中的第一个值,如果结果集中第一个值是NULL,则函数返回NULL,当然你也可以指定IGNORE NULLS.
示例:

SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         first_value(salary) ignore nulls over (partition by department_id order by salary) as sal_lowest_dep
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LOWEST_DEP
------------- ----------------------------------- ------------------- ---------- --------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400           4400
           20 Pat Fay                             2005-08-17 00:00:00       6000           6000
           20 Michael Hartstein                   2004-02-17 00:00:00      13000           6000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500           2500
           30 Alexander Khoo                      2003-05-18 00:00:00       2600           2500
           30 Guy Himuro                          2006-11-15 00:00:00       2600           2500
           30 Sigal Tobias                        2005-07-24 00:00:00       2600           2500
           30 Shelli Baida                        2005-12-24 00:00:00       2900           2500
           30 Den Raphaely                        2002-12-07 00:00:00      11000           2500
           40 Susan Mavris                        2002-06-07 00:00:00       6500           6500
已选择10行。
SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         first_value(salary) ignore nulls over (partition by department_id order by salary rows 1 preceding) as sal_lowest_dep---类似lag函数
  6   from employees
  7   where department_id <= 50;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LOWEST_DEP
------------- ----------------------------------- ------------------- ---------- --------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400           4400
           20 Pat Fay                             2005-08-17 00:00:00       6000           6000
           20 Michael Hartstein                   2004-02-17 00:00:00      13000           6000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500           2500
           30 Guy Himuro                          2006-11-15 00:00:00       2600           2500
           30 Sigal Tobias                        2005-07-24 00:00:00       2600           2600
           30 Alexander Khoo                      2003-05-18 00:00:00       2600           2600
           30 Shelli Baida                        2005-12-24 00:00:00       2900           2600
           30 Den Raphaely                        2002-12-07 00:00:00      11000           2900
           40 Susan Mavris                        2002-06-07 00:00:00       6500           6500

Last_value返回排序结果集中的最后一个值,如果最后一个值是NULL,则函数返回时NULL,可以使用IGNORE NULLS来处理。

SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         last_value(salary) ignore nulls over (partition by department_id order by salary desc) as sal_latest_dep
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LATEST_DEP
------------- ----------------------------------- ------------------- ---------- --------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400           4400
           20 Michael Hartstein                   2004-02-17 00:00:00      13000          13000
           20 Pat Fay                             2005-08-17 00:00:00       6000           6000
           30 Den Raphaely                        2002-12-07 00:00:00      11000          11000
           30 Shelli Baida                        2005-12-24 00:00:00       2900           2900
           30 Alexander Khoo                      2003-05-18 00:00:00       2600           2600
           30 Guy Himuro                          2006-11-15 00:00:00       2600           2600
           30 Sigal Tobias                        2005-07-24 00:00:00       2600           2600
           30 Karen Colmenares                    2007-08-10 00:00:00       2500           2500
           40 Susan Mavris                        2002-06-07 00:00:00       6500           6500

Last_value的窗后函数默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,按照官方文档的说法,这个默认的特性可能生成非预期结果,为了避免这个情况,可以使用
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         last_value(salary) ignore nulls over (partition by department_id order by salary) as sal_last_dep
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LAST_DEP
------------- ----------------------------------- ------------------- ---------- ------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400         4400
           20 Pat Fay                             2005-08-17 00:00:00       6000         6000
           20 Michael Hartstein                   2004-02-17 00:00:00      13000        13000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500         2500
           30 Alexander Khoo                      2003-05-18 00:00:00       2600         2600
           30 Guy Himuro                          2006-11-15 00:00:00       2600         2600
           30 Sigal Tobias                        2005-07-24 00:00:00       2600         2600
           30 Shelli Baida                        2005-12-24 00:00:00       2900         2900
           30 Den Raphaely                        2002-12-07 00:00:00      11000        11000
           40 Susan Mavris                        2002-06-07 00:00:00       6500         6500

已选择10行。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         last_value(salary) ignore nulls over (partition by department_id order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sal_last_dep
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LAST_DEP
------------- ----------------------------------- ------------------- ---------- ------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400         4400
           20 Pat Fay                             2005-08-17 00:00:00       6000         6000
           20 Michael Hartstein                   2004-02-17 00:00:00      13000        13000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500         2500
           30 Alexander Khoo                      2003-05-18 00:00:00       2600         2600
           30 Guy Himuro                          2006-11-15 00:00:00       2600         2600
           30 Sigal Tobias                        2005-07-24 00:00:00       2600         2600
           30 Shelli Baida                        2005-12-24 00:00:00       2900         2900
           30 Den Raphaely                        2002-12-07 00:00:00      11000        11000
           40 Susan Mavris                        2002-06-07 00:00:00       6500         6500

已选择10行。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

SQL> select department_id,
  2         first_name || ' ' || last_name employee_name,
  3         hire_date,
  4         salary,
  5         last_value(salary) ignore nulls over (partition by department_id order by salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sal_last_dep
  6    from employees
  7   where department_id <= 40;

DEPARTMENT_ID EMPLOYEE_NAME                       HIRE_DATE               SALARY SAL_LAST_DEP
------------- ----------------------------------- ------------------- ---------- ------------
           10 Jennifer Whalen                     2003-09-17 00:00:00       4400         4400
           20 Pat Fay                             2005-08-17 00:00:00       6000        13000
           20 Michael Hartstein                   2004-02-17 00:00:00      13000        13000
           30 Karen Colmenares                    2007-08-10 00:00:00       2500        11000
           30 Alexander Khoo                      2003-05-18 00:00:00       2600        11000
           30 Guy Himuro                          2006-11-15 00:00:00       2600        11000
           30 Sigal Tobias                        2005-07-24 00:00:00       2600        11000
           30 Shelli Baida                        2005-12-24 00:00:00       2900        11000
           30 Den Raphaely                        2002-12-07 00:00:00      11000        11000
           40 Susan Mavris                        2002-06-07 00:00:00       6500         6500

已选择10行。
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s