Gbase 8s内置函数之窗口函数

Gbase 8s的内置函数包含标量函数、聚合函数、窗口函数以及其他的函数。

本次介绍窗口函数及其使用例句:

ROW_NUMBER/ROWNUMBER

select row_number() over(partition by f_deptid order by f_salary desc) as f_order, f_deptname, f_employeename, f_salary

from

(select a.f_employeeid, a.f_deptid, b.f_deptname, a.f_employeename, a.f_salary

from t_employee a, t_dept b

where a.f_deptid = b.f_deptid) t;

RANK/DENSE_RANK

select rank() over(partition by f_deptid order by f_salary desc) as f_order, f_deptname, f_employeename, f_salary

from

(select a.f_employeeid, a.f_deptid, b.f_deptname, a.f_employeename, a.f_salary

from t_employee a, t_dept b

where a.f_deptid = b.f_deptid) t;

FIRST_VALUE/LAST_VALUE

select first_value(f_salary) over(partition by f_deptid order by f_salary desc) - f_salary as f_diff, f_deptname, f_employeename, f_salary

from

(select a.f_employeeid, a.f_deptid, b.f_deptname, a.f_employeename, a.f_salary

from t_employee a, t_dept b

where a.f_deptid = b.f_deptid) t;

MAX/MIN

create table t_sale(f_month int, f_quarter int, f_qty int);

SUM/AVG

select f_month, f_quarter, f_qty, sum(f_qty) over(partition by f_quarter order by f_month) as f_sum, avg(f_qty) over(partition by f_quarter order by f_month) as f_avg from t_sale;

LAG/LEAD

select f_month, f_quarter, f_qty, lag(f_qty) over(partition by f_quarter order by f_month) as f_lag, lead(f_qty) over(partition by f_quarter order by f_month) as f_lead from t_sale;