内置函数
标量函数
数学函数
ABS
abs(num)
select abs(-5.6) as num1, abs(5.6) as num2 from sysmaster:sysdual;
> select abs(-5.6) as num1, abs(5.6) as num2 from sysmaster:sysdual;
num1 num2
5.60000000000000 5.60000000000000
1 row(s) retrieved.
>
CEIL/FLOOR/ ROUND
ceil(num)
floor(num)
round(num)
select ceil(5.3) as f_ceil, floor(5.3)as f_floor, round(5.3) as f_round from sysmaster:sysdual;
> select ceil(5.3) as f_ceil, floor(5.3)as f_floor, round(5.3) as f_round from sysmaster:sysdual;
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 sysmaster:sysdual;
> select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from sysmaster:sysdual;
f_ceil f_floor f_round
-5 -6 -6
1 row(s) retrieved.
>
select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from sysmaster:sysdual;
> select ceil(-5.6) as f_ceil, floor(-5.6)as f_floor, round(-5.6) as f_round from sysmaster:sysdual;
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 sysmaster:sysdual;
> select ceil(-5.3) as f_ceil, floor(-5.3)as f_floor, round(-5.3) as f_round from sysmaster:sysdual;
f_ceil f_floor f_round
-5 -6 -5
1 row(s) retrieved.
>
MOD
mod(num)
select mod(10, 3) as f_mod from sysmaster:sysdual;
> select mod(10, 3) as f_mod from sysmaster:sysdual;
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 sysmaster:sysdual;
> select pow(2, 3) as f_pow3, sqrt(25) as f_sqrt, root(64, 3) as f_root from sysmaster:sysdual;
f_pow3 f_sqrt f_root
8.000000000000 5.000000000000 4.000000000000
1 row(s) retrieved.
>
EXP
exp(num)
select exp(2) as f_exp from sysmaster:sysdual;
> select exp(1) as f_exp from sysmaster:sysdual;
f_exp
2.718281828459
1 row(s) retrieved.
> select exp(2) as f_exp from sysmaster:sysdual;
f_exp
7.389056098931
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 sysmaster:sysdual;
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 sysmaster:sysdual;
> select sin(1) as f_sin, cos(1) as f_cos from sysmaster:sysdual;
f_sin f_cos
0.841470984808 0.540302305868
1 row(s) retrieved.
>
select sin(60) as f_sin, cos(30) as f_cos from sysmaster:sysdual;
字符串函数
concat
select concat('Hello', 'World') as f_concat from sysmaster:sysdual;
> select concat('Hello', 'World') as f_concat from sysmaster:sysdual;
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 sysmaster:sysdual) t;
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> 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 sysmaster:sysdual) 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 sysmaster:sysdual) 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 sysmaster:sysdual) 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),与SUBSTR大致相同，只是start,len是以字节计算。
select substr('abcdefg', 2, 3) as str,substrb('abcdefg', 2, 3) as strb from sysmaster:sysdual;
select substr('我们是好朋友', 1, 4) as str,substrb('我们是好朋友', 1, 4) as strb from sysmaster:sysdual;
> select substr('abcdefg', 2, 3) as str,substrb('abcdefg', 2, 3) as strb from sysmaster:sysdual;
str strb
bcd bcd
1 row(s) retrieved.
> select substr('我们是好朋友', 1, 4) as str,substrb('我们是好朋友', 1, 4) as strb from sysmaster:sysdual;
str strb
我们是好 我
1 row(s) retrieved.
>
INSTR
INSTR(c1,c2,i,j)
c1,c2均为字符串，i,j为整数。函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的缺省值为1.
select instr('How are you?', 'o', 1, 1) as f_instr1, instr('How are you?', 'o', 1, 2) as f_instr2 from sysmaster:sysdual;
> select instr('How are you?', 'o', 1, 1) as f_instr1, instr('How are you?', 'o', 1, 2) as f_instr2 from sysmaster:sysdual;
f_instr1 f_instr2
2 10
1 row(s) retrieved.
>
ASCII
ascii(str)，返回str字母的ASCII码
select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('星瑞格') as f_ascii3 from sysmaster:sysdual;
select ascii('星') as f_ascii1, ascii('瑞') as f_ascii2, ascii('星瑞格') as f_ascii3 from sysmaster:sysdual;
> select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('星瑞格') as f_ascii3 from sysmaster:sysdual;
f_ascii1 f_ascii2 f_ascii3
72 119 10459366
1 row(s) retrieved.
> select ascii('Hello') as f_ascii1, ascii('world') as f_ascii2, ascii('星瑞格') as f_ascii3 from sysmaster:sysdual;
f_ascii1 f_ascii2 f_ascii3
72 119 10459366
1 row(s) retrieved.
> select hex(ascii('星')) as f_ascii1, hex(ascii('瑞')) as f_ascii2, hex(ascii('星瑞格')) as f_ascii3 from sysmaster:sysdual;
f_ascii1 f_ascii2 f_ascii3
0x009F98E6 0x009E91E7 0x009F98E6
1 row(s) retrieved.
>
10459366=H0x009F98E6
[informix@vm84145 ~]$ cat a.txt
星瑞格
[informix@vm84145 ~]$ file a.txt
a.txt: UTF-8 Unicode text
[informix@vm84145 ~]$ od -x a.txt
0000000 98e6 e79f 9e91 a0e6 0abc
0000012
[informix@vm84145 ~]$
Replace
replace(str1, str2)
> select content, replace(content, 'reading', 'writing') as f_replace
> from (select 'I like reading' as content from sysmaster:sysdual) t;
content f_replace
I like reading I like writing
1 row(s) retrieved.
>
UPPER/LOWER
upper(str)
select upper('Hello World') as f_upper, lower('Hello World') as f_lower from sysmaster:sysdual;
> select upper('Hello World') as f_upper, lower('Hello World') as f_lower from sysmaster:sysdual;
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('星瑞格 ') as f_len12,
octet_length('星瑞格') as f_len21,
octet_length('星瑞格 ') as f_len22,
char_length('星瑞格') as f_len31,
char_length('星瑞格 ') as f_len32,
length('星瑞格:SinoDB') as f_len41,
octet_length('星瑞格:SinoDB') as f_len42,
char_length('星瑞格:SinoDB') as f_len43
from sysmaster:sysdual;
> select length('星瑞格') as f_len11,
> length('星瑞格 ') as f_len12,
> octet_length('星瑞格') as f_len21,
> octet_length('星瑞格 ') as f_len22,
> char_length('星瑞格') as f_len31,
> char_length('星瑞格 ') as f_len32,
> length('星瑞格:SinoDB') as f_len41,
> octet_length('星瑞格:SinoDB') as f_len42,
> char_length('星瑞格:SinoDB') as f_len43
> from sysmaster:sysdual;
f_len11 9
f_len12 9
f_len21 9
f_len22 10
f_len31 3
f_len32 4
f_len41 16
f_len42 16
f_len43 10
1 row(s) retrieved.
>
REGEXP_REPLACE
替换与正则表达式匹配的字符串。
select f_content, regex_replace(f_content, '[0-9]', '<number>') as f_template
from (select '我今年9岁，上3年级。' as f_content from sysmaster:sysdual) t;
> select f_content, regex_replace(f_content, '[0-9]', '<number>') as f_template
from (select '我今年9岁，上3年级。' as f_content from sysmaster:sysdual) t;
f_content 我今年9岁，上3年级。
f_template 我今年<number>岁，上<number>年级。
1 row(s) retrieved.
>
===========================
使用正则表达式函数的数据库需要注册bts
[informix@vm84145 extend]$ pwd
/home/informix/sinodb/extend
[informix@vm84145 extend]$ ls
binaryudt.1.0 excompat.1.0 ifxmngr ifxrltree.2.00 LLD.1.20.FC2 Node.2.0 TSAFuncs.1.00.FC1 TSPMatch.2.00.FC1
bts.3.10 ifxbuiltins.1.1 ifxregex.1.00 krakatoa mqblade.2.0 TimeSeries.6.00.FC7 TSPIndex.1.00.FC1
[informix@vm84145 extend]$ blademgr
sinodb>register ifxregex.1.00 testdb
Register module ifxregex.1.00 into database testdb? [Y/n]y
Registering DataBlade module... (may take a while).
DataBlade ifxregex.1.00 was successfully registered in database testdb.
sinodb>
================================
日期时间函数
SYSDATE/CURRENT/TODAY
select sysdate as f_date from sysmaster:sysdual;
select current as f_date from sysmaster:sysdual;
select today as f_date from sysmaster:sysdual;
> select sysdate as f_date from sysmaster:sysdual;
f_date
2022-02-18 22:41:50.00000
1 row(s) retrieved.
> select current as f_date from sysmaster:sysdual;
f_date
2022-02-18 22:42:02.000
1 row(s) retrieved.
> select today as f_date from sysmaster:sysdual;
f_date
2022 02月 18日
1 row(s) retrieved.
>
LAST_DAY
select last_day(date('2022-2-18')) as f_lastday from sysmaster:sysdual;
> select last_day(date('2022-2-18')) as f_lastday from sysmaster:sysdual;
f_lastday
2022-02-28
1 row(s) retrieved.
>
YEAR/MONTH/DAY
select year(date('2022-2-18')) as f_year, month(date('2022-2-18')) as f_month, day(date('2022-2-18')) as f_day from sysmaster:sysdual;
> select year(date('2022-2-18')) as f_year, month(date('2022-2-18')) as f_month, day(date('2022-2-18')) as f_day from sysmaster:sysdual;
f_year f_month f_day
2022 2 18
1 row(s) retrieved.
>
WEEKDAY/QUARTER
select weekday(date('2022-2-18')) as f_weekday, quarter(date('2022-2-18')) as f_quarte from sysmaster:sysdual;
> select weekday(date('2022-2-18')) as f_weekday, quarter(date('2022-2-18')) as f_quarte from sysmaster:sysdual;
f_weekday f_quarte
5 1
1 row(s) retrieved.
>
ADD_MONTH
select add_months(date('2022-2-18'), 2) as f_month1, add_months(date('2022-2-18'), -4) as f_month2 from sysmaster:sysdual;
> select add_months(date('2022-2-18'), 2) as f_month1, add_months(date('2022-2-18'), -4) as f_month2 from sysmaster:sysdual;
f_month1 f_month2
2022-04-18 2021-10-18
1 row(s) retrieved.
>
TO_DATE
select to_date('2021-06-18 12:34:56','%Y-%-m-%d %H:%M:%S') as f_date from sysmaster:sysdual;
> select to_date('2021-06-18 12:34:56','%Y-%-m-%d %H:%M:%S') as f_date from sysmaster:sysdual;
f_date
2021-06-18 12:34:56.00000
1 row(s) retrieved.
>
TO_CHAR
select sysdate as f_date1, to_char(sysdate, '%m/%d/%Y %H:%M:%S') as f_date2 from sysmaster:sysdual;
> select sysdate as f_date1, to_char(sysdate, '%m/%d/%Y %H:%M:%S') as f_date2 from sysmaster:sysdual;
f_date1 2022-02-18 23:09:18.00000
f_date2 02/18/2022 23:09:18
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 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;
> 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;
> 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;
窗口函数
ROW_NUMBER()
ROW_NUMBER （）会给一个分组内的数据加行号 ，并且不管其内容是否重复，都可以连续编号
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 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
RANK()
根据order by子句的排序字段，从分组查询每一行数据，按照排序生成序号，会跳跃排序，如：1，2，2，4，5
DENSE_RANK()
根据order by子句的排序字段，从分组查询每一行数据，按照排序生成序号，不会跳跃排序，如：1，2，2，3，4
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 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);
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;
> 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.
计算各部门薪资排名
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 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 3
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 3
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
FIRST_VALUE 返回分区（分组）中的第一个值
LAST_VALUE()事实上并不返回分组中最后的一个值，默认返回的是每一行里面已经出现的数据的最后一个值，即默认的统计范围是”rows between unbounded preceding and current row【无界的前面行和当前行之间】”
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 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;
> 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.
>
可以看到使用last_value函数求每个人最后一个日期，结果并不是想要的。那该怎么办呢，查询该函数的具体用法发现：
rows between unbounded preceding and current row,可以这么理解: x∈(-∞,X)
rows between unbounded preceding and unbounded following, x∈(-∞,+ ∞)
rows between current row and unbounded following, x∈(X,+ ∞)
select f_deptname,f_salary ,
last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between unbounded preceding and current row) as f_diff1,
last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between unbounded preceding and unbounded following) as f_diff2,
last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between current row and unbounded following) as f_diff3,
f_employeename
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 f_deptname,f_salary ,
> last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between unbounded preceding and current row) as f_diff1,
> last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between unbounded preceding and unbounded following) as f_diff2,
> last_value(f_salary) over(partition by f_deptid order by f_salary desc rows between current row and unbounded following) as f_diff3,
> f_employeename
> 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_deptname Dev
f_salary RMB 8000.00
f_diff1 RMB 8000.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Jack
f_deptname Dev
f_salary RMB 6600.00
f_diff1 RMB 6600.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Tom
f_deptname Dev
f_salary RMB 6600.00
f_diff1 RMB 6600.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Mary
f_deptname Dev
f_salary RMB 5000.00
f_diff1 RMB 5000.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Will
f_deptname Test
f_salary RMB 7500.00
f_diff1 RMB 7500.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Rose
f_deptname Test
f_salary RMB 6500.00
f_diff1 RMB 6500.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Bill
f_deptname Test
f_salary RMB 6500.00
f_diff1 RMB 6500.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Henry
f_deptname Test
f_salary RMB 5000.00
f_diff1 RMB 5000.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Judy
f_deptname Market
f_salary RMB 9000.00
f_diff1 RMB 9000.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Bob
f_deptname Market
f_salary RMB 5000.00
f_diff1 RMB 5000.00
f_diff2 RMB 5000.00
f_diff3 RMB 5000.00
f_employeename Kate
10 row(s) retrieved.
>
MAX/MIN
drop table if exists t_sale;
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.
>
patition by 在group by分组汇总的前提下，再汇总一次
1、patition by 1 汇总所有数据
2、patition by 字段 根据该字段汇总
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
lag/lead一般计同比数据或环比数据时会用到，它是和过去的历史的值做比较。
lag(列名称 [,行数字] [,默认值])
访问分区（分组）中指定前N行的记录，如果没有则返回默认值，不设置默认值则返回null
lead(列名称 [,行数字] [,默认值])
访问分区（分组）中指定后N行的记录，如果没有则返回默认值
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
DBINFO()实际上是一组函数,返回不同类型的数据库的相关信息。在参数位置指定一个特定的选项，就可以调用相应的函数功能。可以在SQL语句和UDR中使用DBINFO选项。
select dbinfo('dbhostname') as f_hostname from sysmaster:sysdual;
select dbinfo('dbname') as f_dbname from sysmaster:sysdual;
select dbinfo('version', 'full') as f_version from sysmaster:sysdual;
select dbinfo('sessionid') as f_sessionid from sysmaster:sysdual;
select dbinfo('bigserial') as f_bigserial from sysmaster:sysdual;
select dbinfo('serial8') as f_serial8 from sysmaster:sysdual;
> select dbinfo('dbhostname') as f_hostname from sysmaster:sysdual;
f_hostname
vm84145
1 row(s) retrieved.
> select dbinfo('dbname') as f_dbname from sysmaster:sysdual;
f_dbname testdb
1 row(s) retrieved.
> select dbinfo('version', 'full') as f_version from sysmaster:sysdual;
f_version
Sinoregal SinoDB Dynamic Server Version 12.10.FC8
1 row(s) retrieved.
> select dbinfo('sessionid') as f_sessionid from sysmaster:sysdual;
f_sessionid
56
1 row(s) retrieved.
> select dbinfo('bigserial') as f_bigserial from sysmaster:sysdual;
f_bigserial
0
1 row(s) retrieved.
> select dbinfo('serial8') as f_serial8 from sysmaster:sysdual;
f_serial8
0
1 row(s) retrieved.
>
NVL2
create table t_user5(fname varchar(30),fleave date);
insert into t_user5 values('Tom','2022-02-19');
insert into t_user5 values('Jim',null);
select fname, fleave, nvl2(fleave, '离职', '在职') as f_userstatus
from t_user5;
> select fname, fleave, nvl2(fleave, '离职', '在职') as f_userstatus
> from t_user5;
fname fleave f_userstatus
Tom 2022-02-19 离职
Jim 在职
2 row(s) retrieved.
>
HEX
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 sysmaster:sysdual;
> 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 sysmaster:sysdual;
f_hex1 0x000000FF
f_hex2 0x0000FFFF
f_hex3 0x0000000080808080
f_hex4 0x0000000100000000
f_hex5 0x0fffffffffffffff
1 row(s) retrieved.
>