暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

华为GaussDB T 分析函数

墨天轮 2019-10-12
374

分析函数

LAG

语法:

LAG(expr,n ,m) over([ partition by [expr1][ ,... ] ] [order by [expr2][ ,... ] [NULLS FIRST | LAST] ])

功能:返回同一字段的前第N行的数据作为独立的列, 若不存在,则取默认值。

  • expr1是分组的列字段名称或者表达式,expr2是排序的列字段名称或者表达式。
  • n是偏移量,大于等于0的整数。
  • m是默认值,可以为字段值,常量,表达式。
  • [NULLS FIRST | LAST]请参见•ORDER BY。

说明:

  • 如果当前记录前面第n行expr列没有值,则返回默认值m。
  • 如果不带参数n,m,则返回当前记录前面一行记录expr列的值,没有则默认值为null

示例:

返回staffs表中的员工2个月前的工资。

--删除staffs表。 DROP TABLE IF EXISTS staffs;
--创建staffs表。 CREATE TABLE staffs ( staff_id NUMBER(6) not null, first_name VARCHAR2(20), last_name VARCHAR2(25), email VARCHAR2(25), phone_number VARCHAR2(20), hire_date DATE, employment_id VARCHAR2(10), salary NUMBER(8,2), commission_pct NUMBER(2,2), manager_id NUMBER(6), section_id NUMBER(4), graduated_name VARCHAR2(60) );
--插入数据。 insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2200.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2400.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4000.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4200.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 4400.00, null, 124, 50); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', to_date('25-06-1997', 'dd-mm-yyyy'), 'IT_PROG', 4400.00, null, 103, 60); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', to_date('25-06-1997', 'dd-mm-yyyy'), 'IT_PROG', 4600.00, null, 103, 60); insert into staffs (staff_id, first_name, last_name, email, phone_number, hire_date, employment_id, salary, commission_pct, manager_id, section_id) values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', to_date('25-06-1997', 'dd-mm-yyyy'), 'IT_PROG', 4800.00, null, 103, 60);
--返回员工两个月前的工资。 select staff_ID, lag(salary, 2,null)over(partition by staff_ID order by staff_ID) from staffs; 105 105 105 4400 198 198 198 2200 199 199 199 4000 9 rows fetched.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论