1 标量函数

—1.1 数学函数

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

> 

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

> 

——1.1.4 MOD

mod(num) 返回第一个参数的模

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.

> 

——1.1.5 POW/SQRT/ROOT

pow(num1, num2) 计算数值的N次方

sqrt(num) 计算平方根

root(num1, num2) 计算数值的N次方根

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.

>

——1.1.6 EXP

exp(num) 计算指数

> select exp(1) as f_exp from dual;


         f_exp 

2.718281828459

1 row(s) retrieved.

> 

——1.1.7 LN/LOGN/LOG10

ln(num) 计算自然对数

logn(num) 计算自然对数

log10(num)  计算以10为底的对数

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.

> 

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

> 

—1.2 字符串函数

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

> 

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

> 

——1.2.3 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;

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

> 

——1.2.5 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 ~]$ 

——1.2.6 REPLACE

replace(str1, str2) 替换字符串中的部分内容

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.

> 

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

> 

——1.2.8 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          10

1 row(s) retrieved.

> 

——1.2.9 REGEXP_REPLACE/REGEXP_SUBSTR/REGEXT_INSTR

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;

—1.3日期时间函数

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

> 

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

> 

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

> 

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

> 

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

> 

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

> 

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

> 

2 聚合函数

—2.1数据准备

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);

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

> 

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

> 

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

> 

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

> 

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

> 

3 窗口函数

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

> 

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

> 

—3.3 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;

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

> 

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

> 

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

> 

4 其它函数

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

> 

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

> 

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

> 

—4.4 GetHzFullPY

返回字符串的拼音

select GetHzFullPY('南大通用') as f_py from dual;

> select GetHzFullPY('南大通用') as f_py from dual;

f_py  nandatongyong 

1 row(s) retrieved.

> 

—4.5 SYS_GUID

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

>