GBASE南大通用分享：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;
> 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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_order 3
f_deptname Dev
f_employeename Tom
f_salary $6000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_order 3
f_deptname Test
f_employeename Henry
f_salary $5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary $5000.00
8 row(s) retrieved.
>
GBASE南大通用分享：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;
计算各部门薪资排名
> 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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_order 3
f_deptname Dev
f_employeename Tom
f_salary $6000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_order 3
f_deptname Test
f_employeename Henry
f_salary $5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary $5000.00
8 row(s) retrieved.
>
员工调薪
update t_employee set f_salary = 6600 where f_employeeid = 1;
update t_employee set f_salary = 6500 where f_employeeid = 4;
> update t_employee set f_salary = 6600 where f_employeeid = 1;
1 row(s) updated.
> update t_employee set f_salary = 6500 where f_employeeid = 4;
1 row(s) updated.
>
计算各部门薪资排名
> 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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_order 2
f_deptname Dev
f_employeename Tom
f_salary $6600.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_order 2
f_deptname Test
f_employeename Henry
f_salary $6500.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary $5000.00
8 row(s) retrieved.
>
新员工入职
insert into t_employee values(9, 1, 'Will', 5000.00);
insert into t_employee values(10, 2, 'Judy', 5000.00);
> insert into t_employee values(9, 1, 'Will', 5000.00);
insert into t_employee values(10, 2, 'Judy', 5000.00);
1 row(s) inserted.
>
1 row(s) inserted.
>
计算各部门薪资排名
> 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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_order 2
f_deptname Dev
f_employeename Tom
f_salary $6600.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_order 4
f_deptname Dev
f_employeename Will
f_salary $5000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_order 2
f_deptname Test
f_employeename Henry
f_salary $6500.00
f_order 4
f_deptname Test
f_employeename Judy
f_salary $5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary $5000.00
10 row(s) retrieved.
>
计算各部门薪资排名
select dense_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;
> select dense_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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_order 2
f_deptname Dev
f_employeename Tom
f_salary $6600.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_order 3
f_deptname Dev
f_employeename Will
f_salary $5000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_order 2
f_deptname Test
f_employeename Henry
f_salary $6500.00
f_order 3
f_deptname Test
f_employeename Judy
f_salary $5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary $5000.00
10 row(s) retrieved.
>
GBASE南大通用分享：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;
> 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;
f_diff $0.00
f_deptname Dev
f_employeename Jack
f_salary $8000.00
f_diff $1400.00
f_deptname Dev
f_employeename Tom
f_salary $6600.00
f_diff $1400.00
f_deptname Dev
f_employeename Mary
f_salary $6600.00
f_diff $3000.00
f_deptname Dev
f_employeename Will
f_salary $5000.00
f_diff $0.00
f_deptname Test
f_employeename Rose
f_salary $7500.00
f_diff $1000.00
f_deptname Test
f_employeename Bill
f_salary $6500.00
f_diff $1000.00
f_deptname Test
f_employeename Henry
f_salary $6500.00
f_diff $2500.00
f_deptname Test
f_employeename Judy
f_salary $5000.00
f_diff $0.00
f_deptname Market
f_employeename Bob
f_salary $9000.00
f_diff $4000.00
f_deptname Market
f_employeename Kate
f_salary $5000.00
10 row(s) retrieved.
>
select f_salary - last_value(f_salary) over(partition by f_deptid order by f_salary asc) 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;
select last_value(f_salary) over(partition by f_deptid order by f_salary desc) 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;
GBASE南大通用分享：MAX/MIN
create table t_sale(f_month int, f_quarter int, f_qty int);
insert into t_sale values(1, 1, 3308);
insert into t_sale values(2, 1, 2619);
insert into t_sale values(3, 1, 3466);
insert into t_sale values(4, 2, 2904);
insert into t_sale values(5, 2, 2859);
insert into t_sale values(6, 2, 2528);
insert into t_sale values(7, 3, 2741);
insert into t_sale values(8, 3, 3281);
insert into t_sale values(9, 3, 2824);
insert into t_sale values(10, 4, 2822);
insert into t_sale values(11, 4, 3328);
insert into t_sale values(12, 4, 2623);
select f_month, f_quarter, f_qty, max(f_qty) over(partition by f_quarter order by f_month) as f_max, min(f_qty) over(partition by f_quarter order by f_month) as f_min from t_sale;
> select f_month, f_quarter, f_qty, max(f_qty) over(partition by f_quarter order by f_month) as f_max, min(f_qty) over(partition by f_quarter order by f_month) as f_min from t_sale;
f_month f_quarter f_qty f_max f_min
1 1 3308 3308 3308
2 1 2619 3308 2619
3 1 3466 3466 2619
4 2 2904 2904 2904
5 2 2859 2904 2859
6 2 2528 2904 2528
7 3 2741 2741 2741
8 3 3281 3281 2741
9 3 2824 3281 2741
10 4 2822 2822 2822
11 4 3328 3328 2822
12 4 2623 3328 2623
12 row(s) retrieved.
>
select f_month, f_quarter, f_qty, max(f_qty) over(partition by 1 order by f_month) as f_max, min(f_qty) over(partition by 1 order by f_month) as f_min from t_sale;
> select f_month, f_quarter, f_qty, max(f_qty) over(partition by 1 order by f_month) as f_max, min(f_qty) over(partition by 1 order by f_month) as f_min from t_sale;
f_month f_quarter f_qty f_max f_min
1 1 3308 3308 3308
2 1 2619 3308 2619
3 1 3466 3466 2619
4 2 2904 3466 2619
5 2 2859 3466 2619
6 2 2528 3466 2528
7 3 2741 3466 2528
8 3 3281 3466 2528
9 3 2824 3466 2528
10 4 2822 3466 2528
11 4 3328 3466 2528
12 4 2623 3466 2528
12 row(s) retrieved.
>
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;
> 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;
f_month f_quarter f_qty f_sum f_avg
1 1 3308 3308 3308.00000000000
2 1 2619 5927 2963.50000000000
3 1 3466 9393 3131.00000000000
4 2 2904 2904 2904.00000000000
5 2 2859 5763 2881.50000000000
6 2 2528 8291 2763.66666666667
7 3 2741 2741 2741.00000000000
8 3 3281 6022 3011.00000000000
9 3 2824 8846 2948.66666666667
10 4 2822 2822 2822.00000000000
11 4 3328 6150 3075.00000000000
12 4 2623 8773 2924.33333333333
12 row(s) retrieved.
>
select f_month, f_quarter, f_qty, sum(f_qty) over(partition by 1 order by f_month) as f_sum, avg(f_qty) over(partition by 1 order by f_month) as f_avg from t_sale;
> select f_month, f_quarter, f_qty, sum(f_qty) over(partition by 1 order by f_month) as f_sum, avg(f_qty) over(partition by 1 order by f_month) as f_avg from t_sale;
f_month f_quarter f_qty f_sum f_avg
1 1 3308 3308 3308.00000000000
2 1 2619 5927 2963.50000000000
3 1 3466 9393 3131.00000000000
4 2 2904 12297 3074.25000000000
5 2 2859 15156 3031.20000000000
6 2 2528 17684 2947.33333333333
7 3 2741 20425 2917.85714285714
8 3 3281 23706 2963.25000000000
9 3 2824 26530 2947.77777777778
10 4 2822 29352 2935.20000000000
11 4 3328 32680 2970.90909090909
12 4 2623 35303 2941.91666666667
12 row(s) retrieved.
>
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;
> 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;
f_month f_quarter f_qty f_lag f_lead
1 1 3308 2619
2 1 2619 3308 3466
3 1 3466 2619
4 2 2904 2859
5 2 2859 2904 2528
6 2 2528 2859
7 3 2741 3281
8 3 3281 2741 2824
9 3 2824 3281
10 4 2822 3328
11 4 3328 2822 2623
12 4 2623 3328
12 row(s) retrieved.
>
select f_month, f_quarter, f_qty, lag(f_qty) over(partition by 1 order by f_month) as f_lag, lead(f_qty) over(partition by 1 order by f_month) as f_lead from t_sale;
> select f_month, f_quarter, f_qty, lag(f_qty) over(partition by 1 order by f_month) as f_lag, lead(f_qty) over(partition by 1 order by f_month) as f_lead from t_sale;
f_month f_quarter f_qty f_lag f_lead
1 1 3308 2619
2 1 2619 3308 3466
3 1 3466 2619 2904
4 2 2904 3466 2859
5 2 2859 2904 2528
6 2 2528 2859 2741
7 3 2741 2528 3281
8 3 3281 2741 2824
9 3 2824 3281 2822
10 4 2822 2824 3328
11 4 3328 2822 2623
12 4 2623 3328
12 row(s) retrieved.
>
同比：本年度（季度或月度）与上一年年度（季度或月度）的比较。
环比：本季度（或月度）与上一个季度（或月度）的比较。
select f_month, f_quarter, f_qty, lag(f_qty, 3) over(partition by 1 order by f_month) as f_lag, lead(f_qty, 3) over(partition by 1 order by f_month) as f_lead from t_sale;
> select f_month, f_quarter, f_qty, lag(f_qty, 3) over(partition by 1 order by f_month) as f_lag, lead(f_qty, 3) over(partition by 1 order by f_month) as f_lead from t_sale;
f_month f_quarter f_qty f_lag f_lead
1 1 3308 2904
2 1 2619 2859
3 1 3466 2528
4 2 2904 3308 2741
5 2 2859 2619 3281
6 2 2528 3466 2824
7 3 2741 2904 2822
8 3 3281 2859 3328
9 3 2824 2528 2623
10 4 2822 2741
11 4 3328 3281
12 4 2623 2824
12 row(s) retrieved.
>
455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
