GBase 8s 学习笔记 008 —— GBase 8s 常用函数
标量函数
数学函数
函数 | 说明 |
---|---|
abs | 返回绝对值 |
ceil | 返回大于参数的数值 |
floor | 返回小于参数的数值 |
round | 返回参数的四舍五入数值 |
mod | 返回第一个参数的模 |
pow | 计算数值的N次方 |
sqrt | 计算平方根 |
root | 计算数值的N次方根 |
exp | 计算指数 |
ln | 计算自然对数 |
logn | 计算自然对数 |
log10 | 计算以10为底的对数 |
sin | 计算正弦值 |
cos | 计算余弦值 |
ABS
abs(num)
> select abs(-5.6) as num1, abs(5.6) as num2 from dual;
num1 num2
5.60000000000000 5.60000000000000
1 row(s) retrieved.
>
CEIL/FLOOR/ ROUND
ceil(num)
floor(num)
round(num)
> select ceil(5.6) as f_ceil, floor(5.6)as f_floor, round(5.6) as f_round from dual;
f_ceil f_floor f_round
6 5 6
1 row(s) retrieved.
>
> select ceil(5.3) as f_ceil, floor(5.3)as f_floor, round(5.3) as f_round from dual;
f_ceil f_floor f_round
6 5 5
1 row(s) retrieved.
> select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from dual;
f_ceil f_floor f_round
-5 -6 -6
1 row(s) retrieved.
> select ceil(-5.3) as f_ceil, floor(-5.3)as f_floor, round(-5.3) as f_round from dual;
f_ceil f_floor f_round
-5 -6 -5
1 row(s) retrieved.
>
MOD
mod(num)
> select mod(10, 3) as f_mod from dual;
f_mod
1
1 row(s) retrieved.
>
POW/SQRT/ROOT
pow(num1, num2)
sqrt(num)
root(num1, num2)
> select pow(2, 3) as f_pow3, sqrt(25) as f_sqrt, root(64, 3) as f_root from dual;
f_pow3 f_sqrt f_root
8.000000000000 5.000000000000 4.000000000000
1 row(s) retrieved.
>
EXP
exp(num)
> select exp(1) as f_exp from dual;
f_exp
2.718281828459
1 row(s) retrieved.
>
LN/LOGN/LOG10
ln(num)
logn(num)
log10(num)
> select ln(2.718281828459) as f_ln, logn(2.718281828459) as f_logn, log10(1000) as f_log10 from dual;
f_ln f_logn f_log10
1.000000000000 1.000000000000 3.000000000000
1 row(s) retrieved.
>
SIN/COS
sin(num)
cos(num)
> select sin(1) as f_sin, cos(1) as f_cos from dual;
f_sin f_cos
0.841470984808 0.540302305868
1 row(s) retrieved.
>
字符串函数
函数 | 说明 |
---|---|
CONCAT | 字符串拼接 |
TRIM | 从字符串的开头或结尾移除指定字符 |
LTRIM | 从字符串的开头移除指定字符 |
RTRIM | 从字符串的结尾移除指定字符 |
SUBSTR | 截取字符串 |
SUBSTRB | 截取字符串 |
SUBSTRING | 截取字符串 |
INSTR | 返回字符串中指定子串的开始位置 |
ASCII | 返回字符串第一个字符的编码 |
REPLACE | 替换字符串中的部分内容 |
UPPER | 将字符串的字母转成大写 |
LOWER | 将字符串的字母转成小写 |
LENGTH | 返回字符串的长度 |
OCTET_LENGTH | 返回字符串的长度 |
CHAR_LENGTH | 返回字符串的长度 |
REGEXP_REPLACE | 使用正则表达式方式替换字符串中的内容 |
REGEXP_SUBSTR | 使用正则表达式方式替换字符串中的内容 |
REGEXT_INSTR | 计算字符串中指定正则表达式定义的字符串所在位置 |
CONCAT
concat(str1, str2)
> select concat('Hello', 'World') as f_concat from dual;
f_concat
HelloWorld
1 row(s) retrieved.
>
TRIM/LTRIM/RTRIM
trim(str)
trim(both ‘char’ from column_name)
ltrim(str)
ltrim(str, ‘char’)
rtrim(str)
rtrim(str, ‘char’)
> select f_message,
> octet_length(f_message) as f_len1,
> octet_length(trim(f_message)) as f_len2,
> octet_length(ltrim(f_message)) as f_len3,
> octet_length(rtrim(f_message)) as f_len4
> from (select ' Hello world ' as f_message from dual) t;
f_message f_len1 f_len2 f_len3 f_len4
Hello world 16 11 15 12
1 row(s) retrieved.
>
> select f_message,
> octet_length(f_message) as f_len1,
> octet_length(trim(both '#' from f_message)) as f_len2,
> octet_length(ltrim(f_message, '#')) as f_len3,
> octet_length(rtrim(f_message, '#')) as f_len4
> from
> (select '#Hello world####' as f_message from dual) t;
f_message f_len1 f_len2 f_len3 f_len4
#Hello world#### 16 11 15 12
1 row(s) retrieved.
SUBSTR/SUBSTRB/SUBSTRING
substr(str, start, len)
substrb(str, start, len)
substring(str from start for len)
> select substr('abcdefg', 2, 3) as f_substr from dual;
f_substr
bcd
1 row(s) retrieved.
>
INSTR
instr(str1, str2, start, count)
> select instr('How are you?', 'o', 1, 1) as f_instr1, instr('How are you?', 'o', 1, 2) as f_instr2 from dual;
f_instr1 f_instr2
2 10
1 row(s) retrieved.
>
ASCII
ascii(str)
> select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('冀辉') as f_ascii3 from dual;
f_ascii1 f_ascii2 f_ascii3
72 119 8423141
1 row(s) retrieved.
>
> select ascii('冀') as f_ascii1, ascii('辉') as f_ascii2, ascii('冀辉') as f_ascii3 from dual;
f_ascii1 f_ascii2 f_ascii3
8423141 9027304 8423141
1 row(s) retrieved.
>
REPLACE
replace(str1, str2)
> select content, replace(content, 'reading', 'writing') as f_replace
> from
> (select 'I like reading' as content from dual) t;
content f_replace
I like reading I like writing
1 row(s) retrieved.
>
UPPER/LOWER
upper(str)
lower(str)
> select upper('Hello World') as f_upper, lower('Hello World') as f_lower from dual;
f_upper f_lower
HELLO WORLD hello world
1 row(s) retrieved.
>
LENGTH/OCTET_LENGTH/CHAR_LENGTH
length(str)
octet_length(str)
char_length(str)
> select length('南大通用') as f_len11, length('南大通用:GBase') as f_len12 , octet_length('南大通用') as f_len21, octet_length('南大通用:GBase') as f_len22, char_length('南大通用') as f_len31, char_length('南大通用:GBase)') as f_len3 from dual;
f_len11 f_len12 f_len21 f_len22 f_len31 f_len3
12 18 12 18 4 11
1 row(s) retrieved.
>
REGEXP_REPLACE/REGEXP_SUBSTR/REGEXT_INSTR
> select f_content, regexp_replace(f_content, '\d{11}', '<phone_number>') as f_template from (select '我的电话号码是13920656789,我家的邮编是300160。' as f_content from dual union all select '快递已经给您放小区菜鸟驿站了,有问题打电话18632145678。' as f_content from dual) t;
f_content 我的电话号码是13920656789,我家的邮编是300160。
f_template 我的电话号码是<phone_number>,我家的邮编是300160。
f_content 快递已经给您放小区菜鸟驿站了,有问题打电话1863
2145678。
f_template 快递已经给您放小区菜鸟驿站了,有问题打电话<pho
ne_number>。
2 row(s) retrieved.
>
> select f_content, regexp_instr(f_content, '\d{6}') as f_haspostcode from (select '我的电话号码是13920656789,我家的邮编是300160。' as f_content from dual union all select '快递已经给您放小区菜鸟驿站了,有问题打电话18632145678。' as f_content from dual) t;
f_content 我的电话号码是13920656789,我家的邮编是300160。
f_haspostcode 22
f_content 快递已经给您放小区菜鸟驿站了,有问题打电话1
8632145678。
f_haspostcode 64
2 row(s) retrieved.
>
日期时间函数
SYSDATE/CURRENT/TODAY
> select sysdate as f_date from dual;
f_date
2023-02-13 20:54:42.50980
1 row(s) retrieved.
>
> select current as f_date from dual;
f_date
2023-02-13 20:54:50.901
1 row(s) retrieved.
>
> select today as f_today from dual;
f_today
2023 02月 13日
1 row(s) retrieved.
>
LAST_DAY
> select last_day(date('02/18/2021')) as f_lastday from dual;
f_lastday
02/28/2021
1 row(s) retrieved.
>
# 需要格式化日期,否则执行会报错
# > select last_day(date('02/18/2021')) as f_lastday from dual;
# 1205: Invalid month in date
# Error in line 1
# Near character position 57
[gbasedbt@192 ~]$ export GL_DATE="%m/%d/%iY"
[gbasedbt@192 ~]$ dbaccess - -
Your evaluation license will expire on 2024-02-07 00:00:00
> !echo $GL_DATE
%m/%d/%iY
> select last_day(date('02/18/2021')) as f_lastday from dual;
f_lastday
02/28/2021
1 row(s) retrieved.
>
YEAR/MONTH/DAY
> select weekday(date('02/18/2021')) as f_weekday, quarter(date('02/18/2021')) as f_quarte from dual;
f_weekday f_quarte
4 1
1 row(s) retrieved.
>
ADD_MONTH
> select add_months(date('02/18/2021'), 2) as f_month1, add_months(date('02/18/2021'), -4) as f_month2 from dual;
f_month1 f_month2
04/18/2021 10/18/2020
1 row(s) retrieved.
>
TO_DATE
> select to_date('2021-06-18 12:34:56', 'yyyy-mm-dd hh:mi:ss') as f_date from dual;
f_date
2021-06-18 12:34:56.00000
1 row(s) retrieved.
>
TO_CHAR
> select sysdate as f_date1, to_char(sysdate, 'mm/dd/yyyy hh12:mi:ss') as f_date2 from dual;
f_date1 2023-02-13 21:06:39.44893
f_date2 02/13/2023 09:06:39
1 row(s) retrieved.
>
聚合函数
数据准备
> drop table if exists t_dept;
Table dropped.
> create table t_dept(f_deptid int, f_deptname varchar(50));
Table created.
> insert into t_dept values(1, 'Dev');
1 row(s) inserted.
> insert into t_dept values(2, 'Test');
1 row(s) inserted.
> insert into t_dept values(3, 'Market');
1 row(s) inserted.
> drop table if exists t_employee;
Table dropped.
> create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(50), f_salary money);
Table created.
> insert into t_employee values(1, 1, 'Tom', 6000.00);
1 row(s) inserted.
> insert into t_employee values(2, 1, 'Jack', 8000.00);
1 row(s) inserted.
> insert into t_employee values(3, 1, 'Mary', 6600.00);
1 row(s) inserted.
> insert into t_employee values(4, 2, 'Henry', 5000.00);
1 row(s) inserted.
> insert into t_employee values(5, 2, 'Rose', 7500.00);
1 row(s) inserted.
> insert into t_employee values(6, 2, 'Bill', 6500.00);
1 row(s) inserted.
> insert into t_employee values(7, 3, 'Kate', 5000.00);
1 row(s) inserted.
> insert into t_employee values(8, 3, 'Bob', 9000.00);
1 row(s) inserted.
COUNT
> select a.f_deptid, b.f_deptname, count(1) as f_cnt from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;
f_deptid 1
f_deptname Dev
f_cnt 3
f_deptid 2
f_deptname Test
f_cnt 3
f_deptid 3
f_deptname Market
f_cnt 2
3 row(s) retrieved.
>
SUM
> select a.f_deptid, b.f_deptname, sum(f_salary) as f_salary from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;
f_deptid 1
f_deptname Dev
f_salary RMB 20600.00
f_deptid 2
f_deptname Test
f_salary RMB 19000.00
f_deptid 3
f_deptname Market
f_salary RMB 14000.00
3 row(s) retrieved.
>
AVG
> select a.f_deptid, b.f_deptname, avg(f_salary) as f_salary_avg from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;
f_deptid 1
f_deptname Dev
f_salary_avg RMB 6866.67
f_deptid 2
f_deptname Test
f_salary_avg RMB 6333.33
f_deptid 3
f_deptname Market
f_salary_avg RMB 7000.00
3 row(s) retrieved.
>
MAX/MIN
> select a.f_deptid, b.f_deptname, max(f_salary) as f_salary_max, min(f_salary) as f_salary_min from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;
f_deptid 1
f_deptname Dev
f_salary_max RMB 8000.00
f_salary_min RMB 6000.00
f_deptid 2
f_deptname Test
f_salary_max RMB 7500.00
f_salary_min RMB 5000.00
f_deptid 3
f_deptname Market
f_salary_max RMB 9000.00
f_salary_min RMB 5000.00
3 row(s) retrieved.
>
WM_CONCAT
> select a.f_deptid, b.f_deptname, wm_concat(f_employeename) as f_employees from t_employee a, t_dept b where a.f_deptid = b.f_deptid group by a.f_deptid, b.f_deptname order by a.f_deptid;
f_deptid 1
f_deptname Dev
f_employees Tom,Jack,Mary
f_deptid 2
f_deptname Test
f_employees Henry,Rose,Bill
f_deptid 3
f_deptname Market
f_employees Kate,Bob
3 row(s) retrieved.
>
窗口函数
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;
f_order 1
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_order 3
f_deptname Dev
f_employeename Tom
f_salary RMB 6000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_order 3
f_deptname Test
f_employeename Henry
f_salary RMB 5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary RMB 5000.00
8 row(s) retrieved.
>
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;
>
f_order 1
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_order 3
f_deptname Dev
f_employeename Tom
f_salary RMB 6000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_order 3
f_deptname Test
f_employeename Henry
f_salary RMB 5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary RMB 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;
1 row(s) updated.
>
1 row(s) updated.
>
计算各部门薪资排名
f_order 1
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_order 2
f_deptname Dev
f_employeename Tom
f_salary RMB 6600.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_order 2
f_deptname Test
f_employeename Henry
f_salary RMB 6500.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary RMB 5000.00
8 row(s) retrieved.
>
新员工入职
> insert into t_employee values(9, 1, 'Will', 5000.00);
1 row(s) inserted.
> insert into t_employee values(10, 2, 'Judy', 5000.00);
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 RMB 8000.00
f_order 2
f_deptname Dev
f_employeename Tom
f_salary RMB 6600.00
f_order 2
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_order 4
f_deptname Dev
f_employeename Will
f_salary RMB 5000.00
f_order 1
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_order 2
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_order 2
f_deptname Test
f_employeename Henry
f_salary RMB 6500.00
f_order 4
f_deptname Test
f_employeename Judy
f_salary RMB 5000.00
f_order 1
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_order 2
f_deptname Market
f_employeename Kate
f_salary RMB 5000.00
10 row(s) retrieved.
>
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;
> > >
f_diff RMB 0.00
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_diff RMB 1400.00
f_deptname Dev
f_employeename Tom
f_salary RMB 6600.00
f_diff RMB 1400.00
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_diff RMB 3000.00
f_deptname Dev
f_employeename Will
f_salary RMB 5000.00
f_diff RMB 0.00
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_diff RMB 1000.00
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_diff RMB 1000.00
f_deptname Test
f_employeename Henry
f_salary RMB 6500.00
f_diff RMB 2500.00
f_deptname Test
f_employeename Judy
f_salary RMB 5000.00
f_diff RMB 0.00
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_diff RMB 4000.00
f_deptname Market
f_employeename Kate
f_salary RMB 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;
>
f_diff RMB 0.00
f_deptname Dev
f_employeename Will
f_salary RMB 5000.00
f_diff RMB 0.00
f_deptname Dev
f_employeename Tom
f_salary RMB 6600.00
f_diff RMB 0.00
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_diff RMB 0.00
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_diff RMB 0.00
f_deptname Test
f_employeename Judy
f_salary RMB 5000.00
f_diff RMB 0.00
f_deptname Test
f_employeename Henry
f_salary RMB 6500.00
f_diff RMB 0.00
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_diff RMB 0.00
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_diff RMB 0.00
f_deptname Market
f_employeename Kate
f_salary RMB 5000.00
f_diff RMB 0.00
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
10 row(s) retrieved.
>
> 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;
> > > >
f_diff RMB 8000.00
f_deptname Dev
f_employeename Jack
f_salary RMB 8000.00
f_diff RMB 6600.00
f_deptname Dev
f_employeename Tom
f_salary RMB 6600.00
f_diff RMB 6600.00
f_deptname Dev
f_employeename Mary
f_salary RMB 6600.00
f_diff RMB 5000.00
f_deptname Dev
f_employeename Will
f_salary RMB 5000.00
f_diff RMB 7500.00
f_deptname Test
f_employeename Rose
f_salary RMB 7500.00
f_diff RMB 6500.00
f_deptname Test
f_employeename Bill
f_salary RMB 6500.00
f_diff RMB 6500.00
f_deptname Test
f_employeename Henry
f_salary RMB 6500.00
f_diff RMB 5000.00
f_deptname Test
f_employeename Judy
f_salary RMB 5000.00
f_diff RMB 9000.00
f_deptname Market
f_employeename Bob
f_salary RMB 9000.00
f_diff RMB 5000.00
f_deptname Market
f_employeename Kate
f_salary RMB 5000.00
10 row(s) retrieved.
>
MAX/MIN
> create table t_sale(f_month int, f_quarter int, f_qty int);
Table created.
>
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);
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
> 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);
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
1 row(s) inserted.
>
> 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;
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;
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;
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;
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;
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;
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.
>
其它函数
sql
> select dbinfo('dbhostname') as f_hostname from dual;
f_hostname
192.168.254.134
1 row(s) retrieved.
> select dbinfo('dbname') as f_dbname from dual;
f_dbname mydb
1 row(s) retrieved.
> select dbinfo('version', 'full') as f_version from dual;
f_version
GBase Server Version 12.10.FC4G1TL
1 row(s) retrieved.
> select dbinfo('sessionid') as f_sessionid from dual;
f_sessionid
23
1 row(s) retrieved.
> select dbinfo('bigserial') as f_bigserial from dual;
f_bigserial
0
1 row(s) retrieved.
> select dbinfo('serial8') as f_serial8 from dual;
f_serial8
0
1 row(s) retrieved.
>
NVL2
> select f_username, f_leavedate, nvl2(f_leavedate, '离职', '在职') as f_userstatus
from
(select 'Tom' as f_username, '2020-06-18' as f_leavedate from dual
union all
select 'Jim' as f_username, null as f_leavedate from dual) t;
> > > >
f_username f_leavedate f_userstatus
Tom 2020-06-18 离职
Jim 在职
2 row(s) retrieved.
>
HEX
> select hex(255) as f_hex1, hex(65535) as f_hex2, hex(2155905152) as f_hex3, hex(4294967296) as f_hex4, hex(1152921504606846975) as f_hex5 from dual;
f_hex1 0x000000FF
f_hex2 0x0000FFFF
f_hex3 0x0000000080808080
f_hex4 0x0000000100000000
f_hex5 0x0fffffffffffffff
1 row(s) retrieved.
>
GetHzFullPY
> select GetHzFullPY('南大通用') as f_py from dual;
f_py nandatongyong
1 row(s) retrieved.
>
SYS_GUID
> select sys_guid() from dual;
(expression)
E20B700823824E1297B6D0AC4F8BC790
1 row(s) retrieved.
> select sys_guid() from dual;
(expression)
8CFF88F88C824E45968F5BAF019DFDF9
1 row(s) retrieved.
>
> select current,dbservername,rowid,sitename,sysdate,today,current_user,user from dual;
(expression) 2023-02-13 21:48:58.366
(expression) gbaseserver
rowid 769
(expression) gbaseserver
(expression) 2023-02-13 21:48:58.36629
(expression) 02/13/2023
(expression) gbasedbt
(expression) gbasedbt
1 row(s) retrieved.
>
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。