oracle函数

一、聚合函数，操作多行数据，并返回一个结果
select avg(aly_layer) as 平均值 from t_aly_il_result
select sum(aly_layer) as 总和 from t_aly_il_result
select min(aly_layer) as 最小值 from t_aly_il_result
select max(aly_layer) as 最大值 from t_aly_il_result
select count(aly_layer) from t_aly_il_result

select create_time, avg(aly_layer), count(*)
from t_aly_il_result
where aly_layer > 0
group by  create_time 

select 后面有聚合函数时，不能再有列名，除非该列名在分组函数后，即以上语句是可以查询的，但以下语句不能

select create_time,avg(aly_layer), count(*)  from t_aly_il_result;

二、字符函数
SELECT INSTR('ORACLE技术圈','技术') FROM DUAL; --查找字符串X中字符串Y的位置
select CONCAT('ORACLE','技术圈') from DUAL; ---连接2个字符串
select LENGTH('ORACLE技术圈')from DUAL;---求字符串长度
SELECT LOWER('ORACLe技术') FROM DUAL;---大写转为小写
SELECT UPPER('Oracle技术圈') FROM DUAL;---小写转大写
SELECT INITCAP('ORACLE is good ') FROM DUAL;---首字母为大写
SELECT LTRIM('--ORACLE技术圈','-') FROM DUAL; --去掉字符1左边的字符2，即去掉-（2不填写默认为空格）
SELECT RTRIM('ORACLE技术圈--','-') FROM DUAL;--去掉字符1右边的字符2，即去掉-（2不填写默认为空格）
SELECT TRIM('--ORACLE技术圈--','-') FROM DUAL;--去掉字符1两边的字符2，即去掉-（2不填写默认为空格）
SELECT REPLACE('ORACLE技术圈1','技术圈1','技术交流') FROM DUAL;---把1中的2替换为3
SELECT SUBSTR('ORACLE技术圈',1,6) FROM DUAL;---从第一位开始截取，截取到第六位
SELECT RPAD('ORACLE',9,'-') from DUAL;---对ORACLE右补-，直到长度为9
SELECT LPAD('ORACLE',9,'-') from DUAL;---对ORACLE左补-，直到长度为9

三、日期函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;---系统时间
select systimestamp from dual;---包含时区信息，精确到微秒
select sysdate,last_day(sysdate) from dual;---返回指定日期当月的最后一天
SELECT SYSDATE,add_months(SYSDATE,4) FROM dual;---系统时间+几个月
SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;---四舍五入到最近的周的周日

 

SELECT SYSDATE "date",

EXTRACT(YEAR FROM SYSDATE)"year",

EXTRACT(MONTH FROM SYSDATE)"month",
EXTRACT(DAY FROM SYSDATE)"day",

EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",

EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",

EXTRACT(SECOND FROM SYSTIMESTAMP)"second"

FROM dual;----提取日期中的部分（年、月、日、时、分、秒）

 

 

四、数值函数
select trunc(7.816, 2), trunc(7.816), trunc(76.816, -1) from dual;--y不填时，默认为y=0;
select ABS(-3) from dual---绝对值
select ACOS(1) from dual ----反余弦
select COS(1) from dual ----余弦
select CEIL(5.4) from dual ----大于或等于X的最小值
select FLOOR(5.8) from dual ----小于或等于X的最大值
select LOG(2，4) from dual ----X为底Y的对数
select MOD(8，3) from dual ----X除以Y的余数
select POWER(2，3) from dual ----X的Y次幂
select ROUND(3.456，2) from dual ----整数时.往右边第Y位四舍五入，-2时.往左边第Y位四舍五入
select SQRT(4) from dual ----X的平方根
select TRUNC(3.456，2) from dual ----X在第Y位截断
select ROUND(351.654,-2) from dual

五、转换函数
1、把一个字符串以fmt格式转换成一个日期类型
select to_date('2018-11-13','yyyy-mm-dd') from dual;--把字符串x按照格式f进行格式化转换为时间类型

2、把日期和数字转换为字符串
select to_char(123.46,'999.9') from dual;--把字符串或时间类型x按格式f进行格式化，转换为字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

3、把一个字符串以fmt格式转换为一个数字
select to_number('123.74','999.99') from dual--把字符串x按照格式f进行格式化转换为数值类型
select bin_to_num(1,0,0) from dual;---二进制转换为十进制

select cast('123' as number) num,----数据类型转换，用于数字，字符，时间类型转换
cast(123 as varchar2(3)) as ch,
cast(to_date('20181112', 'yyyymmdd') as varchar2(12)) as time
from dual;

六、其它函数
NVL(X,VALUE) ---如果X为空，返回value，否则返回X，例如：如果没发奖金，每人奖金100元（奖金为空，返回100）
NVL2(x,value1,value2)---如果x非空，返回value1，否则返回value2,例如：如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元
NULLIF(exp1, exp2)---如果表达式exp1与exp2的值相等返回null,否则返回exp1的值
COALESCE(exp1, exp2, ...)---依次考察各参数表达式,遇到非null值即停止并返回该值
SELECT least(10,32,'123','2006') FROM dual;---返回表达式列表中值最小的一个
SELECT greatest(10,32,'123','2006') FROM dual;--返回表达式列表中值最大的一个

---case语句  
select empno，ename, sal,
case  deptno when 10 then '财务部'

                      when 20 then '研发部'

                     when 30 then '销售部'

                     else '未知部门'

end 部门
from emp

---decode语句
select empno, ename, sal,
       decode (deptno, 10, '财务部',
                      20, '研发部',
                     30, '销售部',
                '未知部门')
     部门
from emp;

 

--row_number()函数

语法一：ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)  

例子：

select  t.*,

          row_number() over(partition by rule_id order by chk_date desc) rn

              from t_dqs_ques_stat t

语法二：ROW_NUMBER() OVER(ORDER BY 排序字段 DESC)  

例子

select  t.T_DATA_DATE, t.*,

           row_number() over(order by chk_date desc) rn
               from t_dqs_ques_stat t