标量函数
数学函数
| 函数 | 说明 |
|---|---|
| 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;
> 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;
> 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;
> 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;
> 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;
> 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(num1, num2)
select mod(10, 3) as f_mod from dual;
> 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;
> 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;
> 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;
> 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 | 截取字符串 |
| 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;
> 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;
> 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;
> 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
substr(str, start, len)
substrb(str, start, len)
select substr('abcdefg', 2, 3) as f_substr from dual;
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;
> 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;
select ascii('冀') as f_ascii1, ascii('辉') as f_ascii2, ascii('南大通用') as f_ascii3 from dual;
> 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 hex(ascii('南')) as f_ascii1, hex(ascii('大')) as f_ascii2, hex(ascii('南大通用')) as f_ascii3 from dual;
f_ascii1 f_ascii2 f_ascii3
0x008086E5 0x0089BEE8 0x008086E5
1 row(s) retrieved.
>
8423141 == H 80 86E5
[gbasedbt@devsvr ~]$ cat a.txt
冀辉
[gbasedbt@devsvr ~]$ file a.txt
a.txt: UTF-8 Unicode text
[gbasedbt@devsvr ~]$ od -x a.txt
0000000 86e5 e880 89be 000a
0000007
[gbasedbt@devsvr ~]$
REPLACE
replace(str1, str2, str3)
select content, replace(content, 'reading', 'writing') as f_replace
from
(select 'I like reading' as content from dual) t;
> 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;
> 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;
> 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_len32 from dual;
f_len11 f_len12 f_len21 f_len22 f_len31 f_len32
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;
> 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;
日期时间函数
SYSDATE/CURRENT/TODAY
select current_date() as f_date from dual;
select LOCAL_TIMESTAMP() as f_date from dual;
select sysdate as f_date from dual;
select current as f_date from dual;
> select sysdate as f_date from dual;
f_date
2021-06-25 08:20:24.97931
1 row(s) retrieved.
>
> select current as f_date from dual;
f_date
2021-06-25 08:29:40.321
1 row(s) retrieved.
>
> select today as f_today from dual;
f_today
06/25/2021
1 row(s) retrieved.
>
LAST_DAY
测试前，需要设置环境变量。
select last_day(date('02/18/2021')) as f_lastday from dual;
> 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 year(date('02/18/2021')) as f_year, month(date('02/18/2021')) as f_month, day(date('02/18/2021')) as f_day from dual;
> select year(date('02/18/2021')) as f_year, month(date('02/18/2021')) as f_month, day(date('02/18/2021')) as f_day from dual;
f_year f_month f_day
2021 2 18
1 row(s) retrieved.
>
WEEKDAY/QUARTER
select weekday(date('02/18/2021')) as f_weekday, quarter(date('02/18/2021')) as f_quarte from dual;
> 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;
> 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;
> 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;
> select sysdate as f_date1, to_char(sysdate, 'mm/dd/yyyy hh12:mi:ss') as f_date2 from dual;
f_date1 2021-06-25 08:11:08.46768
f_date2 06/25/2021 08:11:08
1 row(s) retrieved.
>
聚合函数
数据准备
drop table if exists t_dept;
create table t_dept(f_deptid int, f_deptname varchar(50));
insert into t_dept values(1, 'Dev');
insert into t_dept values(2, 'Test');
insert into t_dept values(3, 'Market');
drop table if exists t_employee;
create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(50), f_salary money);
insert into t_employee values(1, 1, 'Tom', 6000.00);
insert into t_employee values(2, 1, 'Jack', 8000.00);
insert into t_employee values(3, 1, 'Mary', 6600.00);
insert into t_employee values(4, 2, 'Henry', 5000.00);
insert into t_employee values(5, 2, 'Rose', 7500.00);
insert into t_employee values(6, 2, 'Bill', 6500.00);
insert into t_employee values(7, 3, 'Kate', 5000.00);
insert into t_employee values(8, 3, 'Bob', 9000.00);
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;
> 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;
> 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 $20600.00
f_deptid 2
f_deptname Test
f_salary $19000.00
f_deptid 3
f_deptname Market
f_salary $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;
> 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 $6866.67
f_deptid 2
f_deptname Test
f_salary_avg $6333.33
f_deptid 3
f_deptname Market
f_salary_avg $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;
> 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 $8000.00
f_salary_min $6000.00
f_deptid 2
f_deptname Test
f_salary_max $7500.00
f_salary_min $5000.00
f_deptid 3
f_deptname Market
f_salary_max $9000.00
f_salary_min $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;
> 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;
> 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.
>
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.
>
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;
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.
>
其它函数
DBINFO
select dbinfo('dbhostname') as f_hostname from dual;
select dbinfo('dbname') as f_dbname from dual;
select dbinfo('version', 'full') as f_version from dual;
select dbinfo('sessionid') as f_sessionid from dual;
select dbinfo('bigserial') as f_bigserial from dual;
select dbinfo('serial8') as f_serial8 from dual;
> select dbinfo('dbhostname') as f_hostname from dual;
f_hostname
devsvr
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;
> 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;
> 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;
> select GetHzFullPY('南大通用') as f_py from dual;
f_py nandatongyong
1 row(s) retrieved.
>
- SYS_GUID
> select sys_guid() from dual;
(expression)
6FAE9FAC458A4B52AC7DCA22A2D19D6F
1 row(s) retrieved.
>
select current,dbservername,rowid,sitename,sysdate,today,current_user,user from dual;
> select current,dbservername,rowid,sitename,sysdate,today,current_user,user from dual;
(expression) 2021-06-26 00:53:24.041
(expression) gbaseserver
rowid 769
(expression) gbaseserver
(expression) 2021-06-26 00:53:24.04174
(expression) 2021 06月 26日
(expression) gbasedbt
(expression) gbasedbt
1 row(s) retrieved.
>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
