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

GBase 8s 学习笔记 008 —— GBase 8s 常用函数

心有阳光 2023-02-13
1230

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. >

GBase094.png数学函数

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. >

GBase095.png数学函数

字符串函数

函数 说明
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. >

GBase096.png字符串函数

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. >

GBase097.png字符串函数

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. >

GBase098.png字符串函数

日期时间函数

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. >

GBase099.png日期时间函数

# 需要格式化日期,否则执行会报错 # > 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. >

GBase100.png
日期时间函数

聚合函数

数据准备

> 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.

GBase101.png数据准备

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. >

GBase102.png聚合函数

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. >

GBase103.png聚合函数

窗口函数

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. >

GBase104.png窗口函数

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. >

GBase105.png窗口函数

员工调薪

> 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. >

GBase106.png窗口函数

计算各部门薪资排名

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. >

GBase107.png窗口函数

新员工入职

> 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. >

GBase108.png窗口函数

> 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. >

GBase109.png窗口函数

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. >

GBase110.png窗口函数

> 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. >

GBase111.png窗口函数

> 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. >

GBase112.png窗口函数

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. >

GBase113.png窗口函数

> 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. >

GBase114.png窗口函数

> 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. >

GBase115.png窗口函数

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. >

GBase116.png窗口函数

> 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. >

GBase117.png窗口函数

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. >

GBase118.png窗口函数

> 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. >

GBase119.png窗口函数

同比:本年度(季度或月度)与上一年年度(季度或月度)的比较。
环比:本季度(或月度)与上一个季度(或月度)的比较。

> 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. >

GBase120.png窗口函数

其它函数

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. >

GBase121.png其它函数

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. >

GBase122.png其它函数

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. >

GBase123.png其它函数

GetHzFullPY

> select GetHzFullPY('南大通用') as f_py from dual; f_py nandatongyong 1 row(s) retrieved. >

GBase124.png其它函数

SYS_GUID

> select sys_guid() from dual; (expression) E20B700823824E1297B6D0AC4F8BC790 1 row(s) retrieved. > select sys_guid() from dual; (expression) 8CFF88F88C824E45968F5BAF019DFDF9 1 row(s) retrieved. >

GBase125.png其它函数

> 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. >

GBase126.png其它函数

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论