Oracle analytic function-LAG

LAG是一个分析函数,从Oracle 8i开始引入。使用这个函数可以一次性从表中获取多条数据而不需要将此表进行自连接,函数返回表中的当前行的前offset行的指定列值。

用法:
LAG
{ ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)

• value_expr 值表达式,通常是字段,也可是是表达式。但不能是分析函数
• offset 偏移,可选参数,是表中与当前行的物理偏移度,如果省略,默认值为1,既当前行的前offset行。
• default 可选参数,如果offset参数指向超出了表的范围,就返回这个值,默认值为null。
• over 理解成在一个结果集范围内,如果后面的 partition by为空,那么就是当前的结果集范围内。
• query_partition_clause 分区语句,对结果集合分区的语句,是可选的,如果没有就是所有的一个分区。
• Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc

Oracle 11g对lag函数进行了增强,加入了{RESPECT | IGNORE} NULLS。

原始数据

SQL> select employee_id,hire_date,salary from employees where department_id <= 40 order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY
----------- ------------------- ----------
        203 2002-06-07 00:00:00       6500
        114 2002-12-07 00:00:00      11000
        115 2003-05-18 00:00:00       3100
        200 2003-09-17 00:00:00       4400
        201 2004-02-17 00:00:00      13000
        117 2005-07-24 00:00:00       2800
        202 2005-08-17 00:00:00       6000
        116 2005-12-24 00:00:00       2900
        118 2006-11-15 00:00:00       2600
        119 2007-08-10 00:00:00       2500

采用lag函数的默认参数分析

SQL> select employee_id,hire_date,salary,lag(salary) over (order by hire_date) as salary_prev
  2  from employees
  3  where department_id <= 40
  4  order by hire_date;

EMPLOYEE_ID HIRE_DATE              SALARY   SALARY_PREV
----------- ------------------- ---------- -----------
        203 2002-06-07 00:00:00       6500        NULL
        114 2002-12-07 00:00:00      11000        6500
        115 2003-05-18 00:00:00       3100       11000
        200 2003-09-17 00:00:00       4400        3100
        201 2004-02-17 00:00:00      13000        4400
        117 2005-07-24 00:00:00       2800       13000
        202 2005-08-17 00:00:00       6000        2800
        116 2005-12-24 00:00:00       2900        6000
        118 2006-11-15 00:00:00       2600        2900
        119 2007-08-10 00:00:00       2500        2600

当对Lag函数传入offset为2,default_value为200的结果

SQL> select employee_id,hire_date,salary,lag(salary,2,200) over (order by hire_date) as salary_prev
  2  from employees
  3  where department_id <= 40
  4  order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY SALARY_PREV
----------- ------------------- ---------- -----------
        203 2002-06-07 00:00:00       6500         200
        114 2002-12-07 00:00:00      11000         200
        115 2003-05-18 00:00:00       3100        6500
        200 2003-09-17 00:00:00       4400       11000
        201 2004-02-17 00:00:00      13000        3100
        117 2005-07-24 00:00:00       2800        4400
        202 2005-08-17 00:00:00       6000       13000
        116 2005-12-24 00:00:00       2900        2800
        118 2006-11-15 00:00:00       2600        6000
        119 2007-08-10 00:00:00       2500        2900

对lag函数加入分组语句

SQL> select employee_id,hire_date,job_id,salary,lag(salary,1,null) over (partition by job_id order by hire_date) as salary_prev
  2  from employees
  3  where department_id <= 40
  4  order by hire_date;

EMPLOYEE_ID HIRE_DATE           JOB_ID                   SALARY SALARY_PREV
----------- ------------------- -------------------- ---------- -----------
        203 2002-06-07 00:00:00 HR_REP                     6500
        114 2002-12-07 00:00:00 PU_MAN                    11000
        115 2003-05-18 00:00:00 PU_CLERK                   3100
        200 2003-09-17 00:00:00 AD_ASST                    4400
        201 2004-02-17 00:00:00 MK_MAN                    13000
        117 2005-07-24 00:00:00 PU_CLERK                   2800        3100
        202 2005-08-17 00:00:00 MK_REP                     6000
        116 2005-12-24 00:00:00 PU_CLERK                   2900        2800
        118 2006-11-15 00:00:00 PU_CLERK                   2600        2900
        119 2007-08-10 00:00:00 PU_CLERK                   2500        2600

对11g中lag函数增强部分{RESPECT | IGNORE} NULLS进行测试。

create table t_lag as select employee_id,hire_date,job_id,salary  from employees where department_id <= 40;
select employee_id,hire_date,job_id,salary  from t_lag;
update t_lag set salary = null where employee_id = 118;
commit;

更新后的记录如下

SQL> select employee_id,hire_date,job_id,salary  from t_lag;

EMPLOYEE_ID HIRE_DATE           JOB_ID                   SALARY
----------- ------------------- -------------------- ----------
        200 2003-09-17 00:00:00 AD_ASST                    4400
        201 2004-02-17 00:00:00 MK_MAN                    13000
        202 2005-08-17 00:00:00 MK_REP                     6000
        114 2002-12-07 00:00:00 PU_MAN                    11000
        115 2003-05-18 00:00:00 PU_CLERK                   3100
        116 2005-12-24 00:00:00 PU_CLERK                   2900
        117 2005-07-24 00:00:00 PU_CLERK                   2800
        118 2006-11-15 00:00:00 PU_CLERK
        119 2007-08-10 00:00:00 PU_CLERK                   2500
        203 2002-06-07 00:00:00 HR_REP                     6500

EMPLOYEE_ID=118的salary为空,继续执行上述的lag分析函数

SQL> select employee_id,hire_date,salary,lag(salary,1) over (order by hire_date) as salary_prev
  2  from t_lag
  3  order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY SALARY_PREV
----------- ------------------- ---------- -----------
        203 2002-06-07 00:00:00       6500
        114 2002-12-07 00:00:00      11000        6500
        115 2003-05-18 00:00:00       3100       11000
        200 2003-09-17 00:00:00       4400        3100
        201 2004-02-17 00:00:00      13000        4400
        117 2005-07-24 00:00:00       2800       13000
        202 2005-08-17 00:00:00       6000        2800
        116 2005-12-24 00:00:00       2900        6000
        118 2006-11-15 00:00:00                   2900
        119 2007-08-10 00:00:00       2500

118的记录为空造成LAG后119的记录也为空。系统默认的为RESPECT NULLS,修改为IGNORE NULLS后

SQL> select employee_id,hire_date,salary,lag(salary,1,null) ignore nulls over (order by hire_date) as salary_prev
  2  from t_lag
  3  order by hire_date;

EMPLOYEE_ID HIRE_DATE               SALARY SALARY_PREV
----------- ------------------- ---------- -----------
        203 2002-06-07 00:00:00       6500
        114 2002-12-07 00:00:00      11000        6500
        115 2003-05-18 00:00:00       3100       11000
        200 2003-09-17 00:00:00       4400        3100
        201 2004-02-17 00:00:00      13000        4400
        117 2005-07-24 00:00:00       2800       13000
        202 2005-08-17 00:00:00       6000        2800
        116 2005-12-24 00:00:00       2900        6000
        118 2006-11-15 00:00:00                   2900
        119 2007-08-10 00:00:00       2500        2900

新增的IGNORE NULLS功能,可以忽略NULL结果,去寻找另一个满足条件的结果。

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

One Response to Oracle analytic function-LAG

  1. Pingback引用通告: Oracle analytic function-LEAD | LEO Notes

发表评论

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